topic Re: Calculation of Percentage of problems with "Summarize" in DQL in DQL

Calculation of Percentage of problems with "Summarize" in DQL

JhonU — Sun, 30 Jun 2024 18:45:31 GMT

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}

Re: Calculation of Percentage of problems with "Summarize" in DQL

krzysztof_hoja — Mon, 01 Jul 2024 15:56:35 GMT

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

Re: Calculation of Percentage of problems with "Summarize" in DQL

Aaron_L — Fri, 04 Apr 2025 14:48:28 GMT

Hi Kris,

We are trying to do a similar thing, only using Log data. From a query cost perspective, would the summarize and expand option be "cheaper", as we don't have to run a subquery?

Thanks,
Aaron

Re: Calculation of Percentage of problems with "Summarize" in DQL

krzysztof_hoja — Mon, 04 May 2026 11:58:48 GMT

Yes!