Splunk Search

Count repeated failured logons following a successful logon

tmontney
Builder

Here's what I got so far:

index="myindex" (host="192.168.0.100" OR host="192.168.0.101") (msg="login OK" OR msg="login FAILED")
| transaction user maxspan=30s  endswith="login OK"
| eval FailedLogons=eventcount-1
| where msg="login FAILED" AND FailedLogons >= 3
| table _time user FailedLogons

For example, a user's account quickly fails to logon 3 times, then successfully logs on. I consider this strange activity and want to track it. This query gets me mostly there; however, assumes all events before "login OK" are failures (which may not always be the case). Transaction combines msg to where there's only one value of each; however, _raw contains the combined fields.

Is there a way to look for 3 consecutive failures that end with success?

0 Karma

DalJeanis
Legend

transaction is almost never the answer. This is a job for streamstats

 index="myindex" (host="192.168.0.100" OR host="192.168.0.101") (msg="login OK" OR msg="login FAILED")

 | rename COMMENT as "mark logons and then number them, remembering their time"
 | eval OK_flag=case(msg="login OK",1) 
 | streamstats sum(OK_flag) as OK_group last(eval(case(OK_flag=1,_time))) as next_logon by user

 | rename COMMENT as "for each group, count the number of fails"
 | eval fail_flag=case(msg="login FAILED",1)
 | eval fail_time=case(msg="login FAILED",_time)
 | eventstats max(next_logon) as next_logon sum(fail_flag) as fail_count by user OK_group

 | rename COMMENT as "drop all groups with less than 3 fails"
 | where fail_count >=3 

 | rename COMMENT as "you can stop here, or analyze a little more, since we haven't limited the time of fails to 30 sec"
 | where (next_logon < _time+30)

 | rename COMMENT as "roll up each group"
 | stats sum(fail_flag) as fail_count min(fail_time) as first_fail_time max(fail_time) as last_fail_time values(_time) as _time max(next_logon) as logon_time by user OK_group 

 | convert ctime(*_time)

The above is aircode, but it should be pretty close .

tmontney
Builder

Why is transaction "almost never the answer"?

0 Karma

DalJeanis
Legend

@tmontney -
transaction is highly expensive, and almost every use case the same search can be done better with less resources by proper use of stats, streamstats and/or eventstats.

Plus, if you engineer your search to use exactly those, then you know exactly what it is doing.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The Splunk Security Essentials app has a example query that does something similar. Look for the "Brute Force Login Attempts" use case.

---
If this reply helps you, Karma would be appreciated.

tmontney
Builder

I've always been meaning to look at this, good idea.

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

Tech Talk Recap | Mastering Threat Hunting

Mastering Threat HuntingDive into the world of threat hunting, exploring the key differences between ...

Observability for AI Applications: Troubleshooting Latency

If you’re working with proprietary company data, you’re probably going to have a locally hosted LLM or many ...

Splunk AI Assistant for SPL vs. ChatGPT: Which One is Better?

In the age of AI, every tool promises to make our lives easier. From summarizing content to writing code, ...