Splunk Search

Events correlation: search events that occur after N given events

fabiob
Explorer

Hello guys,

I have an access log and I need to extract (detect) all those "successful login" events that have occurred after 4 "failed login" events. Of course these events must be user-related, i.e. it doesn't matter if I have 4 consecutive failed logins, as long as they refer to different users.

I created a simple log to test this:

08/07/2013, 12:17:30 - client=10.1.1.0, Series 1: should be detected
08/07/2013, 12:17:35 - client=10.1.1.1, action_feedback=FAIL
08/07/2013, 12:17:42 - client=10.1.1.1, action_feedback=FAIL
08/07/2013, 12:17:55 - client=10.1.1.1, action_feedback=FAIL
08/07/2013, 12:18:04 - client=10.1.1.1, action_feedback=FAIL
08/07/2013, 12:18:07 - client=10.1.1.1, action_feedback=OK
08/08/2013, 12:17:30 - client=10.1.1.0, Series 2: shouldn't be detected
08/08/2013, 12:17:35 - client=10.1.1.1, action_feedback=FAIL
08/08/2013, 12:17:42 - client=10.1.1.1, action_feedback=FAIL
08/08/2013, 12:17:55 - client=10.1.1.1, action_feedback=OK
08/08/2013, 12:18:04 - client=10.1.1.1, action_feedback=FAIL
08/08/2013, 12:18:07 - client=10.1.1.1, action_feedback=FAIL
08/08/2013, 12:18:15 - client=10.1.1.1, action_feedback=OK
08/09/2013, 12:17:30 - client=10.1.1.0, Series 3: should be detected for client .3, not for .1
08/09/2013, 12:17:35 - client=10.1.1.3, action_feedback=FAIL
08/09/2013, 12:17:42 - client=10.1.1.3, action_feedback=FAIL
08/09/2013, 12:17:50 - client=10.1.1.2, action_feedback=OK
08/09/2013, 12:17:57 - client=10.1.1.1, action_feedback=FAIL
08/09/2013, 12:17:58 - client=10.1.1.1, action_feedback=FAIL
08/09/2013, 12:17:55 - client=10.1.1.3, action_feedback=FAIL
08/09/2013, 12:17:58 - client=10.1.1.1, action_feedback=FAIL
08/09/2013, 12:17:59 - client=10.1.1.1, action_feedback=OK
08/09/2013, 12:18:04 - client=10.1.1.3, action_feedback=FAIL
08/09/2013, 12:18:07 - client=10.1.1.3, action_feedback=OK

(notice that logs with client 10.1.1.0 only separate series of events used as test cases, just for readability purposes).
What I'd like to get is:

08/07/2013, 12:18:07 - client=10.1.1.1, action_feedback=OK
08/09/2013, 12:18:07 - client=10.1.1.3, action_feedback=OK

Do you have any hint on how to do this? It seems that transaction is not the best here, as it returns different events merged into one (and I can't set a threshold for the minimum number of events). I racked my brain trying to come up with a solution using subsearches, but either it's not feasible in that way, or I haven't racked my brain enough. 🙂

Besides, if the feedback of an action was not expressed through a single field (so, for instance, a successful login was given by specific values of two or three fields and the existence of a string in the event), could the solution be easily adapted?

Thanks for your suggestions!

Tags (2)
0 Karma

gfuente
Motivator

Hello

I think you can get it this way:

index="yourindex" sourcetype="yoursourcetype" | transaction client endswith="action_feedback=OK" | search eventcount="5" | stats count by client

The meaning of this searh is, that you create transactions by client that ends with a succesful login, and the transaction has only 5 events, that means 1 OK at the end and 4 "NOT OK" events before the OK.

Try it and let me know if it works

Regards

gfuente
Motivator

Please, reverse the search string, like this:
"*OK*FAIL*FAIL*FAIL*FAIL*"
As they are in reverse time order in the transactions. Try it and let me know

Regards

0 Karma

gfuente
Motivator

You are welcome

Try with a less restrictive search, like "FAIL*FAIL" to see if that works, and remove the last commmand, the stats part, to keep it simple. When you get the desired subset of results then add the statistical command

Also keep the quotes surrounding the search to specify that is a string

Regards

0 Karma

fabiob
Explorer

Mmh, it seems that the second search is not working properly, I'm trying to understand why (it looks all good to me, but it returns "No results found").
Also, if I replace "stats count by client" with "table _time client", I don't get the timestamp of the successful login, but that of the first failed login in the series.

However, gfuente, thank you so much, I'm slowly getting into Splunk's logic through your answers! 😄

gfuente
Motivator

Ok, I see...

What about this:
index="yourindex" sourcetype="yoursourcetype" | transaction client endswith="action_feedback=OK" | search eventcount="5" | search "FAIL*FAIL*FAIL*FAIL*OK" | stats count by client

This include a search in the transactions that match the previous conditions, to look for 4 consecutive FAIL strings and then one OK string.

???

0 Karma

fabiob
Explorer

Hi gfuente, thanks for your answer, it actually worked!
However, I realized I have probably oversimplified my original situation (sorry, my bad!). If we assume there are other types of logged events we don't care about (say, action_feedback=Unknown or action_feedback=AlreadyLoggedIn), how could we count only the 5 action_feedback=FAIL preceding our action_feedback=OK?

Thanks again! 🙂

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...