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
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!