Splunk Search

Sorting issue with numeric field, descending order

Communicator

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?

Tags (3)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

Communicator

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

0 Karma

Path Finder

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

0 Karma

Communicator

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!