Dedup Field with event times as column headers

[wwhite12]

I have json data that comes in tracking ID's. An event is created when an ID is "created" and an event is created when an ID is "closed". Each event has the same alert ID and I'm struggling with a query that dedups the alert ID, and puts the times the ID's were created and closed in the columns. Looking for a table something like this: the times can be when the events were created or there is also a json field that's extracted as "date" Thanks in advance.

ID|  Created_Time | Closed_Time
  |
x | xxxxxxxxxxxx  | xxxxxxxx

[richgalloway · Accepted solution]

The stats command can do that.

... | stats earliest(Created_Time) as Created_Time, latest(Closed_Time) as Closed_Time by ID
| table ID, Created_Time, Closed_Time

[wwhite12]

What if Created_Time and Closed_Time are not pre-defined fields, is there a way I can pick the timestamp of an event if it contains "Created ID" and assign that to Created_Time and the same thing for Closed_Time?

[richgalloway]

Easy. Set fields to _time when the target strings are detected.

... | eval Created_Time = if(searchmatch("Created ID"), _time, null)
| eval Closed_Time = if(searchmatch("Closed ID"), _time, null)
| stats earliest(Created_Time) as Created_Time, latest(Closed_Time) as Closed_Time by ID
| table ID, Created_Time, Closed_Time

[wwhite12]

worked great thank you!