Including Data From a Previous Instance of a Given Event

[SplunkMonster]

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.

[yannK]

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]

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...