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, Karma 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, Karma 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, Karma would be appreciated.
0 Karma

wwhite12
Path Finder

worked great thank you!

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

REGISTER NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If ...

Observability | Use Synthetic Monitoring for Website Metadata Verification

If you are on Splunk Observability Cloud, you may already have Synthetic Monitoringin your observability ...

More Ways To Control Your Costs With Archived Metrics | Register for Tech Talk

Tuesday, May 14, 2024  |  11AM PT / 2PM ET Register to Attend Join us for this Tech Talk and learn how to ...