Splunk Search

Group by id.

sandeepmakkena
Contributor

I have a query like this

  index=MyIndex 
    | stats values(status) as status by id, time
    | dedup id,status

Gives me something like this
Status id time
apply 123 2019-10-28 10:04:02.707 EST
verify 123 2019-10-28 10:04:07.767 EST
approved 123 2019-10-28 10:04:10.707 PDT
login 123 2019-10-28 10:04:12.707 PDT
but I want
id latest(time) Status
123. 2019-10-28 10:04:12.707 PDT apply, verify, approved login

I did try

stats values(status), latest(time) by id but, the problem is with values(status) they're getting sorted alphabetically.

Thanks for your time.

0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

Using

stats list(status) as status latest(time) by id
| eval status=mvjoin(mvdedup(status),",")

list does not sort the items, but it also reports all occurrences of status, so if there is more than one of a single status, it will list all, hence the mvdedup will dedup the duplicates without changing the order.

View solution in original post

0 Karma

woodcock
Esteemed Legend

Try this:

index=MyIndex 
| stats max(_time) AS _time values(status) AS status BY id

Or perhaps this:

index=MyIndex 
| stats max(_time) AS _time BY status id
0 Karma

bowesmana
SplunkTrust
SplunkTrust

Using

stats list(status) as status latest(time) by id
| eval status=mvjoin(mvdedup(status),",")

list does not sort the items, but it also reports all occurrences of status, so if there is more than one of a single status, it will list all, hence the mvdedup will dedup the duplicates without changing the order.

0 Karma

sandeepmakkena
Contributor

This is beautiful, Thanks!

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...