Splunk Search

Default Table sort order not working as expected

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

0 Karma

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

SplunkTrust
SplunkTrust

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

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

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

SplunkTrust
SplunkTrust

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

SplunkTrust
SplunkTrust

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

0 Karma

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

SplunkTrust
SplunkTrust

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

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

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.

SplunkTrust
SplunkTrust

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

0 Karma

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

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

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