Query Details
Identity Inactive Privileged Users
Query
//Find users who hold privileged Azure AD roles but haven't signed onto Azure for 30 days
//Data connector required for this query - Azure Active Directory - Signin Logs
//Data connector required for this query - Microsoft Sentinel UEBA
let applications = dynamic(["Azure Active Directory PowerShell", "Microsoft Azure PowerShell", "Graph Explorer", "ACOM Azure Website", "Azure Portal", "Azure Advanced Threat Protection"]);
IdentityInfo
| where TimeGenerated > ago(21d)
| where isnotempty(AssignedRoles)
| project-rename UserPrincipalName=AccountUPN
| where AssignedRoles != "[]"
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| join kind=leftanti (
SigninLogs
| where TimeGenerated > ago(30d)
| where AppDisplayName in (applications)
| where ResultType == "0"
)
on UserPrincipalName
| project UserPrincipalName, AssignedRolesExplanation
This query is looking for users who have privileged Azure AD roles but have not signed into Azure for the past 30 days. It uses the Azure Active Directory - Signin Logs data connector and the Microsoft Sentinel UEBA data connector.
The query first retrieves identity information for users who have assigned roles and have generated logs in the past 21 days. It then renames the "AccountUPN" column to "UserPrincipalName" and filters out users with empty or no assigned roles.
Next, it finds the latest log entry for each user based on the timestamp and joins it with the signin logs data. The signin logs data is filtered to include only logs from the past 30 days, with specific application display names, and a successful result type.
Finally, the query projects the UserPrincipalName and AssignedRoles columns for the final result.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators