Query Details
Audit Users Who Havent Elevated PIM
Query
//Find users who have not elevated any roles in Azure AD PIM in 30 days
//Data connector required for this query - Azure Active Directory - Audit Logs
AuditLogs
| where TimeGenerated > ago (365d)
| project TimeGenerated, OperationName, Result, TargetResources, InitiatedBy
| where OperationName == "Add member to role completed (PIM activation)"
| where Result == "success"
| extend ['Last Role Activated'] = tostring(TargetResources[0].displayName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| summarize arg_max(TimeGenerated, *) by Actor
| project
Actor,
['Last Role Activated'],
['Last Activation Time']=TimeGenerated,
['Days Since Last Activation']=datetime_diff("day", now(), TimeGenerated)
| where ['Days Since Last Activation'] >= 30
| sort by ['Days Since Last Activation'] descExplanation
This query finds users who have not elevated any roles in Azure AD PIM (Privileged Identity Management) in the last 30 days. It uses the Azure Active Directory - Audit Logs data connector to retrieve the necessary information. The query filters the audit logs based on the operation name and result, and then extracts the relevant fields such as the last role activated, actor (user), last activation time, and days since the last activation. Finally, it sorts the results based on the number of days since the last activation in descending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
AuditLogs
Keywords
Users,AzureADPIM,AuditLogs
Operators
whereagoprojectextendsummarizearg_maxdatetime_diffnowsort bydesc