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