Identity Daily Summaryof Users Addedto AAD Groups
Query
//Create a daily summary of Azure Active Directory group additions
//Data connector required for this query - Azure Active Directory - Audit Logs
let timerange=7d;
AuditLogs
| where TimeGenerated > ago (timerange)
| where OperationName == "Add member to group"
| extend Type = tostring(TargetResources[0].type)
| where Type == "User"
| extend ['Group Name'] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| extend UserAdded = tostring(TargetResources[0].userPrincipalName)
| where isnotempty(UserAdded)
| summarize ['Users Added']=make_set(UserAdded) by ['Group Name'], startofday(TimeGenerated)
| sort by ['Group Name'] asc, TimeGenerated descExplanation
This query creates a daily summary of Azure Active Directory group additions. It uses the Azure Active Directory - Audit Logs data connector. The query filters for operations where a member is added to a group. It then extracts the relevant information such as the group name and the user added. The query summarizes the data by grouping it by the group name and the start of each day. The results are sorted by group name in ascending order and time generated in descending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
AuditLogs
Keywords
Azure Active DirectoryAudit LogsTimeGeneratedOperationNameAdd member to groupTargetResourcestypeUserGroup NamemodifiedPropertiesnewValueUserAddedisnotemptyUsers Added
Operators
agowhereextendtostringparse_jsonisnotemptysummarizemake_setbysort