Splunk Search

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

tedder
Communicator

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)

1 Solution

Lowell
Super Champion

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.

View solution in original post

Lowell
Super Champion

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.

Lowell
Super Champion

Glad it works for you 😉

0 Karma

tedder
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...