Query Details
PIM User Assigned Rolebut Hasnt Activated
Query
// Find users who are assigned a privileged role in Azure AD but haven't activated a role in the last 45 days
//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA
IdentityInfo
| where TimeGenerated > ago(21d)
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| summarize arg_max(TimeGenerated, *) by AccountUPN
| join kind=leftanti (
AuditLogs
| where TimeGenerated > ago(45d)
| where OperationName == "Add member to role completed (PIM activation)"
| extend AccountUPN = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| summarize arg_max(TimeGenerated, *) by AccountUPN)
on AccountUPNExplanation
This query is looking for users in Azure AD who have been assigned a privileged role but have not activated a role in the last 45 days. It uses the Azure Active Directory - Audit Logs data connector and the Microsoft Sentinel UEBA data connector.
The query first filters the IdentityInfo table to only include data from the last 21 days and where the AssignedRoles field is not empty. It then uses the summarize function to keep only the latest record for each user based on the TimeGenerated field.
Next, it performs a left anti-join with the AuditLogs table. The AuditLogs table is filtered to only include data from the last 45 days and where the OperationName is "Add member to role completed (PIM activation)". The AccountUPN field is extracted from the InitiatedBy.user field and the summarize function is used to keep only the latest record for each user based on the TimeGenerated field.
Finally, the two tables are joined on the AccountUPN field using a left anti-join, which means only the users from the IdentityInfo table who do not have a matching record in the AuditLogs table will be included in the results.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators