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!
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
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
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
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! 😄
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.
???
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! 🙂