Query Details
Identity Legacy Auth Pivot Table
Query
//Create a pivot table showing all your users who have signed in with legacy auth, which applications they are using (such as IMAP or ActiveSync) and the count of each
//Data connector required for this query - Azure Active Directory - Signin Logs
//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where ClientAppUsed !in ("Mobile Apps and Desktop clients", "Browser")
| where isnotempty(ClientAppUsed)
| evaluate pivot(ClientAppUsed, count(), UserPrincipalName)
//Advanced Hunting query
//Data connector required for this query - Advanced Hunting with Azure AD P2 License
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where ErrorCode == 0
| where ClientAppUsed !in ("Mobile Apps and Desktop clients", "Browser")
| where isnotempty(ClientAppUsed)
| evaluate pivot(ClientAppUsed, count(), AccountUpn)Explanation
This query retrieves information about users who have signed in with legacy authentication and the applications they are using. It counts the number of sign-ins for each application. The first query uses the Azure Active Directory - Signin Logs data connector in Microsoft Sentinel, while the second query uses the Advanced Hunting with Azure AD P2 License data connector. Both queries filter the results based on a time range, result type, client app used, and non-empty client app. They then use the "evaluate pivot" function to create a pivot table showing the count of sign-ins for each user and application combination.
Details
Matt Zorich
Released: June 17, 2022
Tables
SigninLogsAADSignInEventsBeta
Keywords
SigninLogs,TimeGenerated,ResultType,ClientAppUsed,MobileAppsandDesktopclients,Browser,isnotempty,UserPrincipalName,AADSignInEventsBeta,Timestamp,ErrorCode,AccountUpn
Operators
whereago==!inisnotemptyevaluatepivotcount()