How to Display Each Event of User with "X" Number of Failed Logins

[SplunkLunk]

Good morning,

I have the following search:

index=[my index] source=[my source] sourcetype=[my sourcetype] event=login_fail|stats count as Count values(event) as Event values(ip) as "IP Address" values(url_60) as "URL" by user| where Count > 5|sort -Count|rename user as User

Right now it displays a summary of the events. What I'd really like to do is determine every user that had more than five failed logins and display each event on a separate line. I'll add a timestamp as well to the alert/events. Any advice would be appreciated. Thanks.

[woodcock]

We could do this with eventstats and show the raw events but I think the problem that you really have is that you are using values(event) instead of list(_raw); try this:

index=[my index] source=[my source] sourcetype=[my sourcetype] event=login_fail
| stats count AS Count list(_raw) AS Events values(ip) AS IPs values(url_60) AS "URL" BY user
| where Count > 5|sort -Count|rename user as User

[SplunkLunk]

This appears to give me all the failed_login events for the day, not just any user greater than five. I cut and paste your suggestion to make sure I wasn't making a typo.

[woodcock]

That seems impossible; the | where Count > 5 will unquestionably limit the ending result set.

[somesoni2 · Accepted solution]

Try like this. Using eventstats instead of stats( which does the aggregation) so that all actual events are retained. You probably would need to include appropriate table command in case you want a table visualization else, you could use Event visualization with following

index=[my index] source=[my source] sourcetype=[my sourcetype] event=login_fail|eventstats count as Count by user| where Count > 5|sort -Count

[SplunkLunk]

Thank you. That looks like what I need. Much appreciated. I did not know about the eventstats command. Makes sense given them name.