Clarifying Unfamiliar Sign-ins with Kusto

By Gianni Castaldi

One of the most common alerts we receive in Microsoft Azure Sentinel is the alert: Unfamiliar Sign-in Properties, from Microsoft Azure Identity protection. According to the docs, its caused when: “Sign in with properties we’ve not seen recently for the given user.” The alert continuously improved, and is looking at least for the following properties:

Device identifiers
IP addresses
Locations
Corporate IP addresses
IP carriers
Browser sessions

Back in the days, when users had a single computer waiting for them, this would not give any issues. But nowadays users can have several computers, mobile phones, tablets and can travel all over the world. That is why rules like these exist and can get triggered a lot. To interpret the data you could incorporate it in a second rule or open Microsoft Azure Sentinel and start investigating.

What we will do in this query is declare 2 values with the let operator so we can easily repurpose the query. After that, we aggregate the SigninLogs table with the 2 values, and use the min and max operator to see when the user used the IP, location, device id, user agent, client app and app display name. We will also add a success and failure counter to get a feeling if the combination is trustable.

let UPN = "gianni@kustoking.com"; 
let TimeFrame = ago(30d); 
SigninLogs 
| where TimeGenerated > TimeFrame
| where UserPrincipalName =~ UPN
| summarize FirstSeen = min(TimeGenerated), LastObserved = max(TimeGenerated), SuccessfullCount = count(ResultType = 0), FailureCount = count(ResultType != 0) 
by UserPrincipalName, IPAddress, Location, tostring(DeviceDetail.deviceId), UserAgent, ClientAppUsed, AppDisplayName

To see how the result will look I have launched the query in several steps.

The first time to see the IP Addresses, Location and Device ID’s: