Splunk Search

5 failed attempts for 5 users in 5 min

sr_dhinesh
Path Finder

index=windows "fail" | stats count by user | where count >4
This query is absolutely working as expected for my alert and i will trigger the condition for 5 min and problem resolved, but i want more information to it. LIke the time, host, message, field1, field2.
Stats is not helping me with that, eventstats will give me individual events whereas i want the count. Is there any other way other than using stats to accomplish this or can i work it out with stats,

Tags (3)
1 Solution

sideview
SplunkTrust
SplunkTrust

You need to tell stats to carry those other fields along, and at the same time how you want to carry them along.

stats count by user will only give two fields back, count and user. The most usual path would be with values(foo) as foo, which says "take all the values of foo you see for that user along the way and roll them up into a multivalue field called foo". Even if there is often or always only one value in the results, I recommend values() to catch unexpectedness when there are two or more there. If you fall into the habit of using first() or last() always, you can miss crucial logic in edge cases.

So with values(foo) as foo, it would look like:

index=windows "fail" | stats count values(host) as host values(message) as message values(field1) as field1 values(field2) as field2 by user | where count >4

To save some characters, I prefer to restrict the fields down with the fields command, then do values(*) as *, like so

index=windows "fail" | fields host message field1 field2 user | stats count values(*) as * | where count>4

but be very careful with using "*" in stats. Without something like this fields command to narrow down the incoming fields it can blow up on you and slow your search to a crawl.

There's a good blog post giving an overview of stats. http://blogs.splunk.com/2014/04/01/search-command-stats-eventstats-and-streamstats-2/
and of course the official reference page for the stats command is very important. http://docs.splunk.com/Documentation/Splunk/6.2.2/SearchReference/Stats

View solution in original post

0 Karma

sideview
SplunkTrust
SplunkTrust

You need to tell stats to carry those other fields along, and at the same time how you want to carry them along.

stats count by user will only give two fields back, count and user. The most usual path would be with values(foo) as foo, which says "take all the values of foo you see for that user along the way and roll them up into a multivalue field called foo". Even if there is often or always only one value in the results, I recommend values() to catch unexpectedness when there are two or more there. If you fall into the habit of using first() or last() always, you can miss crucial logic in edge cases.

So with values(foo) as foo, it would look like:

index=windows "fail" | stats count values(host) as host values(message) as message values(field1) as field1 values(field2) as field2 by user | where count >4

To save some characters, I prefer to restrict the fields down with the fields command, then do values(*) as *, like so

index=windows "fail" | fields host message field1 field2 user | stats count values(*) as * | where count>4

but be very careful with using "*" in stats. Without something like this fields command to narrow down the incoming fields it can blow up on you and slow your search to a crawl.

There's a good blog post giving an overview of stats. http://blogs.splunk.com/2014/04/01/search-command-stats-eventstats-and-streamstats-2/
and of course the official reference page for the stats command is very important. http://docs.splunk.com/Documentation/Splunk/6.2.2/SearchReference/Stats

0 Karma

sr_dhinesh
Path Finder

Thank Stephen and sideview..
I guess sideview option works best for me

0 Karma

stephane_cyrill
Builder

Hi sr_dhinesh,
Just try this:

index=windows "fail" | stats count by user |table _time user count host message | where count >4

but you have to make sure that all the fields you want to display are already extracted

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...