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!

Your Guide to SPL2 at .conf24!

So, you’re headed to .conf24? You’re in for a good time. Las Vegas weather is just *chef’s kiss* beautiful in ...

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...