Splunk Search

How to sort strings based off a dictionary of values?

fre
Engager

Hi & thanks in advance for reading,

I have a table as follows:

email                              event
----------------------------------------------
I-got-delivered@example.com     deferred    
I-got-delivered@example.com     delivered
I-got-delivered@example.com     processed
I-bounced@example.com             deferred  
I-bounced@example.com             processed 
I-bounced@example.com             bounced   
Im-processing@example.com         deferred
Im-processing@example.com         processed

where the events are ordered as follows:

{
    1: 'deferred',
    2: 'processed'
    3: 'bounced',
    4: 'delivered'
}

I want group by the email, compare the events and return only the max value for event (i.e. deferred < processed < bounced < delivered). The table should look like this:

I-got-delivered@example.com     delivered
I-bounced@example.com           bounced 
Im-processing@example.com       processed

I was thinking I could do it with lots of nested if statements, but I was wondering if there's a more elegant way to do it. How would you achieve this?

Thanks,
fre

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

your current search giving above table with field email, event
| replace "deferred" with 1 "processed" with 2 "bounced" with 3 "delivered" with 4 in event
| stats max(event) as event by email 
| replace "1" with "deferred" "2" with "processed" "3" with "bounced" "4" with "delivered" in event

View solution in original post

somesoni2
Revered Legend

Give this a try

your current search giving above table with field email, event
| replace "deferred" with 1 "processed" with 2 "bounced" with 3 "delivered" with 4 in event
| stats max(event) as event by email 
| replace "1" with "deferred" "2" with "processed" "3" with "bounced" "4" with "delivered" in event
Get Updates on the Splunk Community!

ATTENTION!! We’re MOVING (not really)

Hey, all! In an effort to keep this Slack workspace secure and also to make our new members' experience easy, ...

Splunk Admins: Build a Smarter Stack with These Must-See .conf25 Sessions

  Whether you're running a complex Splunk deployment or just getting your bearings as a new admin, .conf25 ...

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...