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