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.

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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...