5 failed attempts for 5 users in 5 min

[sr_dhinesh]

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,

[sideview · Accepted solution]

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

[sr_dhinesh]

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

[stephane_cyrill]

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