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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...