Calculation of Percentage of problems with "Summarize" in DQL

[JhonU]

Hello, maybe you could help me, I have a query that counts the total events by category "event.category", when using the summarize command I cannot calculate the total events in this way to obtain the percentage it represents in an additional column, EX: %=100*(count per Category/Total)

fetch events//, from:now()
| filter event.kind == "DAVIS_PROBLEM"
| fieldsAdd dt.entity.service= affected_entity_ids[0]
| fieldsAdd dt.entity.service.name = entityName(dt.entity.service)
| filter event.status_transition == "CREATED"
| summarize a = count(), by:{event.category}

[krzysztof_hoja · Accepted solution]

It is possible to approach in 2 way to the problem.

1. "Ask again"

fetch events
| filter event.kind == "DAVIS_PROBLEM"
| filter event.status_transition == "CREATED"
| summarize a = count(), by:{event.category, event.kind}
| lookup [
  fetch events
  | filter event.kind == "DAVIS_PROBLEM"
  | filter event.status_transition == "CREATED"
  | summarize total = count(), by:{event.kind}
], sourceField:event.kind, lookupField:event.kind, fields:{total}
| fields event.category, a, ap=100*a/total

Using lookup command you can add (calculated by another query) total count to each of of rows. Then you you can divide particular count by total number of events. Lookup command requires joining fields to be specified. I used event.kind on both sided as it will have only one value for this query and no "fake key" needs to be produced.

1. "summarize + expand"

fetch events
| filter event.kind == "DAVIS_PROBLEM"
| filter event.status_transition == "CREATED"
| summarize a = count(), by:{event.category}
| summarize { total=sum(a), d=collectArray(record(event.category, a))}
| expand d
| fields event.category=d[event.category], a=d[a], ap=100*d[a]/total

After a basic query additional summarization needs to happen. This way we can calculate total (sum of individual counts). In order not to lose details, we need to collect components in arrays of pairs (event.category and a). After summarization we can bring back original row count using expand command after which each row will have total field ready to calculate percentage.

Kris