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 = ""; 
let TimeFrame = ago(30d); 
| 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:

The second with the user agent:

And the last one to understand which apps are used:

Based on the results we can state that:

  • The user suddenly signed in from France and Israel
  • The sign-in from France used a different user agent
  • The counters do not show signs of brute-forcing

Based on these results I would recommend:

  • Contact the user to see if he has used some sort of VPN or VPS service
  • Reset the user’s password
  • Check if the user’s E-mail address appears on
Alternative Text

By Gianni Castaldi

MVP | NinjaCat | Researching and Engineering Cyber Security @ KustoWorks

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.