How to prev()

Ashley Van 1

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

Alfredo Revilla - Upwork Top Talent | IAM SWE SWA 27,461 Reputation points

2020-10-19T15:40:54.28+00:00

I'm reaching within the product team and will come back to you.
Alfredo Revilla - Upwork Top Talent | IAM SWE SWA 27,461 Reputation points

2020-10-27T02:18:31.627+00:00

@Ashley Van What is the time range we are trying to query on?
Clive Watson - MSFT 106 Reputation points

2020-10-28T14:40:20.42+00:00

This looks similar to the query I used to have in: https://github.com/Azure/Azure-Sentinel/blob/master/Workbooks/UserMap.json its been amended in the latest versions (use the same link).

I changed the way I handled the "first location" better and thus the prev()
Clive Watson - MSFT 106 Reputation points

2020-10-28T14:41:38.353+00:00

SigninLogs
| 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 distanceType = iif('{Measurement}' == "KM", todouble(1000), todouble(1609.344))
| extend distance_in = iif(row_number() > 1,tostring(round(geo_distance_2points(todouble(longitude_), todouble(latitude_), todouble(pLon), todouble(pLat))/distanceType ,2)),strcat("FirstLocation",' ','({Measurement})') )
| extend dwell_in = datetime_diff("second",TimeGenerated, prev(TimeGenerated))
| where distance_in !="0.0"

Share via

How to prev()

Your answer