Splunk Search

How to use field value count to filter the search result?

Kwip
Contributor

I want to generate a search which generates results based on the threshold of field value count.

I.E.,, My base search giving me 3 servers in host field..
server1
server2
server3.

I want the result to be generated in anyone of the host count is greater than 10.

Server1>10 OR sever2>10 OR server3>10.

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

Is your search giving you counts?

If that's the problem, you normally solve that with the stats command.

Something like this:

... my search that returns raw events ... | stats count by host

That should give you a list more like the following

server    count
server1   12
server2   4
server3   8

Now that we have the counts available, you can add a search to search your search. LOL, love that.

... my search that returns raw events ... | stats count by host | search count>10

That will then search those above results and return any rows where the count is greater than 10.

Let us know how that works!

Happy Splunking!

-Rich

View solution in original post

Richfez
SplunkTrust
SplunkTrust

Is your search giving you counts?

If that's the problem, you normally solve that with the stats command.

Something like this:

... my search that returns raw events ... | stats count by host

That should give you a list more like the following

server    count
server1   12
server2   4
server3   8

Now that we have the counts available, you can add a search to search your search. LOL, love that.

... my search that returns raw events ... | stats count by host | search count>10

That will then search those above results and return any rows where the count is greater than 10.

Let us know how that works!

Happy Splunking!

-Rich

Kwip
Contributor

Wow Great!! That works!! Thank you..!!

0 Karma

Kwip
Contributor

I want to generate alert for the below query if it gives more than 10 results per minute for the continues 10 minutes. I can achieve it from the below query and setting the alert condition as search result equal to 10 and scheduling the alert to run on every 1 minute.

Query - eventtype=prod_servers sc_status!=200 | eval ComputerStatus=host."-"sc_status | bucket span=1m _time | stats count as TotalErrCount by _time ComputerStatus | where TotalErrCount >10 | stats value(ComputerStatus ) as ErrorCode count by _time

But the problem is the eval field ComputerStatus comprises of many values (I.e, server1-404, server2-404, server1-500, server3-403.,,,) so in 10 minutes span I am getting result like as below

02:27 10:01-server2-404
02:27 10:02-server2-404
02:27 10:03-server1-500
02:27 10:04-server1-500
02:27 10:05-server1-500
02:27 10:06-server1-500
02:27 10:07-server3-403
02:27 10:08-server3-403
02:27 10:09-server3-403
02:27 10:10-server2-404

My alert conditions are satisfied for the above results and alert getting triggered. but I want the alert to be generated if any one of the filed value count is greater than 10 per 1 minute for the continuous 10 minutes.
Example
02:27 10:01-server2-404 OR server1-500
02:27 10:02-server2-404 OR server1-500
02:27 10:03-server2-404 OR server1-500
02:27 10:04-server2-404 OR server1-500
02:27 10:05-server2-404 OR server1-500
02:27 10:06-server2-404 OR server1-500
02:27 10:07-server2-404 OR server1-500
02:27 10:08-server2-404 OR server1-500
02:27 10:09-server2-404 OR server1-500
02:27 10:10-server2-404 OR server1-500

0 Karma

Richfez
SplunkTrust
SplunkTrust

I'm not sure I understand what you are trying to do, could you please revise your example you have above to use real numbers? In what you've written it's not clear why the second example has server2-404 OR server1-500. And why are we creating the string ComputerName instead of just stats count by _time, host, sc_status?

But perhaps you are trying to find where a particular server has been over 10 for ALL 10 minutes? Using your own search above as the start, you might be able to do that like this:

sourcetype="access_combined" sc_status!=200 | eval ComputerStatus=host."-".sc_status| bucket span=1m _time | stats count as TotalErrCount by _time ComputerStatus | where TotalErrCount >10 | transaction ComputerStatus | where eventcount>=10

So, since we've whittled down the amount of data to only those with enough errors, we just run a transaction on them then search where there are ten returned for that ComputerStatus. You can use stats for this easily enough too, but in either case this is finicky in that it needs to have all static timeframes everywhere or else it'll go wrong (e.g. if you run it over 9 minutes, well, nothing would ever show up...).

If instead you are trying to get it to list all the servers that went over in a particular 10 minute time frame listed together, well, I don't have data with as many errors as you, so I changed mine to a 7-day search hour by hour to get it to show up right,

sourcetype="access_combined" status!=200 | eval ComputerStatus=host."-".status | bucket span=1h _time | stats count as TotalErrCount by _time ComputerStatus | where TotalErrCount >10  | stats list(ComputerStatus) as ComputerStatus by _time

But that's basically the search you already showed, I think.

Another thought...

sourcetype="access_combined" status!=200 
| bucket span=1h _time 
| stats count as TotalErrCount by _time host status 
| where TotalErrCount >3 
| rename _time AS time 
| mvcombine delim=", " host 
| mvcombine delim=", " status 
| fieldformat time=strftime(time, "%Y-%m-%d %H:%M:%S")

(Keep in mind I have "status" not "sc_status" and I've binned by 1 hour increments and only looking where TotalErrCount is above 3, but maybe try that?)

So if these don't help, a description of what you are after and maybe an accurate example would help us figure out just what you are trying to do.

Thanks!
-Rich

0 Karma

Kwip
Contributor

Than you for the detailed explanation.
This query did the trick!
sourcetype="access_combined" sc_status!=200 | eval ComputerStatus=host."-".sc_status| bucket span=1m _time | stats count as TotalErrCount by _time ComputerStatus | where TotalErrCount >10 | transaction ComputerStatus | where eventcount>=10

0 Karma

rjthibod
Champion

When you say you want the result to be generated for "anyone of host count", is there some other field in the data that you care about, or do you only care about filtering only based on the host field count?

Also, this approach can be best optimized if you share your search. It would be helpful to know if we can use tstats for what you are doing, but that requires you share the search string or at least part of it.

0 Karma

Kwip
Contributor

Thank you!!

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 ...