Splunk Search

Sorting Question

g038123
Explorer

Hello, hoping someone can give me a hand or point me in the right direction.
I have a report that is based off of a DB query, I'm trying to sort a date field descending. Due to the nature of the data and the number of records I need to display, I end up with duplicate date records. This comes into play later on.........

In order to correctly sort the date field, I am converting the field to epoch time sorting it desc and then converting back to human readable ( "y/m/d")
When I run the query everything works as expected, see below for search details to this point:

|"Base SQL query"
| eval TRIGGER_DATE = strptime(TRIGGER_DATE, "%Y-%m-%d %H:%M:%S.%3N")
| sort 0 -num(TRIGGER_DATE)
| eval TRIGGER_DATE =strftime(TRIGGER_DATE,"%Y/%m/%d")

Here's where things go wrong.
Next I am piping the results to a stats command. If I use list(Date_field) everything is fine, dates are sorted correctly but I have multiple identical date fields.
If I use values(date_field), the duplicates are handled but the dates are now sorted ascending.

|"Base SQL query"
| eval TRIGGER_DATE = strptime(TRIGGER_DATE, "%Y-%m-%d %H:%M:%S.%3N")
| sort 0 -num(TRIGGER_DATE)
| eval TRIGGER_DATE =strftime(TRIGGER_DATE,"%Y/%m/%d")
| stats values(EXT_ACCT), values(date_field)......etc

I have tried everything I can think of but cannot get the date fields sorted descending after the stats command when using values.

Any help would be greatly appreciated.

0 Karma
1 Solution

acharlieh
Influencer

So you have the results sorted in the order you want them, but you want to eliminate duplicates after you pull them together into a multi-valued field using stats? So for a run-anywhere example, we have a bunch of results with dates:

| makeresults count=20 
| eval random=random()%3-1,adj=case(random=0,"@d",random=1,"+d@d",random=-1,"-d@d"),date=strftime(relative_time(_time,adj),"%Y-%m-%d") 
| table date 

As you found, if you sort the dates in the order that you want them going into the stats command, you can use list to then collect the list of dates. Then after the stats command you can use the mvdedup function to eliminate the duplicate entries.

...
| sort 0 - date | stats list(date) as list
| eval list=mvdedup(list)

I would take the opportunity to log an enhancement request with Splunk... it would be nice if there was an option to just reverse the output of values, (saving you from the pre-sort) but I'm not seeing an option that would do such easily.

View solution in original post

0 Karma

acharlieh
Influencer

So you have the results sorted in the order you want them, but you want to eliminate duplicates after you pull them together into a multi-valued field using stats? So for a run-anywhere example, we have a bunch of results with dates:

| makeresults count=20 
| eval random=random()%3-1,adj=case(random=0,"@d",random=1,"+d@d",random=-1,"-d@d"),date=strftime(relative_time(_time,adj),"%Y-%m-%d") 
| table date 

As you found, if you sort the dates in the order that you want them going into the stats command, you can use list to then collect the list of dates. Then after the stats command you can use the mvdedup function to eliminate the duplicate entries.

...
| sort 0 - date | stats list(date) as list
| eval list=mvdedup(list)

I would take the opportunity to log an enhancement request with Splunk... it would be nice if there was an option to just reverse the output of values, (saving you from the pre-sort) but I'm not seeing an option that would do such easily.

0 Karma

g038123
Explorer

Thank you acharlieh!
The mvdedup works perfectly! Just what I needed.

0 Karma

niketn
Legend

@g038123 what is that you want to output as result? Also do you have three date fields TRIGGER_DATE, EXT_ACCT and something else? Are you performing stats by some field?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

skalliger
SplunkTrust
SplunkTrust

When you do a | stats values(field) AS fieldnew you can also simply do a | sort 0 -fieldnew at the end which should be the easiest solution.
Edit: Oh, and stats() also has two functions called first() and last().

Skalli

0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...