KQL Sign-in Logs

Question

Is there a way to pull the last 2 sign-ins for every user, then filter by users with multiple states? I had some help with this code, but am stuck on trying to dial this down.

SigninLogs
| project State = tostring(LocationDetails.state), UserDisplayName
| summarize States = make_set(State) by UserDisplayName, LocationDetails_countryOrRegion
| where array_length(States) > 1

Avnera Avnera · Accepted Answer · 2020-12-23T06:34:10

It depends if you are looking for multiple states in the last two sign-ins or that users with two signs-ins had multiple states in their history. Assuming it is the former, here is one suggestion:

SigninLogs
// will be good to have a time filter
| summarize by State = tostring(LocationDetails.state), UserDisplayName, LocationDetails_countryOrRegion, TimeColumn
| order by UserDisplayName, TimeColumn desc
| extend row_num = row_number(0,UserDisplayName!=prev(UserDisplayNames))
| where row_num <= 1 //pick the last two sign ins
| summarize States = make_set(State) by UserDisplayName, LocationDetails_countryOrRegion
| where array_length(States) > 1

KQL Sign-in Logs

1 Answers