SignIns with Country Name

raw.githubusercontent.com/lukes/ISO-3166-Countries-with-Regional-Codes/refs/heads/master/all/all.csv'] with (format=csv, ignoreFirstRecord=True);

Detection Query

let CountryCodes = externaldata (country: string,countryOrRegion:string) [@'https://raw.githubusercontent.com/lukes/ISO-3166-Countries-with-Regional-Codes/refs/heads/master/all/all.csv'] with (format=csv, ignoreFirstRecord=True);
SigninLogs
| where TimeGenerated > ago(90d)
| where ResultType == 0
| extend countryOrRegion = tostring(LocationDetails.countryOrRegion)
| join kind = leftouter CountryCodes on countryOrRegion
| where isnotempty(countryOrRegion)
| extend VT_IP= iff(isnotempty(IPAddress),strcat(@"https://www.virustotal.com/gui/ip-address/",IPAddress),IPAddress)
| summarize count() by country, UserPrincipalName, VT_IP
| extend country = iif(country == "United Kingdom of Great Britain and Northern Ireland","United Kingdom",country)

Tags