[SOLVED] Joining events into a single row

Joining events into a single row

I have some events that capture the times when different jobs start or end. Here are two events that capture the start and end times of a job-

[
  {
    "appName": "a1",
    "eventName": "START",
    "eventTime": "t1"
  },
  {
    "appName": "a1",
    "eventName": "END",
    "eventTime": "t2"
  },
  {
    "appName": "a1",
    "eventName": "START",
    "eventTime": "t3"
  },
  {
    "appName": "a2",
    "eventName": "START",
    "eventTime": "t4"
}
    
]

I am looking to visualize this information in a table showing the latest start and end times of each application, something like this -

--AppName--Last Start Time--Last End Time--
--a1--t3--t2--
--a2--t4--null--

The above table is assuming t3 comes after t1. How do i get to this ? I am able to extract the latest events for each into separate rows with this - stats latest(eventTime) by appName, eventName but need them to be combined into one single tuple.

Solution

Create separate fields for start and end times, then use stats to get the latest for each.

| eval start_time=if(eventName="START", eventTime, null())
| eval end_time=if(eventName="END", eventTime, null())
| stats latest(start_time) as last_start, latest(end_time) as last_end by appName

XXX Note that for the latest function to work properly, eventTime must be in epoch format. If it isn't, use the strptime function in a eval to convert it. XXX (Disregard this last part.)