topic Re: Default Table sort order not working as expected in Splunk Search

Default Table sort order not working as expected?

simpkins1958 — Tue, 04 Oct 2022 13:32:57 GMT

 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.

Re: Default Table sort order not working as expected

jkat54 — Fri, 22 Apr 2016 23:40:35 GMT

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

Re: Default Table sort order not working as expected

guilmxm — Sat, 23 Apr 2016 00:23:36 GMT

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

Re: Default Table sort order not working as expected

martin_mueller — Sat, 23 Apr 2016 13:35:21 GMT

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.

Re: Default Table sort order not working as expected

martin_mueller — Sat, 23 Apr 2016 13:38:49 GMT

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.

Re: Default Table sort order not working as expected

jkat54 — Sat, 23 Apr 2016 14:10:03 GMT

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

Re: Default Table sort order not working as expected

jkat54 — Sat, 23 Apr 2016 14:17:25 GMT

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?

Re: Default Table sort order not working as expected

martin_mueller — Sat, 23 Apr 2016 14:20:28 GMT

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.

Re: Default Table sort order not working as expected

jkat54 — Sat, 23 Apr 2016 14:41:26 GMT

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

Re: Default Table sort order not working as expected

guilmxm — Sat, 23 Apr 2016 14:51:07 GMT

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...

Re: Default Table sort order not working as expected

martin_mueller — Sat, 23 Apr 2016 15:04:02 GMT

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.

Re: Default Table sort order not working as expected

guilmxm — Sat, 23 Apr 2016 15:34:56 GMT

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

Re: Default Table sort order not working as expected

guilmxm — Sat, 23 Apr 2016 15:58:15 GMT

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

Re: Default Table sort order not working as expected

martin_mueller — Sat, 23 Apr 2016 16:42:07 GMT

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.

Re: Default Table sort order not working as expected

templets — Thu, 01 Jun 2017 23:54:13 GMT

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.

Re: Default Table sort order not working as expected

stefan_dt — Tue, 04 Oct 2022 12:45:37 GMT

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.