Knowledge Management

Best Way to Do Subsearch on Event Types and Have The Subsearch Check For Certain Threshold?

SplunkLunk
Path Finder

Me again,

So someone was nice enough to introduce me to the eventstats command and I'm using it on the following search to find someone who had more than five failed logins and display all the events, not just a summary:

index=[my index] source=[my source] sourcetype=[my sourcetype] event=login_fail | eventstats count as Count values(event) as Event by user|where Count > 5

What I'd like to do is grab all the events login_fail and login_success and show all of those for people that have more than five failed logins to see if there was a successful login after a bunch of failed logins. So I really need to search for:

index=[my index] source=[my source] sourcetype=[my sourcetype] event=login* and then do a subsearch in those events for any event=login_fail >5, and display both success and fail events.

Is a subsearch the way to do that or is there something else that would be less convoluted? Thanks for any help.

Tags (1)
0 Karma
1 Solution

cpetterborg
SplunkTrust
SplunkTrust

I saw your earlier post and the reply, but I was thinking that you could get what you wanted using a subsearch. For example, the following type of search might help you figure out what you want to do:

index=my_index source=my_source sourcetype=my_sourcetype [search index=my_index source=my_source sourcetype=my_sourcetype event=login_fail | eventstats count as Count by user|where Count > 5 | fields user] | stats values(raw) by user

This would give you a list of the full events by the user where the user had more than 5 failed logins.

There are other ways to solve this as well, using transactions. That method would give you a list of the users that had failed logs, but no valid log attempts:

index=my_index source=my_source sourcetype=my_sourcetype | transaction user | stats latest(user) | search event=login_fail

That should give you a list of the ones whose latest login attempt failed.

View solution in original post

0 Karma

woodcock
Esteemed Legend

You can do both at the same time without a subsearch and it will be faster and more efficient. See my answer.

0 Karma

cpetterborg
SplunkTrust
SplunkTrust

I saw your earlier post and the reply, but I was thinking that you could get what you wanted using a subsearch. For example, the following type of search might help you figure out what you want to do:

index=my_index source=my_source sourcetype=my_sourcetype [search index=my_index source=my_source sourcetype=my_sourcetype event=login_fail | eventstats count as Count by user|where Count > 5 | fields user] | stats values(raw) by user

This would give you a list of the full events by the user where the user had more than 5 failed logins.

There are other ways to solve this as well, using transactions. That method would give you a list of the users that had failed logs, but no valid log attempts:

index=my_index source=my_source sourcetype=my_sourcetype | transaction user | stats latest(user) | search event=login_fail

That should give you a list of the ones whose latest login attempt failed.

0 Karma

SplunkLunk
Path Finder

This appears to still only give me login_fail events. When I run against my source I'm getting 15 failed logins. There should be 2 login_success events as well for a total of 17 events I want to search within, but still display all success/fail events so I can display a table off of those results.

0 Karma

cpetterborg
SplunkTrust
SplunkTrust

Using which search? The second will only give you the list of the users with failed logins as their LAST attempt when they had more than 5 fails. The first should have given you a list of all the ones with more than 5 failed attempts, and list all their attempts, failed or successful. Without getting into your exact data it is hard to make sure that there isn't something else that I missed.

So there were 2 additional users that had more than 5 failed login attempts, but also had a successful login?

I guess I mistakenly thought that you ultimately wanted to know the people that had more than 5 failed logins without a successful login. Sorry about the misunderstanding. So you in the end want to know all the login events for each of the users that have more than 5 failed logins? If that is the case, then the first search would do that better than the second.

0 Karma

SplunkLunk
Path Finder

You said it exactly. My fault for the misunderstanding. I just wasn't explaining it well. Your one sentence explained it perfectly. want to know all the login events for each of the users that have more than 5 failed logins. However, using your first search is still only returning failed logins. Not sure what I'm doing wrong.

0 Karma

SplunkLunk
Path Finder

So this is what I ended up doing. I used your subsearch idea and got what I wanted. My final search ended up looking like:

index=[my index] source=[my source] sourcetype=my sourcetype [search event=login_fail
|eventstats count as Count by user|where Count > 5 | fields user]
|sort user,-_time
|rename user as User, _time as Time, ip as "IP Address", url_60 as "URL", event as Event
|table Time, User, "IP Address", Event,URL
|convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

This gives me a nice table I can use to send to the app administrator each morning via an email alert. I just have it trigger if the threshold is greater than 0. I'm sure my search could be refined but this seems to give me what I'm looking for.

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