Search code examples
azurekqlazure-data-explorer

KQL Comparison to Find Changes

How would compare values in column to see if it's changed for a unique item. For example, how would I modify a query to only display a record if the serial number changed for a specific unique device. In this case there is a new record each day for each DeviceID. An example is below. I'm a noob and have no idea where to start. If my question doesn't make sense or you need additional info please let me know. Thanks in advance.

MyInventory_CL
| mvexpand parse_json(Health_s) | project Date, DeviceID, ['Type'] = Health_s['Type'], ['XOLabel'] = Health_s['XOLabel'],['Serial'] = Health_s['XOSerial Number'], ['Placement'] = Health_s['XOPlNumber'], Status

Date DeviceID Type Label Serial Placement Status

03/10/24 FHDFKJSKFJ5 SSD HFM001TD3GX013N 25359_8549_2286_F168_9FFA_5625_3214_0085 0 On

03/10/24 EUXMSFSG78 SSD KBG40ZNS512G 30100_0090_0000_0000_8CE3_8E04_0157_C6FA 0 On

03/10/24 NMLWHQA98 SSD KBG40ZNS512G H0100_0020_0000_0000_8CE3_8E04_0121_7017 0 On

03/10/24 WOOFTYUV36 SSD kBG50ZNS512G Z0000_0030_0000_0000_8CE3_8E04_0389_1C04 0 On

03/10/24 SVDFKJSKF98 SSD HFM001TD3GX014N L0000_0070_0000_0010_0008_0D05_000D_8ACB 0 On

03/09/24 FHDFKJSKFJ5 SSD HFM001TD3GX013N 5ACE4_2E10_2567_E6A5_2EE4_AC00_0000_0001 0 On

03/09/24 EUXMSFSG78 SSD KBG40ZNS512G 30100_0090_0000_0000_8CE3_8E04_0157_C6FA 0 On

03/09/24 NMLWHQA98 SSD KBG40ZNS512G H0100_0020_0000_0000_8CE3_8E04_0121_7017 0 On

03/09/24 WOOFTYUV36 SSD kBG50ZNS512G Z0000_0030_0000_0000_8CE3_8E04_0389_1C04 0 On

03/09/24 SVDFKJSKF98 SSD HFM001TD3GX014N L0000_0070_0000_0010_0008_0D05_000D_8ACB 0 On

03/08/24 FHDFKJSKFJ5 SSD HFM001TD3GX013N 5ACE4_2E10_2567_E6A5_2EE4_AC00_0000_0001 0 On

03/08/24 EUXMSFSG78 SSD KBG40ZNS512G 30100_0090_0000_0000_8CE3_8E04_0157_C6FA 0 On

03/08/24 NMLWHQA98 SSD KBG40ZNS512G H0100_0020_0000_0000_8CE3_8E04_0121_7017 0 On

03/08/24 WOOFTYUV36 SSD kBG50ZNS512G Z0000_0030_0000_0000_8CE3_8E04_0389_1C04 0 On

03/08/24 SVDFKJSKF98 SSD HFM001TD3GX014N L0000_0070_0000_0010_0008_0D05_000D_8ACB 0 On

Solution

  • KQL Comparison to Find Changes

    To compare column and find changes made in that column you can try with the below code. In first summarization, it calculates the distinct count of serial numbers for each device DeviceID and filter it by where distinctCountSerial > 1 now inner join is performed on the DeviceID and the second summarization is used for finding the maximum Date for each DeviceID and it again performs a inner join based on Date and DeviceID to get the expected result.

    Click this link to check the query in Data Explorer.

    Query I tried with:

    let TableA = datatable(Date: datetime, DeviceID: string, Type: string, Label: string, Serial: string, Placement: int, Status: string)
[
    datetime(2024-03-10), 'FHDFKJSKFJ5', 'SSD', 'HFM001TD3GX013N', '25359_8549_2286_F168_9FFA_5625_3214_0085', 0, 'On',
    datetime(2024-03-10), 'EUXMSFSG78', 'SSD', 'KBG40ZNS512G', '30100_0090_0000_0000_8CE3_8E04_0157_C6FA', 0, 'On',
    datetime(2024-03-10), 'NMLWHQA98', 'SSD', 'KBG40ZNS512G', 'H0100_0020_0000_0000_8CE3_8E04_0121_7017', 0, 'On',
    datetime(2024-03-10), 'WOOFTYUV36', 'SSD', 'kBG50ZNS512G', 'Z0000_0030_0000_0000_8CE3_8E04_0389_1C04', 0, 'On',
    datetime(2024-03-10), 'SVDFKJSKF98', 'SSD', 'HFM001TD3GX014N', 'L0000_0070_0000_0010_0008_0D05_000D_8ACB', 0, 'On',
    datetime(2024-03-09), 'FHDFKJSKFJ5', 'SSD', 'HFM001TD3GX013N', '5ACE4_2E10_2567_E6A5_2EE4_AC00_0000_0001', 0, 'On',
    datetime(2024-03-09), 'EUXMSFSG78', 'SSD', 'KBG40ZNS512G', '30100_0090_0000_0000_8CE3_8E04_0157_C6FA', 0, 'On',
    datetime(2024-03-09), 'NMLWHQA98', 'SSD', 'KBG40ZNS512G', 'H0100_0020_0000_0000_8CE3_8E04_0121_7017', 0, 'On',
    datetime(2024-03-09), 'WOOFTYUV36', 'SSD', 'kBG50ZNS512G', 'Z0000_0030_0000_0000_8CE3_8E04_0389_1C04', 0, 'On',
    datetime(2024-03-09), 'SVDFKJSKF98', 'SSD', 'HFM001TD3GX014N', 'L0000_0070_0000_0010_0008_0D05_000D_8ACB', 0, 'On',
    datetime(2024-03-08), 'FHDFKJSKFJ5', 'SSD', 'HFM001TD3GX013N', '5ACE4_2E10_2567_E6A5_2EE4_AC00_0000_0001', 0, 'On',
    datetime(2024-03-08), 'EUXMSFSG78', 'SSD', 'KBG40ZNS512G', '30100_0090_0000_0000_8CE3_8E04_0157_C6FA', 0, 'On',
    datetime(2024-03-08), 'NMLWHQA98', 'SSD', 'KBG40ZNS512G', 'H0100_0020_0000_0000_8CE3_8E04_0121_7017', 0, 'On',
    datetime(2024-03-08), 'WOOFTYUV36', 'SSD', 'kBG50ZNS512G', 'Z0000_0030_0000_0000_8CE3_8E04_0389_1C04', 0, 'On',
    datetime(2024-03-08), 'SVDFKJSKF98', 'SSD', 'HFM001TD3GX014N', 'L0000_0070_0000_0010_0008_0D05_000D_8ACB', 0, 'On'
];
TableA
| summarize distinctCountSerial = count_distinct(Serial) by DeviceID
| where distinctCountSerial > 1
| join kind=inner (
    TableA
) on DeviceID
| summarize Date = max(Date) by DeviceID
| join kind=inner (
    TableA) on Date, DeviceID
| project Date, DeviceID, Type, Label, Serial, Placement, Status

    Output: enter image description here