CODEWITHSUNDEEP
kql
PMO1948
PMO1948

Reputation: 2554

KQL summarize by count and then filter

The goal of my query is to see if at any given minute we have more than 500 logs.

I have this line at the end | summarize count() by bin(env_time, 1m), but now I want to know if I can add filtering beyond that to only see rows with more than 500 results. Something along the lines of:

| totals = summarize count() by bin(env_time, 1m)
| where totals>500

Is there a way to do this correctly in KQL?

TIA

Upvotes: 2

Views: 1130

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

let t = materialize(range i from 1 to 9700 step 1 | extend env_time = ago(20m * rand()));
t
| summarize count() by bin(env_time, 1m)
| where count_ > 500
env_time count_
2023-01-08T09:54:00Z 531
2023-01-08T09:56:00Z 501
2023-01-08T09:57:00Z 501
2023-01-08T10:00:00Z 510
2023-01-08T10:03:00Z 502

Fiddle

or (with alias for count())

let t = materialize(range i from 1 to 9700 step 1 | extend env_time = ago(20m * rand()));
t
| summarize rows_per_minute = count() by bin(env_time, 1m)
| where rows_per_minute > 500
env_time rows_per_minute
2023-01-08T09:51:00Z 539
2023-01-08T09:57:00Z 501
2023-01-08T10:02:00Z 516

Fiddle

Upvotes: 3

Related Questions