Forum Discussion

dave8thomas's avatar
dave8thomas
Copper Contributor
Sep 24, 2019

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 
    

     

  • 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 
    

     

    • dave8thomas's avatar
      dave8thomas
      Copper Contributor
      Awesome, thanks Clive!! It's the simple things in life .. like quotes! 🙂

Resources