KQL Search

Query Details

HomeAI ToolsDevice Query

Sign Inby Location

Query

SigninLogs
| where TimeGenerated > ago(120d)
| where UserDisplayName !="On-Premises Directory Synchronization Service Account"
| extend city_  = tostring(LocationDetails.city) 
| extend state_ = tostring(LocationDetails.state) 
| extend countryOrRegion_ = tostring(LocationDetails.countryOrRegion) 
| extend latitude_  = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).latitude) 
| extend longitude_ = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).longitude) 
| order by TimeGenerated asc , city_ asc
| serialize 
| extend pLat = prev(latitude_,1)
| extend pLon = prev(longitude_,1)
| extend distance_in_miles = iif(isnotempty(pLat),tostring(round(geo_distance_2points(todouble(longitude_), todouble(latitude_), todouble(pLon), todouble(pLat))/1609.344 ,2)),"FirstLocation")
| where distance_in_miles !="0.0"
| summarize count() by bin(TimeGenerated, 24h),                    
                       userNameLocation = strcat(UserDisplayName," 👤 " ,city_ , " 🗺️ ",
                       countryOrRegion_),
                       visit_order = strcat(row_number(), ".",city_),
                       MilesTravelled=distance_in_miles                                        
| project-away count_
| order by TimeGenerated asc, visit_order asc

Explanation

This query retrieves signin logs from the past 120 days and filters out a specific user account. It then extracts location details such as city, state, and country, as well as latitude and longitude coordinates. The results are sorted by time and city. The query calculates the distance traveled between consecutive locations and removes any entries with a distance of 0.0 miles. Finally, it summarizes the count of signin events per day, along with the user's display name, city, country, visit order, and miles traveled. The results are ordered by time and visit order.

Details

Rod Trent profile picture

Rod Trent

Released: August 26, 2020

Tables

SigninLogsLocationDetails

Keywords

SigninLogs,TimeGenerated,UserDisplayName,LocationDetails,city_,state_,countryOrRegion_,latitude_,longitude_,pLat,pLon,distance_in_miles,userNameLocation,visit_order,MilesTravelled

Operators

|where>ago!="extend=tostringparse_jsonroundiifisnotemptyprevtodoublegeo_distance_2pointsisnotemptysummarizebinstrcatrow_numberproject-away

Actions