topic Re: Sorting Question in Splunk Search

Sorting Question

g038123 — Wed, 30 Sep 2020 00:35:54 GMT

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.

Re: Sorting Question

skalliger — Sat, 18 May 2019 13:16:52 GMT

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

Re: Sorting Question

niketn — Wed, 30 Sep 2020 00:37:39 GMT

@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?

Re: Sorting Question

acharlieh — Sun, 19 May 2019 00:02:47 GMT

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.

Re: Sorting Question

g038123 — Mon, 20 May 2019 15:54:15 GMT

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