Microsoft Sentinel
A scalable, cloud-native solution for security information event management and security orchestration automated response. Previously known as Azure Sentinel.
1,065 questions
How to prev()
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(163.2, 38%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAV%3C/text%3E%3C/svg%3E)
Ashley Van
1
Reputation point
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
{count} votes
-
Alfredo Revilla - Upwork Top Talent | IAM SWE SWA 27,436 Reputation points2020-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,436 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 points2020-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"
Sign in to comment