Forum Discussion

mikey365's avatar
mikey365
Brass Contributor
Mar 14, 2023

Trying to get the unique values of "newValue" and "oldValue" in AuditLogs using Kusto

My query is: AuditLogs | where Category == "Policy" | where AdditionalDetails contains "conditional access" | project format_datetime(ActivityDateTime, 'MM-dd-yyyy hh:mm '), ActivityDisplayName...
SuryaJ's avatar
SuryaJ
Icon for Microsoft rankMicrosoft
Mar 27, 2023

Hi mikey365 ,

Can you try this ? : 

let device_mappings = datatable(col1:dynamic)
[dynamic({"displayName":"conditional","oldvalue":{"id":"123","Name":"Surya","dept":"maths"},"newvalue":{"id":"123","Name":"Surya","dept":"science"}})];
device_mappings
| where col1.displayName=="conditional"
| extend oldvalue=col1.oldvalue, newvalue=col1.newvalue
| mv-expand  oldvalue, newvalue // to explode old and new values to multiple records
| summarize make_set(oldvalue), make_set(newvalue) // for use in set_difference
| project newValues= set_difference(set_newvalue,set_oldvalue) // compares new and old values and gets the New/changed key value pair

The output of this will be the changed value only:

 

  • mikey365's avatar
    mikey365
    Brass Contributor
    Mar 27, 2023

    Hi SuryaJ 

     

    I get this (Not sure where to add "AuditLogs, | where category == "policy"

    etc

     

     

     

     

    :

    • SuryaJ's avatar
      SuryaJ
      Icon for Microsoft rankMicrosoft
      Mar 27, 2023
      The query I gave was just an example. You can translate this to your data. For Example, I used co1.displayname=="conditional" which you can replace with category == "policy". This is not a literal query but the structure should help with your case.
      1. Use mv-expand on oldvalue and newvalue
      2. Use make_set
      3. Use set_difference

Resources