kqlchange-tracking

Change tracking using Kusto queries


I have data in kusto table that gets updated with every deployment. I want to check what change was made in a particular deployment

Column A Column B Modified at
Row 1 Value 1 Dec 15
Row 2 Value 2 Dec 15
Row 3 Value 6 Dec 15
Row 4 Value 5 Dec 15
Row 1 Value 1 Dec 14
Row 2 Value 2 Dec 14
Row 3 Value 6 Dec 14
Row 4 Value 4 Dec 14
Row 1 Value 1 Dec 13
Row 2 Value 2 Dec 13
Row 3 Value 3 Dec 13
Row 4 Value 4 Dec 13

Now if we need to track change by column A values in column B Row 3 has a value change on Dec 14 load (From Value 3 to Value 6 ) Row 4 has a value change on Dec 15 load (From Value 4 to Value 5)

I want to extract data on each date as for what all has changed like to project only changed rows for a date ,and I can run this query daily to find the daily change tracking

Column A Column B Modified at
Row 4 Value 5 Dec 15
Column A Column B Modified at
Row 4 Value 5 Dec 15

Solution

  • All changes

    datatable (["Column A"]:string, ["Column B"]:string, ["Modified at"]:string)
    [
       ,"Row 1" ,"Value 1" ,"Dec 15"
       ,"Row 2" ,"Value 2" ,"Dec 15"
       ,"Row 3" ,"Value 6" ,"Dec 15"
       ,"Row 4" ,"Value 5" ,"Dec 15"
       ,"Row 1" ,"Value 1" ,"Dec 14"
       ,"Row 2" ,"Value 2" ,"Dec 14"
       ,"Row 3" ,"Value 6" ,"Dec 14"
       ,"Row 4" ,"Value 4" ,"Dec 14"
       ,"Row 1" ,"Value 1" ,"Dec 13"
       ,"Row 2" ,"Value 2" ,"Dec 13"
       ,"Row 3" ,"Value 3" ,"Dec 13"
       ,"Row 4" ,"Value 4" ,"Dec 13"
    ]
    | partition hint.strategy=native by ['Column A']
      (
            order by ['Modified at'] asc
            | where ["Column B"] != prev (["Column B"]) and row_number() != 1
      )
    
    Column A Column B Modified at
    Row 4 Value 5 Dec 15
    Row 3 Value 6 Dec 14

    Fiddle