United Kingdom: +44 (0)208 088 8978

Log Search Queries for Sign-In Logs

Some sign-ins you just need to be notified of.

We're hiring Software Developers

Click here to find out more

It is a common practice for Azure admins to create so-called break-glass admin accounts. These accounts are not meant to be used for your daily Azure shenanigans, and should only be reached for in your penultimate-ditch effort to regain admin access. Because nobody wants the embarrassment of the ultimate solution - having to ask your Microsoft partner to reset your access.

As these accounts are not to be used aside from emergencies and hold a lot of power, you'd probably want to be alerted when they're used. There are enough how-tos out there for creating these accounts and relevant alerts. I will fast-track through the fundamentals, then I will get to the KQL queries I designed with my limited KQL skills.

The Fundamentals

Create the account

A few tips on that:

  • Do not use your custom domain in the account - use the *.onmicrosoft.com domain instead. This is to make sure you can use it even when your custom domain is down or compromised.
  • Think about how many people you really need to share the password with (the less the better), and preferably do not store it anywhere outside the brains of the chosen few.
  • Edit your Conditional Access Policies to exclude the account from any MFA requirements/enforcements.

Setting up the alerts

Export the sign-in logs

In Azure portal, go to your Microsoft Entra ID. In the Monitoring section, find Diagnostic settings. Add a setting, make sure SignInLogs is ticked and select the Log Analytics workspace you want to ingest the selected logs into. Save the setting.

Create the Alert

In Azure portal, go to Monitor. Navigate to the Alerts blade and click Create an alert rule. On the resource selector screen, find the workspace that holds the logs and tick only it and nothing else.



Go through the remaining pages to set the alert name, description and severity, and determine where the alert is going to live in your Azure subscription. Don't forget to set up actions to get notified via the proper channels. It is handy to have an Action Group configured to email, text, or maybe even tickle an endpoint of a Logic App that will send out notifications to designated channels on Slack and/or Microsoft Teams.

KQL Time

Query 1 - they tried to break in

| where TimeGenerated > ago(6minutes)
| where UserPrincipalName =~ 'darth.adminous@mydomain.onmicrosoft.com'

This query detects any sign-in attempt mentioned in the logs, successful or not. It takes the entries generated in the last 6 minutes (remember the rule is going to be set to execute this query every 5 minutes) and filters them down to the ones mentioning the user principal we are interested in. It can hardly get simpler than this.

Query 2 - they tried to break in and they succeeded

| where TimeGenerated > ago(6minutes)
| where UserPrincipalName =~ 'darth.adminous@mydomain.onmicrosoft.com'
| project parse_json(AuthenticationDetails), RowId=hash_many(TimeGenerated, Id)
| where array_length(AuthenticationDetails) > 0
| mv-expand AuthenticationDetails
| summarize AuthSteps = make_list(tobool(AuthenticationDetails.succeeded)) by RowId
| where AuthSteps !contains 'false'

This is an alternative, slightly more evolved version of Query 1. You can use this variant for accounts that are not excluded from the MFA policy. It inspects authentication details attached to the record and only lets through entries where every authentication step is marked as successful.

Something to think about if you want to get creative with the time intervals in this query (and the alert rule, in concert): don't forget that the ago() function works with time that is 'now' in the moment the query is executing, whereas the TimeGenerated timestamp could be a datetime outside your look-back period by the time it reaches the log storage.

Final thoughts

It should be noted that log ingestion takes time (up to several minutes) and as such, relying on log analysis to solve this problem is not optimal if we want to get notified about an important security event as soon as possible. Good enough if you're stuck with Azure Entra ID P1 licence, though.