topic Re: Sequential event mining in Splunk Search

Sequential event mining

fayedong — Sat, 25 May 2013 04:38:47 GMT

Hi everybody,

I am new to Splunk. I have a question about Splunk query.

Here are some sample logs (timestamp ordered) which record users' success attempts and failure attempts:

TimeStamp UserName Status
t7 UserA success
t6 UserA failure
t5 UserB success
t4 UserC failure
t3 UserC success
t2 UserD failure
t1 UserE success

My question is what should the query like if I want to find out users whose first attempt failed and then second attempt succeeded? .

Thanks.

Re: Sequential event mining

martin_mueller — Sat, 25 May 2013 18:27:35 GMT

For analysing relationships between events you often need streamstats. For example, appending this

streamstats count by UserName

will give you a unique numbering of events per user. After that you could filter for those where the first is a failure and the second is a success.

Re: Sequential event mining

fayedong — Sat, 25 May 2013 19:16:18 GMT

@Martin_mueller, thanks. Streamstats is cool. I don't know this command before but I managed to generate unique event using DEDUP. My actual question is actually what command should I use after having unique event:

How to list those where the first is a failure and the second is a success
and the number of unique users having this sequence?

Re: Sequential event mining

fayedong — Sat, 25 May 2013 19:17:58 GMT

Thanks, martin_mueller. My question actually is : how to filter for those where the first is a failure and the second is a success? 🙂

Re: Sequential event mining

martin_mueller — Sat, 25 May 2013 19:38:53 GMT

After counting them, you could filter like this:

where (Status="failure" AND count=1) OR (Status="success" AND count=2) | eventstats count by UserName | where count=2

That'll first filter for only those events where the first attempt is a failure and the second attempt is a success, then counts by username and only keeps those that have both attempts - ie filters out two failures or two successes.

I'm not sure whether that's what you need though - what goal are you trying to achieve?

Re: Sequential event mining

fayedong — Sat, 25 May 2013 20:41:01 GMT

stats first(Status) as Status1 last(Status) as Status2 by UserName | search Status1="Success" AND Status2="Failure"