Hunt for Azure Active Directory sign-in events

Published Jan 07 2021 01:15 PM 21.7K Views

We are happy to announce the public preview availability of a new data source in Microsoft 365 Defender advanced hunting.


Two new tables for Azure Active Directory sign-ins are now available in advanced hunting:

Tables are visible for global roles assigned in Azure Active Directory only, as enforced by Azure Active Directory.


The tables are suffixed with “beta” because it is a short-term solution to help you quickly identify possible malicious sign-in events for investigation. In parallel to making this data available, we are working on a more robust and complete solution. We will share more details on that soon.


Here are some useful sample queries that can also help you understand how to use these new tables:


// Finds attempts to sign in to disabled accounts, listed by IP address
let timeRange = 14d;
| where  Timestamp >= ago(timeRange)
| where ErrorCode == '50057'  // The user account is disabled.
| summarize StartTime = min(Timestamp), EndTime = max(Timestamp), numberAccountsTargeted = dcount(AccountObjectId),
numberApplicationsTargeted = dcount(ApplicationId), accountSet = make_set(AccountUpn), applicationSet=make_set(Application),
numberLoginAttempts = count() by IPAddress
| extend timestamp = StartTime, IPCustomEntity = IPAddress
| order by numberLoginAttempts desc
// Users with multiple cities 
// Gets a list of users that signed in from multiple locations in the last 24 hours
| where  Timestamp >= ago(1d)
| summarize CountPerCity = dcount(City), citySet = make_set(City) by AccountUpn
| where CountPerCity > 1
| order by CountPerCity desc
// Most active Managed Identities
// Gets list of the top 100 most active managed identities in the last 24 hours

| where Timestamp > ago(1d)
| where IsManagedIdentity == True
| summarize CountPerManagedIdentity = count() by ServicePrincipalId
| order by CountPerManagedIdentity desc
| take 100
// Inactive Service Principals 
// Gets list of service principals with no sign-ins in the last ten days
| where Timestamp > ago(30d)
| where ErrorCode == 0
| summarize LastSignIn = max(Timestamp) by ServicePrincipalId
| where LastSignIn < ago(10d)
| order by LastSignIn desc


Note: Customers who can access Microsoft 365 Defender through the Azure Security Center’s integrated Microsoft Defender for Endpoint solution, but do not have licenses for any of Microsoft Defender for Office 365, Microsoft Defender for Identity, or Microsoft Cloud App Security, will not be able to view this schema.

Version history
Last update:
‎May 11 2021 10:47 PM
Updated by: