I wanted to get a list of users recently removed from an Entra ID group. I can see the removals in the audit logs, but there’s easy way to add a column showing removals.
Enter KQL. We are sending all audit logs to a Log Analytics workspace, so I queried this info that way. This is a very trivial use of KQL, but I am just putting it here for my own reference later on…
This code gives you everything from AuditLogs in the last 30 days that have “Remove member from group” as the activity.
|
1 2 3 |
AuditLogs | where TimeGenerated > ago(30d) | where ActivityDisplayName == 'Remove member from group' |
This activity name corresponds to what you see in the portal too.
Take one of the result and expand it until you get to TargetResources. The second entry in that has the group that’s affected. Expand that to get to the id, and then right click and filter for that.
This modifies the code thus:
|
1 2 3 4 |
AuditLogs | where TimeGenerated > ago(30d) | where ActivityDisplayName == 'Remove member from group' | where TargetResources[1].id == "<the group guid>" |
Then similarly expand the first entry under TargetResources, select UPN in there, and extend that. (In my case, the already selected entry didn’t have a user being removed… so I found an entry with a user removal).
Finally project just that extended column. The end result looks like this:
|
1 2 3 4 5 6 |
AuditLogs | where TimeGenerated > ago(30d) | where ActivityDisplayName == 'Remove member from group' | where TargetResources[1].id == "<the group guid>" | extend userPrincipalName_ = tostring(TargetResources[0].userPrincipalName) | project userPrincipalName_ |
This gives a list of UPNs removed from the group.
Of course now that you/ future me have read this blog post, just copy paste the above KQL and use it directly. :)