0

I created a custom data type to store some configuration of an external product. So each day I send the configuration of this specific product / service ( multiple rows but with identical data model) to the Log Analytics data store.

Is there a possibility to show which rows gets added or removed between multiple days? The data structure is always the same f.e.

MyCustomData_CL | project Guid_g , Name_s, URL_s

I would like to see which records gets added / removed at which time. So basically compare every day with the previous day.

How could I accomplish this with Kusto query language?

Best regards, Jens

Jens
  • 181
  • 1
  • 13

1 Answers1

4

Next query uses full-outer join to compare two sets (one from previous day, and one from the current day). If you don't have datetime column in your table, you can try using ingestion_time() function instead (that reveals the time data was ingested into the table).

let MyCustomData_CL =
datatable (dt:datetime,Guid_g:string , Name_s:string, URL_s:string)
[
datetime(2018-11-27), '111', 'name1', 'url1',
datetime(2018-11-27), '222', 'name2', 'url2',
//
datetime(2018-11-28), '222', 'name2', 'url2',
datetime(2018-11-28), '333', 'name3', 'url3',
];
let data_prev = MyCustomData_CL | where dt between( datetime(2018-11-27) .. (1d-1tick));
let data_new =  MyCustomData_CL | where dt between( datetime(2018-11-28) .. (1d-1tick));
data_prev 
| join kind=fullouter (data_new) on Guid_g , Name_s , URL_s
| extend diff=case(isnull(dt), 'Added', isnull(dt1), 'Removed', "No change")

Result:

dt         Guid_g Name_s URL_s dt1       Guid_g1 Name_s1 URL_s1 diff
2018-11-28 333    name3  url3                                   Added
2018-11-27 111    name1  url1                                   Removed
2018-11-27 222    name2  url2 2018-11-28 222    name2     url2  No change
Alexander Sloutsky
  • 2,827
  • 8
  • 13
  • Thanks a lot for this. I'm optimizing it right now but the fullouter is exactly what I needed – Jens Nov 30 '18 at 10:29