Conditioning on an aggregated value

[ryan-balcerzak]

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!

[sinisa_zubic · Accepted solution]

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

[ryan-balcerzak · Accepted solution]

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, "🔴")