CODEWITHSUNDEEP
azurekqlazure-data-explorer
Jerald Baker
Jerald Baker

Reputation: 1369

KQL - Get Sum of Values of Dynamic Columns

From a query, I get a result which has the three Columns: Service, ResponseCode and Column

Service StatusCode  Count
A       201             100
A       202             50
A       503             20
C       201             25

The response codes are dynamic and are not fixed.

I want the data transformed to one row per service, with each column representing the StatusCode, and then I want two more columns - SuccessStatusCount and FailStatusCount, that can sum the values of columns based on condition. Example output:

Service 201 202 503  SuccessStatusCount   FailStatusCount
A       100 50  20   150//(100+50)        20        
C       25  0   0    25                   0

I am able to get the first 4 columns using pivot:

let x = datatable (Service:string, ResponseCode: long, Count: long)
    [
    "A",201,100,
    "A",202,50,
    "A",503,20,
    "C",201,25
    ];
x | evaluate pivot(ResponseCode, sum(Count))

But how can I get the SuccessStatusCount and FailStatusCount ?

I don't want to do | extend SuccessStatusCount = 201+202+203+.... because the StatusCodes are dynamic and large in number. I just know that SuccessStatusCount should include the count of all calls where StatusCode >=200 and <500

How can I get it?

Upvotes: 0

Views: 291

Answers (1)

Yoni L.
Yoni L.

Reputation: 25955

here's one option to achieve that:

let T = datatable (Service:string, ResponseCode: long, Count: long)
[
  "A",201,100,
  "A",202,50,
  "A",503,20,
  "C",201,25
];
let totals = toscalar(
    T
    | extend what = case(ResponseCode between(200..299), "Success", "Failure")
    | summarize sum(Count) by Service, what
    | summarize make_bag(bag_pack(strcat(Service, "_", what),sum_Count))
);
T
| evaluate pivot(ResponseCode, sum(Count))
| extend SuccessStatusCount = coalesce(tolong(totals[strcat(Service, "_Success")]), 0),
         FailureStatusCount = coalesce(tolong(totals[strcat(Service, "_Failure")]), 0)
Service 201 202 503 SuccessStatusCount FailureStatusCount
A 100 50 20 150 20
C 25 0 0 25 0

Upvotes: 0

Related Questions