using top and stats in the same query? aggregated data and nonaggregated data?

[tedder]

I'm trying to do a search like this:

index="errorlogs" | rex field=_raw "EXCEPTION:\s(?<exceptiontext>.*)" | stats count AS ecount | top exceptiontext

The idea is that I'm going to save it as a search, and display the "top exceptiontext", but alert when ecount is over a specific number. Why? Well, once I use "top", the saved search "alert on event count" thinks the number of events is 10, which is the number of lines in top.

I could do this with two searches, but.. there must be a better way.

(and yes, I know I could do field extraction on the exceptiontext)

[Lowell · Accepted solution]

So you want to trigger the alert action of your saved search if the total number of events exceeds some limit (like 100 events), but you want the content of the alert to contain the more standard "top"-like output. I did some playing around, and came up with two different options for you.

Option 1: Use streamstats to get the total count, and use the "by" clause of top (just as a way of preserving the total count field). Name your "count" field with a leading "_" so that it doesn't show up on on the final output. Try something like this:

index="errorlogs" | rex "EXCEPTION:\s(?<exceptiontext>.*)" | eventstats count AS _total_count | top exceptiontext by _total_count

Then set your alerting condition to a search expression:

 where _total_count>100

Option 2: Keep your search pretty much as-is, and add some logic to your custom alerting condition:

Search:

index="errorlogs" | rex "EXCEPTION:\s(?<exceptiontext>.*)" | top exceptiontext

For your alerting condition, use:

stats sum(count) as total_count | where total_count>100

This approach is slightly easier since it doesn't mess with your main search, but it's also slightly less accurate. For example, the total_count will only include your top 10 counts; Since your adding up largest 10 values, this probably will not be an issue. It could work for your or against you, but I figured I should point out the slight difference in logic between these two approaches.

[tedder]

I had to read this several times to understand the difference. There will be a long (and large) tail, so door #2 isn't accurate enough for this specific case. Option 1 does the trick. And I simply folded the "where" into the query to prove it worked before scheduling a search.

[Lowell]

Glad it works for you 😉