Splunk Search

Including Data From a Previous Instance of a Given Event

SplunkMonster
Engager

I'm looking to create a report that lists out the occurrences of a given event, but also includes information about the previous instance of the event for a given user. Let's start with some sample data. Say this is all stored in myindex1 with the sourcetype mysourcetype1:

(Event #) Time Event Event_ID User MyValue1
1 1/7/14 10:53:24 Auth Success 141 mbojangles 1125
2 1/7/14 10:57:18 Session Started 76 mbojangles 454
3 1/7/14 11:05:41 Auth Success 141 jbaggadnts 4658
4 1/7/14 11:05:53 Logged Out 23 mbojangles 1701
5 1/7/14 11:07:26 Auth Success 141 mbojangles 74656


Let's say I'm making the report about Auth Success events. Say I iterate through a span of time in my index listing out all success events. So, my search would be something like:

index=myindex1 sourcetype=mysourcetype1 Event="Auth Success"
| table _time Event Event_ID User MyValue1

Which essentially returns the "Auth Success" events listed above in the sample data. However, for each of those lines/occurrences, I'd like to include two additional values-- one being the time that event last occurred prior to the given instance, and the other being the "MyValue1" field from that previous occurrence. So in this case, for the Auth Success event for mbojangles at 11:07 (event #5 in the first table), the line in my report would look like this:

(Event #) Time Event Event_ID User MyValue1 Prev_Event_time Prev_MyValue1
1 1/7/14 11:07:26 Auth Success 141 mbojangles 74656 1/7/14 10:53:24 1125

So not only am I showing the info for event #5 from the first table (since it was an instance of a successful login), I'm also going back and finding the last successful auth event for mbojangles, which happens to be event #1 in the first table, and adding that time in another column, along with the value of "MyValue1" for event #1, and combining them all on to the same line.


What is the best way for me to achieve this? I've seen a few hints/ideas in other questions, but I'm still not sure what the best approach is for this specific situation. I'd also like the search to be able to handle an instance where no previous information is available for a given event. So, for example, let's say I had event #1 from the first table on my report... there is no previous "Auth Success" event for mbojangles prior to that, so my last two columns would either show empty or "N/A" or something along those lines.

Tags (3)
0 Karma

yannK
Splunk Employee
Splunk Employee

streamstats will work. Otherwise solution are a stats command with first() and last(), or in the worse case a transaction command (more costly)

Solution with a streamstats
<my search>
| streamstats window=2 first(Event_Number) AS Event_Number first(_time) AS recent_time first(Event) AS Event first(MyValue1) AS MyValue1 last(_time) AS Prev_event_time last(MyValue1) AS Prev_MyValue1 by user
| convert ctime(recent_time)
| convert ctime(Prev_event_time)
| table user Event_Number recent_time MyValue1 Prev_event_time Prev_MyValue1 Event

explanation :
- will use all the events in the time range
- stats by user to differentiate them all
- current=true means to incluse the most recent
- window =2 means to get 2 events for user
- first and last will provide : first= most recent, last=oldest (it's base on the current time, so a bit counter-intuitive)
- conversion of any timestamp in epoch time to a readable format
- table to list the fields to display

martin_mueller
SplunkTrust
SplunkTrust

You can use streamstats to copy over fields from other events: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/streamstats

Maybe I'll find time to write a full response later...

.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!