Splunk Search

Group by id.

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

Motivator

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

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

Motivator

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

Contributor

This is beautiful, Thanks!

0 Karma