CODEWITHSUNDEEP
azurekqlazure-log-analyticsazure-monitor
teru
teru

Reputation: 1

How to get the difference between two rows gruping by columns in Azure Monitor KQL

I want to get difference value between two rows in Azure Log Analytics Workspace in KQL.

I can get them each host separately using "| where Hostname == 'host01'", "| extend Difference = Coalesce(Value - prev(Value), double(0))", but I want to get all hosts of them at once.

Sample data:

TimeGenerated Hostname Value
2024-01-01T00:10:00 host01 12
2024-01-01T00:10:00 host02 24
2024-01-01T00:10:00 host03 35
2024-01-01T00:00:00 host01 10
2024-01-01T00:00:00 host02 20
2024-01-01T00:00:00 host03 30

Expected result Output:

TimeGenerated Hostname Difference
2024-01-01T00:10:00 host01 2
2024-01-01T00:10:00 host02 4
2024-01-01T00:10:00 host03 5

Thanks in advance.

Upvotes: 0

Views: 149

Answers (1)

RithwikBojja
RithwikBojja

Reputation: 11393

Below KQL Query worked for me:

let RithTable = datatable(TimeGenerated: datetime, Hostname: string, Value: int)
[
    datetime(2024-01-01T00:10:00), "host01", 12,
    datetime(2024-01-01T00:10:00), "host02", 24,
    datetime(2024-01-01T00:10:00), "host03", 35,
    datetime(2024-01-01T00:00:00), "host01", 10,
    datetime(2024-01-01T00:00:00), "host02", 20,
    datetime(2024-01-01T00:00:00), "host03", 30
];
let test1=RithTable
| summarize Test= make_set(Value) by Hostname
| extend Test=array_sort_desc(Test)
| extend y=toint(Test[0]),z=toint(Test[1])
| extend diff= y-z
| project-away Test,y,z;
RithTable
| summarize Time=max(TimeGenerated) by Hostname
| join kind=inner test1 on Hostname
| project-reorder Time,Hostname,diff
| project-away Hostname1

Output:

enter image description here

Fiddle.

Upvotes: 1

Related Questions