Forum Discussion
dave8thomas
Sep 24, 2019Copper Contributor
Querying AAD Audit Logs
Hello all,
I am piping my AAD audit logs to Log Analytics through the Diagnostic Logs, and then I want to set up some alerts if users are added to certain administrator roles..
I have got this far, but this returns nothing:
AuditLogs
| where Category == "RoleManagement"
| extend PropertiesJSON = parse_json(TargetResources)
| extend role = PropertiesJSON[0].modifiedProperties[1]['newValue']
| where role == 'SharePoint Service Administrator'
| project role
I think because the value I am searching for appears to be an Array of characters, rather than a string.. (same code, just taking out the filter)
AuditLogs
| where Category == "RoleManagement"
| extend PropertiesJSON = parse_json(TargetResources)
| extend role = PropertiesJSON[0].modifiedProperties[1]['newValue']
| project role
Any ideas how I can search for a term that is stored in the data like this?
Thanks!
Hi dave8thomas
You could just use ' ' around the string as there are "quotes" in the returned data
E.g.AuditLogs | where Category == "RoleManagement" | extend PropertiesJSON = parse_json(TargetResources) | extend role = PropertiesJSON[0].modifiedProperties[1]['newValue'] | where role == '"Company Administrator"'
or cleanup the returned data
AuditLogs | where Category == "RoleManagement" | extend PropertiesJSON = parse_json(TargetResources) | extend role = trim(@"[^\w]+", tostring(PropertiesJSON[0].modifiedProperties[1]['newValue']) ) | where role == "Company Administrator" // trims all non-word characters from start and end of the string // https://docs.microsoft.com/en-us/azure/kusto/query/trimfunction
- CliveWatson
Microsoft
Hi dave8thomas
You could just use ' ' around the string as there are "quotes" in the returned data
E.g.AuditLogs | where Category == "RoleManagement" | extend PropertiesJSON = parse_json(TargetResources) | extend role = PropertiesJSON[0].modifiedProperties[1]['newValue'] | where role == '"Company Administrator"'
or cleanup the returned data
AuditLogs | where Category == "RoleManagement" | extend PropertiesJSON = parse_json(TargetResources) | extend role = trim(@"[^\w]+", tostring(PropertiesJSON[0].modifiedProperties[1]['newValue']) ) | where role == "Company Administrator" // trims all non-word characters from start and end of the string // https://docs.microsoft.com/en-us/azure/kusto/query/trimfunction
- dave8thomasCopper ContributorAwesome, thanks Clive!! It's the simple things in life .. like quotes! 🙂