Archive

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

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 loginfail and loginsuccess 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

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

Esteemed Legend

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

Esteemed Legend

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

Path Finder

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

0 Karma

Esteemed Legend

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.

0 Karma

Path Finder

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 loginsuccess or loginfail) 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.

0 Karma

Esteemed Legend

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

0 Karma

Path Finder

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)

0 Karma

Esteemed Legend

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.

0 Karma

Path Finder

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.

0 Karma

Path Finder

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=loginfail)) 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.

0 Karma

Path Finder

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.

0 Karma

Esteemed Legend

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

0 Karma

Path Finder

I'm afraid to tell you I already tried that too and same result. I've been trying everything I can think of this morning. I did find that if you take out loginfail and just use event, it adds them up as expected (but includes both success and fail). So, the eval statement is working just using event. It's like it's treating event=loginfail as a variable it can't match on and returns zero results under "Failures". I was sure the quotes would work, but it behaves the same way.

0 Karma

Esteemed Legend

OK, try this then. It will work but is not as clear as the original answer but this one MUST work:

index=[my index] source=[my source] sourcetype=[my sourcetype] (event=login_fail OR event=login_success)
| stats count(eval(searchmatch("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

Path Finder

Winner, winner, chicken dinner. THANK YOU!

0 Karma

Esteemed Legend

I would still like to know why the direct comparison failed but since we know that the base search was working, we used a "base-search-like" test in the stats. If you have what you need, be sure to click Accept to close the question and upvote any other answers that were useful/educational to you.

0 Karma

Path Finder

Thanks for all your help. I wish I could "Accept" more than one answer. I went with the other one since I had more trouble creating a table based on your query. The subsearch suggestion above seemed to work better for what I want. However, I learned a lot about the stats command trying to get your suggestions to work. Thanks again.

0 Karma

Esteemed Legend

Subsearches have limits and are best to be avoided for that reason. Also, the reason that we were having problem has to be that "loginfail" is actually "LoginFail" or something else regarding capitalization. The one method is case-sensitive, but the searchmatch method, like the base search is case-insensitive for the value.

0 Karma

Esteemed Legend

See my (unaccepted) answer in the other question.

0 Karma

Path Finder

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.

0 Karma