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,
TargetResources[0].modifiedProperties,
InitiatedBy.user.userPrincipalName

 

The problem is the output is "oldValue" and "newValue" and every parameter is included, not just the changed values (see screenshot). Is it possible to query the unique values between the two to know what actually changed?

 

Thank you

 

 

3 Replies

  • 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