topic Dedup Field with event times as column headers in Splunk Search

Dedup Field with event times as column headers

wwhite12 — Tue, 12 May 2020 03:32:15 GMT

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

Re: Dedup Field with event times as column headers

richgalloway — Tue, 12 May 2020 12:52:11 GMT

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

Re: Dedup Field with event times as column headers

wwhite12 — Wed, 30 Sep 2020 05:21:39 GMT

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?

Re: Dedup Field with event times as column headers

richgalloway — Tue, 12 May 2020 13:52:58 GMT

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

Re: Dedup Field with event times as column headers

wwhite12 — Tue, 12 May 2020 15:18:48 GMT

worked great thank you!