topic How to Display Each Event of User with "X" Number of Failed Logins in Splunk Search

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

SplunkLunk — Tue, 29 Sep 2020 13:27:51 GMT

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.

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

somesoni2 — Wed, 05 Apr 2017 15:48:41 GMT

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

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

SplunkLunk — Wed, 05 Apr 2017 15:54:04 GMT

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

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

woodcock — Wed, 05 Apr 2017 15:56:42 GMT

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

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

SplunkLunk — Wed, 05 Apr 2017 16:09:36 GMT

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.

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

woodcock — Wed, 05 Apr 2017 18:36:06 GMT

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