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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...