Querying AAD Audit Logs

[dave8thomas]

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!

[#2 · Accepted solution]

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 

[#3]

Awesome, thanks Clive!! It's the simple things in life .. like quotes! :)