question

AshleyVan avatar image
0 Votes"
AshleyVan asked CliveWatson-3295 commented

How to prev()

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-sentinelazure-ad-sign-in-logs
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm reaching within the product team and will come back to you.

0 Votes 0 ·

@AshleyVan What is the time range we are trying to query on?

0 Votes 0 ·

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()




0 Votes 0 ·
Show more comments

0 Answers