topic Re: Best Way to Do Subsearch on Event Types and Have The Subsearch Check For Certain Threshold? in Knowledge Management

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

SplunkLunk — Tue, 29 Sep 2020 13:33:15 GMT

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.

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

cpetterborg — Wed, 05 Apr 2017 17:54:16 GMT

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.

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

SplunkLunk — Tue, 29 Sep 2020 13:33:18 GMT

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.

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

woodcock — Wed, 05 Apr 2017 18:33:04 GMT

See my (unaccepted) answer in the other question.

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

cpetterborg — Wed, 05 Apr 2017 18:38:54 GMT

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.

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

woodcock — Wed, 05 Apr 2017 18:39:24 GMT

Like this:

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

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

woodcock — Wed, 05 Apr 2017 18:40:17 GMT

This is VERY similar to my answer to your previous question.

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

SplunkLunk — Wed, 05 Apr 2017 18:44:51 GMT

Thanks for the reply. I guess I'm not understanding something. Your other comment is still only searching for failed logins (I think). What I need to do is search for all login events (success and failure) for the day. Then I need to search those events for any user that had more than five failed logins. After I determine who had more than five failed logins, I need to display all login events (success and fail) for those people. This will allow me to create a table for users who had more than five failed logins and also show if they had a successful login that day. I can sort by time and I would expect to see failed logins followed by a successful login if they forgot their password.

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

SplunkLunk — Wed, 05 Apr 2017 18:58:48 GMT

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.

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

woodcock — Wed, 05 Apr 2017 19:07:47 GMT

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

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

SplunkLunk — Wed, 05 Apr 2017 19:16:54 GMT

Looking at this search makes sense. However, it's pulling up all the success/fails events for the day not just the success/fail events for users who had more than five failed logins. It's like it's ignoring the where Failures > 5 statement. In the statistics tab in Splunk it's pulling up zero results. In the events tab it's pulling up 545 events. I would expect it to only pull up 17 events as only one user yesterday had more than five failed logins (15 failed and two success).

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

woodcock — Wed, 05 Apr 2017 19:28:00 GMT

OK, I think that I know what is happening. Do you mean "5 CONSECUTIVE failures (without a successful login/logout between any of them)"? If so, then you should say that and then I agree that my solution needs adjusting.

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

SplunkLunk — Tue, 29 Sep 2020 13:33:24 GMT

It doesn't need to be consecutive. I'm running the search for the previous day. So I need to search all login events (it's either login_success or login_fail) for the previous day. I then need to search those results for any users that had more than five "login_fail" events. I then need to create a table with all login success/fails for those users. I can add a timestamp and then sort by most recent time. It may be the case that the failures are consecutive (I would expect that if they forgot their password) but wouldn't necessarily be the case. If that ends up being the case, the app owner might contact them and ask them some questions. If it turns out to be the case they just fat fingered their password several times during the day, the app owner wouldn't contact them.

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

woodcock — Wed, 05 Apr 2017 19:42:54 GMT

Just adjust login_pass to login_success (I will edit, too) and it should do all that.

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

SplunkLunk — Wed, 05 Apr 2017 19:59:01 GMT

Tried that already (I figured that's what you meant so I adjusted before I ran). I get 545 events and nothing in the stats panel. I would have expected 17 events. (15 fail/2 success)

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

woodcock — Wed, 05 Apr 2017 20:52:31 GMT

Are you sure that field user is really spelled (capitalization counts) user? That is the only reason I can think for it not to work.

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

SplunkLunk — Thu, 06 Apr 2017 13:17:28 GMT

Triple checked and I've spelled "user" correctly. Ran the search this morning and the same result. Today I get back 581 results. It's like it's doing the first part of the search but ignoring the > 5 portion.

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

SplunkLunk — Tue, 29 Sep 2020 13:33:44 GMT

As soon as I remove the |where Failures > 5 part of the search I raw events in the stats panel. I'm spelling Failures right because I cut and paste the word from the first part of the search. So my query where I get my 581 events and 253 raw events under the stats panel looks like this:

index=[my index] source=[my source] sourcetype=my sourcetype
|stats count(eval(event=login_fail)) AS Failures list(_raw) AS Events values(ip) AS IPs values(url_60) AS "URL" BY user

Adding |where Failures > 5 at the end still shows 581 events, but no raw events in the stats panel.

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

SplunkLunk — Thu, 06 Apr 2017 13:37:59 GMT

So I know why it isn't reporting any Failures > 5 but I don't know why it is happening. When I run the search, it is supposed to count up any Failures so it can match any users with Failures > 5. The raw events that are coming back in the stats panel all show Failures=0. So something with the eval or count isn't working properly.

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

woodcock — Thu, 06 Apr 2017 14:31:03 GMT

I know what it is! Hang see updated answer! It needs double-quotes around "login_fail".