Query Details
Audit Userswith Priv Rolesbutno Activity
Query
//Find users who hold a privileged Azure AD role but haven't completed any activities in Azure AD for 45 days
//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA
//Lookup the IdentityInfo table for any users holding a privileged role
IdentityInfo
| where TimeGenerated > ago(21d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| project UserPrincipalName=AccountUPN, AssignedRoles
| join kind=leftanti (
AuditLogs
| where TimeGenerated > ago(45d)
| extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where isnotempty(UserPrincipalName)
| distinct UserPrincipalName
)
on UserPrincipalNameExplanation
This query is looking for users who have privileged roles in Azure AD but have not performed any activities in Azure AD for the past 45 days. It uses the Azure Active Directory - Audit Logs data connector and the Microsoft Sentinel UEBA data connector.
The query first looks up the IdentityInfo table to find users with privileged roles. It filters the results to include only data from the past 21 days and groups the data by the user's account UPN (User Principal Name). It then filters out any users who do not have any assigned roles or have an empty assigned roles field. The query projects the UserPrincipalName and AssignedRoles fields.
Next, the query performs a left anti-join with the AuditLogs table. It filters the AuditLogs data to include only data from the past 45 days and extracts the UserPrincipalName from the InitiatedBy.user field. It removes any rows where the UserPrincipalName is empty or duplicated.
Finally, the query joins the results of the IdentityInfo table with the filtered AuditLogs table using the UserPrincipalName field. This will give a list of users who have privileged roles but have not performed any activities in Azure AD for the past 45 days.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators