Splunk Search

Dedup Field with event times as column headers

wwhite12
Path Finder

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
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

wwhite12
Path Finder

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?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma

wwhite12
Path Finder

worked great thank you!

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!