topic Re: Sorting timechart series in Splunk Search

Sorting timechart series

simumichaelm — Tue, 26 Oct 2010 02:20:29 GMT

We have a timechart that plots the number of entries of a specific type per day. The types are numerical (2, 3, 4...10, 11 at the moment).

Right now, doing a "timechart count by type" produces the type of chart we want, except that the first two series are 10 and 11 (so it is being ordered 10, 11, 2, 3, 4, 5, etc...)

How do I reorder the search so that the series emerge in numerical order instead of lexicographical?

Re: Sorting timechart series

David — Tue, 26 Oct 2010 05:26:52 GMT

Have you tried this route?: timechart count by type | sort num(type)

(From: http://www.splunk.com/base/Documentation/latest/SearchReference/Sort )

Re: Sorting timechart series

sideview — Tue, 18 Jan 2011 12:28:35 GMT

1) If the numbers really are quite small it's probably best to just use the fields command to reorder them.

<your search> | fields _time 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

2) or you could look into the transpose command. You could in theory transpose, sort and then transepose again. However transpose has some strange characteristics and you'd have to use a lot of eval contortions to end up with what you want....

3) if the numbers are NOT small, you dont want to have a fields command that lists every integer, and you dont mind bucketing the split-by field a little, you can use this:

<your search> | timechart count by date_second bins=10

Not many people know this, but if you put a bins argument after a split-by term, it actually buckets the split-by term.... So you end up with column values like "10-20", "20-30", "30-40", "40-50" etc... This may not be what you're looking for, but it has the nice side effect of sorting the buckets numerically.

Re: Sorting timechart series

vbumgarner — Thu, 21 Apr 2011 00:55:13 GMT

Or prefix type with zero.

... | eval type=if(type<10,"0"+type,type) | timechart ...

Re: Sorting timechart series

sideview — Thu, 21 Apr 2011 07:33:33 GMT

the sort command will sort the rows by a field called 'type', but in a split-by clause like this the type values are columns. So this question is really about sorting columns not rows.

Re: Sorting timechart series

jpreve — Tue, 06 Nov 2012 23:22:14 GMT

The way we found to do this was with a subsearch and return $field.

get your sorting criteria (we ran stats on a subset of the source data)
sort the results
use fields to pull only the field used for your series
mvcombine to turn all the records into a single one
then eval with mvjoin to make it into a comma or space-delimited string
output with return $<your list field> (the $ returns only the field's value)

Probably a more efficient way out there, but this seems to work.