topic Conditioning on an aggregated value in DQL

Conditioning on an aggregated value

ryan-balcerzak — Thu, 12 Oct 2023 17:54:52 GMT

I have a question regarding a query using an If() statement.
Query:
fetch logs, scanLimitGBytes: -1
| filter matchesPhrase(content,"*******") OR matchesPhrase(content,"******")
| filter matchesPhrase(content,"*ACCESS")
| parse content, "************-'"
| filter channel != "null"
| summarize count=count(),by:{channel}

Result is screenshot. My question is, my customer wants to add another field such as "isLive" that has the value TRUE, if and only if the count() value is the greatest amount all records. In the example shown, a value of true would appear next to the blue channel because the count() is the maximum among both records. I've been trying this with an if() statement with no results because they cannot contain aggregations. Any ideas of how to accomplish this? Thank you!

Re: Conditioning on an aggregated value

sinisa_zubic — Thu, 12 Oct 2023 16:17:47 GMT

hi @ryan-balcerzak

This query should work.

and please check out following youtube video where I explain how the last part of the query is crafted: https://www.youtube.com/watch?v=Lgv5rxlEJ0Q

Re: Conditioning on an aggregated value

ryan-balcerzak — Thu, 12 Oct 2023 17:55:46 GMT

Here is what the query looked like as a final solution:

fetch logs, scanLimitGBytes: -1

| filter matchesPhrase(content,"*********") OR matchesPhrase(content,"**********")

| filter matchesPhrase(content,"*ACCESS")

| parse content, "*************-'"

| filter channel != "null" | summarize count=count(),by:{channel}

| summarize array = collectArray(record(channel=channel, count=count)), count = collectArray(count)

| fieldsAdd count_max = arrayMax(count)

| expand array

| fields `Channel` = array[channel], `count`=array[count], isLive = if(array[count] == count_max, "🔴")