Splunk Search

Default Table sort order not working as expected?

simpkins1958
Contributor
 host="*" index=main sourcetype=WwanSignal uid="3F77F61645E8323E205F832212" | table _time deviceName user quality prevQuality prevDuration RSRP RSRQ RSSI SINR SQ lat lon

is returning in this sort order. Why is _time not sorting as expected, newest to oldest? Running 6.4.0. I though by default sort is base on _time field newest to oldest.

alt text

Labels (1)
0 Karma

stefan_dt
Loves-to-Learn

TLDR;  seems that stats list(x) as well as table commands return the events in the order they were indexed.

I may be flogging a dead horse here but I thought to add my two cents after investigating the same topic recently. It is still relevant and helps to be aware of this behavior. 

For my use case, I generated sample data and indexed it to a single Splunk Enterprise 9.0.1 instance. (So index clustering is not the culprit )

I noticed that the values/rows returned in my stats list(x) and table commands are ordered the same but not correctly by time. But when you do a search that just return the events, they are ordered correctly by _time as usual. 

I realized that the events returned from the commands are ordered the same as the events in the input file and therefore the order they were indexed in. 

This is of course a moot point when the data source is chronological making this one quite sneaky.

0 Karma

templets
Path Finder

This behavior is common when the search is made against clustered indexers. The results seem to be returned in chunks from each indexer so overall the events are not strictly in order by time.

For testing, adding "splunk_server" to the list of fields in the table command should show this.

0 Karma

guilmxm
Influencer

This is the way i do, creating a custom field in epochtime, sorting on this field and removing it:

| eval time=strftime('_time', "%s") | sort - time | fields - time

Or:

| eval time=strftime('_time', "%s") | sort time | fields - time

Sorting by an integer will always work as expected.

Note that sort will truncate to 10.000 output, if you want more:

sort limit=0
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

I tried both in search and on a dashboard, both work as expected for both sort orders.

If you see something different and sorting doesn't work as expected, do file a bug with support.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Are you sure? This works fine:

index=_internal | head 10 | table _time sourcetype | sort + _time

Use 1:n event sampling to get reasonable spaces in your timestamps if the last ten events all have the same.

0 Karma

guilmxm
Influencer

In a table within a view ? Or in search ?
Within a table in a view, sorting by the _time will not work as expected as far as i know

0 Karma

guilmxm
Influencer

I'm looking for an example to illustrate... maybe i'm wrong 🙂

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

This should not be necessary, underneath the UI formatting _time already is an epoch.subsecond value. Restricting this to %s would in fact reduce sortability by discarding the subsecond.

0 Karma

guilmxm
Influencer

It is necessary if the desired output is a table, and not event.
He can always create a field in epochtime with subsecond ("%s.%f") to keep the subsecond accuracy.
_time is in deed already epochtime, but it cannot be used to sort within a table in a view for example.

Sometimes you will want a view with a table (and not event), and some evaluations sorted by the original time or a custom time, therefore it is not appropriated to sort raw events that's right...

0 Karma

jkat54
SplunkTrust
SplunkTrust

Tables don't auto sort to my knowledge. Just add a |sort - _time and that should work. Or maybe you're looking for | sort _time You did mention descending time newest to oldest... So I think you want the positive sort not the negative sort.

The docs specifically say not to use table if you want the internal fields like _time.

http://docs.splunk.com/Documentation/Splunk/6.4.0/SearchReference/Table

 host="*" index=main sourcetype=WwanSignal uid="3F77F61645E8323E205F832212" | table _time deviceName user quality prevQuality prevDuration RSRP RSRQ RSSI SINR SQ lat lon | sort _time

martin_mueller
SplunkTrust
SplunkTrust

Pretty much. table in and of itself doesn't guarantee any sorting. It switches from events to results... events are sorted by Splunk in reverse time order , results can be anything.

jkat54
SplunkTrust
SplunkTrust

switches from events to results is the key then. Thanks Martin!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

The docs note about not using table for internal fields starting with an underscore doesn't mean you shouldn't use table to display _time.
Instead, you should not do this:

... | timechart count by sourcetype | table _time splunkd

This would drop the hidden timechart field _span that is needed by the charting engine to display the axis labels correctly.

0 Karma

jkat54
SplunkTrust
SplunkTrust

That makes more sense. Frankly I was surprised to see the new docs on table. I rarely if at all look at documents for the table command and I was pleasantly surprised to see the new format of docs etc. I read the cautionary statement wrong and thought it was new to 6.4 but I also see it in previous versions too.

So my final answer is the | sort _time

0 Karma

jkat54
SplunkTrust
SplunkTrust

Are my thoughts correct in that I beleive the op is getting out of order results due to an index cluster or multiple indexers returning results and the table command is just presenting them as they arrived at the search heads and in the order they were received?

0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Hi Splunky people! We are excited to share the newest updates in Splunk Enterprise 9.3!Admins and Analyst can ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...