Default Table sort order not working as expected?

[simpkins1958]

 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.

[stefan_dt]

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.

[templets]

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.

[guilmxm]

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

[martin_mueller]

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.

[martin_mueller]

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.

[guilmxm]

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

[guilmxm]

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

[martin_mueller]

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.

[guilmxm]

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

[jkat54]

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]

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]

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

[martin_mueller]

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.

[jkat54]

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

[jkat54]

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?