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!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...