topic Re: Sorting issue with numeric field, descending order in Splunk Search

Sorting issue with numeric field, descending order

pgoldweic — Wed, 29 Apr 2020 17:05:59 GMT

I have a simple search with a sort command at the end as follows:
.... some base search | dedup id | table id, name | sort -id

and I'm being presented with the following results:

18760000000000166   2020 Summer
18760000000000168   2020 Fall
18760000000000167   2020-2021 Academic Year
18760000000000164   2020 Winter
18760000000000165   2020 Spring
18760000000000163   2019 Fall
18760000000000131   2019-2020 Academic Year
18760000000000127   2019 Spring
18760000000000129   2019-2020 Academic Year
18760000000000130   2018-2019 Academic Year

I expected the results to have been ordered by id (descending order). What am I missing?

Re: Sorting issue with numeric field, descending order

dsctm3 — Wed, 29 Apr 2020 17:19:31 GMT

Try forcing splunk to treat the ID field as a number.

| sort -num(id)

https://docs.splunk.com/Documentation/Splunk/8.0.3/SearchReference/Sort

Re: Sorting issue with numeric field, descending order

pgoldweic — Wed, 29 Apr 2020 17:52:10 GMT

Thanks for your reply. I just tried your suggestion but it's not working (gives me identical results!).

Re: Sorting issue with numeric field, descending order

to4kawa — Wed, 29 Apr 2020 18:41:20 GMT

Solution:

| makeresults
| eval _raw="id,name
18760000000000166,2020 Summer
18760000000000168,2020 Fall
18760000000000167,2020-2021 Academic Year
18760000000000164,2020 Winter
18760000000000165,2020 Spring
18760000000000163,2019 Fall
18760000000000131,2019-2020 Academic Year
18760000000000127,2019 Spring
18760000000000129,2019-2020 Academic Year
18760000000000130,2018-2019 Academic Year"
| multikv forceheader=1
| table id name
| rex field=id "(?<Upper>\d{10})(?<Lower>\d{7})"
| sort - Upper Lower
| fields - Upper Lower

that's interesting.

sample:

| makeresults
| eval _raw="id,name
18760000000000166,2020 Summer
18760000000000168,2020 Fall
18760000000000167,2020-2021 Academic Year
18760000000000164,2020 Winter
18760000000000165,2020 Spring
18760000000000163,2019 Fall
18760000000000131,2019-2020 Academic Year
18760000000000127,2019 Spring
18760000000000129,2019-2020 Academic Year
18760000000000130,2018-2019 Academic Year"
| multikv forceheader=1
| table id name
| eval check = tonumber(id)

id is huge number. so Splunk has rounded off the number.

Re: Sorting issue with numeric field, descending order

pgoldweic — Wed, 29 Apr 2020 18:44:47 GMT

Oh, I see. It didn't occur to me that Splunk could be rounding out, but it now makes sense. Thanks!