Reporting on Azure Security Group Membership Additions

[WumboJumbo675]

Hello -

Trying to create a query that will output additions to Azure security groups memberships. I am able to successfully output the information I need, but in the newValue field, it contains multiple different values. How do I omit the 'null' value and the security group IDs. I only want it to show that actual name of the security group. The way the logs are currently parsed, all of those values are in the same field - "properties.targetResources{}.modifiedProperties{}.newValue"

Query:

index="azure-activity" | search operationName="Add member to group" | stats count by "properties.initiatedBy.user.userPrincipalName", "properties.targetResources{}.userPrincipalName", "properties.targetResources{}.modifiedProperties{}.newValue", operationName, _time

 Output:

[marnall · Accepted solution]

One way you could do this is by spath-ing until you get a multivalue field containing each of the modifiedProperties json objects, then use mvfilter to filter that field to only the "Group.DisplayName" json object, then spath again to get the newValue:

| spath input=_raw path=properties.targetResources{}.modifiedProperties{} output=hold
| eval hold = mvfilter(like(hold,"%Group.DisplayName%"))
| spath input=hold path=newValue output=NewGroupName

[WumboJumbo675]

Awesome that seemed to do it, thank you so much.

index="azure-activity" | spath input=_raw path=properties.targetResources{}.modifiedProperties{} output=hold
| eval hold = mvfilter(like(hold,"%Group.DisplayName%"))
| spath input=hold path=newValue output=NewGroupName
| search operationName="Add member to group" | stats count by "properties.initiatedBy.user.userPrincipalName", "properties.targetResources{}.userPrincipalName", NewGroupName, operationName, _time

[h20]

this removes null and uid from the target group.

| search operationName="Add member to group"
| stats count by "properties.initiatedBy.user.userPrincipalName", "properties.targetResources{}.userPrincipalName", "properties.targetResources{}.modifiedProperties{}.newValue", operationName, _time
``` removes uid ```
| regex properties.targetResources{}.modifiedProperties{}.newValue!=".{8}-.{4}-.{4}-.{4}-.{12}"
``` removes null value ```
| search NOT properties.targetResources{}.modifiedProperties{}.newValue="null"
| rename "properties.initiatedBy.user.userPrincipalName" as initiated_user, "properties.targetResources{}.userPrincipalName" as target_user, "properties.targetResources{}.modifiedProperties{}.newValue" as group_name
| eval group = replace(group_name, "\"", "")
| eval initiated_user = lower(initiated_user), target_user = lower(target_user)