topic Re: How can I detect a successful login after multiple failed logins? in Splunk Search

How to detect a successful login after multiple failed logins?

janedoe887 — Tue, 22 Feb 2022 17:15:10 GMT

Hello, fellow splunkers!

What I am trying to do is to detect a successful login after multiple failed attempts. I've been trying to get a working search for Windows and Linux but wasn't very successful.

For Linux I found a search posted as answer to a similar question from 2011. I adapted it and at least it does something - but not quite what I want.

"failed password" OR "accepted password"| stats  list(action) as list by host, user  | eval  list = mvjoin(list, " ")  | eval  alert = if(match(list, " (?:failure\s?){3,}"), "True", "False")

It is supposed to write True in the last column, if there are 3 or more failed logins followed by a successful login. However, no matter what I try, the list it tries to match always contains two failures followed by one success, so that the matching condition is never met. (see screenshot for clarification)

For Windows, I tried writing my own search by using transactions.

(eventtype="win_logon_failed" OR eventtype="win_logon_success") AND (NOT user=HealthMailbox* NOT user=*$*)|stats count by Account_Name, src| where count > 5 | transaction Account_Name startswith=eval(EventCode=4625) endswith=eval(EventCode=4624)

The problem I am facing in this case, is that I can't seem to tell Splunk that there need to be a total of at least 6 Events (5 failed + 1 successful logins).

Can anyone please tell me how to write such queries correctly?

Thanks in advance!

Re: How can I detect a successful login after multiple failed logins?

javiergn — Tue, 16 Feb 2016 15:01:58 GMT

This is what I would do.

Linux (minor change to make sure there's a success taking place after your 3 or more failures):

index=foo sourcetype=bar "failed password" OR "accepted password"
| stats  list(action) as list by host, user  
| eval  list = mvjoin(list, " ")  
| eval  alert = if(match(list, "(?:failure\s?){3,}(?:success)"), "True", "False")

Windows (same logic as above but using the eventtype field):

 (eventtype="win_logon_failed" OR eventtype="win_logon_success") AND (NOT user=HealthMailbox* NOT user=*$*)
| stats list(eventtype) as list by Account_Name, host
| eval  list = mvjoin(list, " ")  
| eval  alert = if(match(list, "(?:win_logon_failed\s?){3,}(?:win_logon_success)"), "True", "False")

Both will alert if there are 3 or more consecutive login failures and then a successful one for any given user name and host.
Feel free to tweak it to match your requirements.

Hope that helps.

Re: How can I detect a successful login after multiple failed logins?

janedoe887 — Tue, 29 Sep 2020 08:45:54 GMT

Thanks a lot for your answering so quickly!

Unfortunately, the searches dont't work quite as expected:

The linux one still does the same weird thing: I tried logging on with wrong credentials three times, the 4th time I used proper credentials and logged on. The resulting list contains: success failure failure success when it should be failure, failure, failure, success. Therefore, the last column stays at False no matter what.

The windows one is even worse: the list contains so many eventtypes that there is no way that win_logon_failed is followed by win_logon_success...

One entry looks like this:
msad-nt6-successful-user-logons msad-successful-user-logons windows_authentication_events windows_logon_success wineventlog_security wineventlog_windows winsec win_logon_combined win_logon_success msad-nt6-successful-user-logons msad-successful-user-logons windows_authentication_events windows_logon_success wineventlog_security wineventlog_windows winsec win_logon_combined win_logon_success msad-nt6-successful-user-logons msad-successful-user-logons windows_authentication_events windows_logon_success wineventlog_security wineventlog_windows winsec win_logon_combined win_logon_success msad-failed-user-logons msad-nt6-failed-user-logons nix_errors windows_authentication_events windows_logon_failure wineventlog_security wineventlog_windows winsec win_logon_combined win_logon_failed msad-failed-user-logons msad-nt6-failed-user-logons nix_errors windows_authentication_events windows_logon_failure wineventlog_security wineventlog_windows winsec win_logon_combined win_logon_failed msad-failed-user-logons msad-nt6-failed-user-logons nix_errors windows_authentication_events windows_logon_failure wineventlog_security wineventlog_windows winsec win_logon_combined win_logon_failed

I am really at a loss...

Re: How can I detect a successful login after multiple failed logins?

woodcock — Tue, 16 Feb 2016 16:10:57 GMT

Try this (for windows; you will have to adjust for *NIX):

EventCode=4768 OR EventCode=4771 OR EventCode=4776

| eval TargetDomain=upper(coalesce(Supplied_Realm_Name, Service_Name, "-"))
| eval TargetObject=upper(coalesce(Account_Name, Logon_Account, "-"))
| rex mode=sed field=TargetDomain "s/\..*//"
| rex mode=sed field=TargetDomain "s%KRBTGT/%%"
| eval CombinedTarget = TargetDomain . "\\" . TargetObject

| eval LoginAttemptResult=if((action="failure" OR Keywords="Audit Failure" OR EventCode=4771), "FAILED", "SUCCESSFUL")
| streamstats count(eval(LoginAttemptResult="SUCCESSFUL")) AS LoginSessionID BY CombinedTarget
| eventstats latest(_time) AS mostRecentTime latest(LoginAttemptResult) AS mostRecentResult count(eval(LoginAttemptResult="FAILED")) AS NumFailuresBeforeSuccessOrAbandon by LoginSessionID CombinedTarget

| where mostRecentResult="SUCCESSFUL"

Re: How can I detect a successful login after multiple failed logins?

javiergn — Wed, 17 Feb 2016 11:08:46 GMT

Hi,

Sorry but I don't really have a lab to test your queries so for the Windows one I would use the one @woodcock answered below. The error you are getting about the rex command is probably here:

| rex mode=sed field=TargetDomain "s%KRBTGT/%%"

As it should be:

| rex mode=sed field=TargetDomain "s/%KRBTGT/%%"

With regards to the linux query, try visualising your results in a table first and see if that makes any sense:

index=foo sourcetype=bar "failed password" OR "accepted password"
| table _time, host, user, action
| sort -limit=0 host, user, _time

There's no transformation taking place there so if you are generating 3 or 4 failures and then one success, you should be able to see it. Feel free to post the output here if you are not sure what's going on.

Thanks,
J

Re: How can I detect a successful login after multiple failed logins?

woodcock — Fri, 19 Feb 2016 23:31:27 GMT

You are incorrect about any mistake being in that command . The sed command can use other characters than / for the separator, including %. I double-checked and there is nothing at all wrong with that line of my solution.

Re: How can I detect a successful login after multiple failed logins?

woodcock — Fri, 19 Feb 2016 23:36:35 GMT

There was another tiny typo (cont instead of count) that I have now fixed by re-editing. I stand completely by the above answer for Windows.

Re: How can I detect a successful login after multiple failed logins?

janedoe887 — Tue, 29 Sep 2020 08:51:06 GMT

Talking about tiny typos: there is another one: count(eval(LoginAttemptResult="SUCCESFUL")) --> SUCCES*S*FUL

Also, could you please explain how this search works or what exactly it is looking for? I thought, EventCode=4624 marks a successful login and EventCode=4625 is a failed login. Your search, however, looks for 4771 and 4776 which are some Kerberos ticket events if I am not mistaken.
How do you check for multiple failed logins followed by a successful one?

Basically, the search works now - as in, it returns "something". I am not sure, if the results are what I am looking for because they contain both failed and successful logins which is the same as if I am searching for EventCode=4624 OR EventCode=4625. I am actually looking for a search that returns 1 Event if there were multipe failed logins followed by one successful login.
Could you please explain what the search results mean? What is the difference to just searching for the mentioned EventCodes?

I am sorry for asking so many questions but I would really like to understand the search in order to learn something new instead of just copying and pasting it.

Thanks for your help.

Re: How can I detect a successful login after multiple failed logins?

janedoe887 — Mon, 22 Feb 2016 14:13:55 GMT

Thanks for your input javiergn!

The Linux query results something like:

2016-02-16 14:01:30 splunk02 testuser failure
2016-02-16 14:02:00 splunk02 testuser failure
2016-02-16 14:02:05 splunk02 testuser success

How would I manage to write a query that returns a single event if multiple failed logins (from one user on one machine) are followed by a successful one?

Re: How can I detect a successful login after multiple failed logins?

woodcock — Mon, 22 Feb 2016 18:45:54 GMT

Good catch on the typo; I went back and fixed it (re-edited).

You can modify the events included (the very first search portion) and also the assignment of the values for LoginAttemptResult to make sure that each event either has FAILED or SUCCESSFUL and then it should work for your particular events. In my case, I was given the subset of events to qualify and did not question them (given to me by Windows Event SME) but perhaps I should have. Please do update this with a comment when you figure out a final solution that works for you.

Re: How can I detect a successful login after multiple failed logins?

lakshman239 — Wed, 31 Aug 2016 22:24:09 GMT

not sure if you have sorted this ... on the unix one, how about the below: (assuming success comes within 90s)

your search | stats list(action) AS Attempts, count(eval(match(action,"failed password"))) AS Failed, count(eval(match(action,"accepted password"))) AS Success by user,host,_time | transaction host user maxspan=90s | where mvcount(Attempts)>=2 AND mvcount(Success)>=1 AND mvcount(Failed)>=1 | table _time, host, user

you can remove | after 90s and check the events an change where clause accordingly.

Re: How can I detect a successful login after multiple failed logins?

claudio_manig — Thu, 30 Nov 2017 14:09:23 GMT

I might be wrong but in your query you either have to change the regex or ad a "| reverse" at the beginning of the query as the list command lists from youngest to oldest value resulting the rex in your sample to fail as the youngest result is a "success" no?

Re: How can I detect a successful login after multiple failed logins?

christoffertoft — Tue, 16 Jan 2018 13:27:32 GMT

Thanks alot for this. Had to redo some stuff to fit my use case but nice writeup nonetheless.

Re: How can I detect a successful login after multiple failed logins?

christoffertoft — Tue, 16 Jan 2018 13:46:02 GMT

@woodcock what is the purpose of mostRecentTime?

Re: How can I detect a successful login after multiple failed logins?

renjujacob88 — Tue, 29 Sep 2020 18:13:42 GMT

Hi folks,

Please find the updated query . This query will only alert if you have authentications like Login_Failure multiple times followed by Login activity

index=citrix citrix_netscaler_event_name=LOGIN_FAILED OR citrix_netscaler_event_name=LOGIN |eval Citrix_Browser=coalesce(Browser,Browser_type)| stats list(citrix_netscaler_event_name) as action list(Citrix_Browser) as Citrix_Browser by _time Citrix_User | sort - _time | stats list(_time) as time list(action) as action dc(action) as action_value list(Citrix_Browser) as Citrix_Browser by Citrix_User | convert ctime(time) | eval Action_Done = mvjoin(action," ") |eval Alert = if(match(Action_Done, "(?:LOGIN_FAILED\s?){2,}(?:LOGIN)"), "True", "False") | sort - _time | where Alert="True" and action_value > 1 | fields - action_value

Re: How can I detect a successful login after multiple failed logins?

suryaavinash — Wed, 09 May 2018 07:52:10 GMT

@woodcock Thank you so so much . been fighting for this with Autoregress and other stuff . my requirement was to find 6th successfull login after 5 failed logins.

I can use this just that i need to add a few other event codes 4625 for failed login and 4624 for success login

Thank you so much.

Re: How can I detect a successful login after multiple failed logins?

woodcock — Wed, 09 May 2018 14:08:33 GMT

Context. After that evenstats call, any/every event has the details of the matching "latest" event. This allows many sorts of final logic (not necessarily needed for this one).

Re: How can I detect a successful login after multiple failed logins?

pongc — Wed, 30 Sep 2020 12:58:09 GMT

Putting these links here as they helped me greatly in creating a search to meet the question raised by OP.

https://conf.splunk.com/files/2017/recordings/advanced-splunk-searching-for-security-hunting-and-alerting.mp4

Accompanying slides

https://conf.splunk.com/files/2017/slides/advanced-splunk-searching-for-security-hunting-and-alerting.pdf

Note that you just have to reverse the success / failure logic since the video used multiple successes followed by failure as an example.

Re: How can I detect a successful login after multiple failed logins?

dilumajeed — Tue, 22 Feb 2022 16:38:24 GMT

use maxevents command