SigninLogs
| where ResultType != 0 // Non-successful sign-ins
| where TimeGenerated >= ago(30d) // Last 30 days
| extend Country = tostring(LocationDetails.countryOrRegion)
| where Country != “AU” // Exclude Australia
| summarize FailedLogins = count() by Country
| order by FailedLogins desc
The above is an improved version of a KQL query you can use to report on failed logins to Entra ID over the past 30 days. It also excludes a country (here Australia) if desired.
The country codes are here:
https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2
Note: if you copy and paste directly from here you will probably have the change the “ when you paste into your own environment as the wrong “ gets taken across!
CIAOPS 
Hi Robert,
This is really great.
However, when I run it on my little tenant in Entra-Id , only three users, I get a system message âYour query is consuming excessive resourcesâ It ran for 13 seconds.
The query returns a number 23 failed logins as a single line but doesnât generate the chart, just a blank blue square.
Do you have an insight to this?
LikeLike
Are you actually ingesting the EntraID logs so the query can use that data??
LikeLike
I ran it again without the country filter.
I get a pie charts so I guess that means I have no failed logins from other countries.
This is so powerful.
Mike Hatfield
LikeLike