How to prev()

Ashley Van 1 Reputation point
2020-10-15T14:49:24.17+00:00

Hello!

How do I use prev() to return only results of the same UserDisplayName of the current log?

Running the search below gives unexpected output (negative time_between_logins) and the previous log seems to be tied to a different user.

Any advice on how to filter this to only return results of specific users? Any advice on how to make this better?

SigninLogs
| extend timestamp = TimeGenerated
| 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)
| serialize
| extend pLat = prev(latitude_,1)
| extend pLon = prev(longitude_,1)
| extend time_between_logins = datetime_diff('minute',timestamp,prev(timestamp))
| 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 ConditionalAccessStatus == "success"
| summarize by time_between_logins

Microsoft Sentinel
Microsoft Sentinel
A scalable, cloud-native solution for security information event management and security orchestration automated response. Previously known as Azure Sentinel.
920 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
18,578 questions
{count} votes