KQL Search

Query Details

HomeAI ToolsDevice Query

Users Connect From Multiple City

Query

//Reports users who have connected from more than 1 location

AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(1d)
| extend City = parse_json(LocationDetails).city
| summarize CountPerCity = dcount(tostring(City)) by UserPrincipalName
| where CountPerCity > 2
| order by CountPerCity desc

Explanation

This query retrieves information about users who have connected from multiple locations. It filters the data to include only the logs from the past day and extracts the city information from the location details. Then, it calculates the number of unique cities per user and filters out users who have connected from more than two cities. Finally, it sorts the results in descending order based on the count of cities per user.

Details

Rod Trent profile picture

Rod Trent

Released: November 5, 2021

Tables

AADNonInteractiveUserSignInLogs

Keywords

Reports,Users,Connected,Location,AADNonInteractiveUserSignInLogs,TimeGenerated,City,LocationDetails,UserPrincipalName,CountPerCity,Order

Operators

whereagoextendparse_jsonsummarizedcounttostringbyorder bydesc

Actions