Splunk Search
Highlighted

How to sort timechart results

Path Finder

I have timechart for maximum CPU usage. but I have to show 10 hosts having maximum CPU usage in Graph.
I have used sort command after timechart command but it didnt worked.Sort works perfectly for chart command.
How to sort results of timechart??
Please help me!

Thanks in advance!

Tags (1)
Highlighted

Re: How to sort timechart results

SplunkTrust
SplunkTrust

sort will sort rows, and when you're sorting chart max(CPU) over host, each host is a row.

In timechart max(CPU) by host however, if you look at the results in the main search UI, in table form, you'll see the host values are each columns, and so the sort command will thus have no effect on it.

The answer is a little clunky, and that's to use the fields command at the end to reorder the columns.

<your search> | timechart max(CPU) by host | fields host1 host2 host3 host4 host5

etc...

Someone else might think of some search language that can effectively do a generic columnsort operation, but I cant think of a way offhand.

Highlighted

Re: How to sort timechart results

Builder

Was there a progress in sorting in timechart (specifically in its columns)? I wish I could use it e.g. in a timechart with errors for sorting errors by their occurances in days.

0 Karma
Highlighted

Re: How to sort timechart results

Champion

I really needed to tackle the same kind of problem. I have tried a couple different ways. Both of the ones below seem to work, but the method you want to use probably depends on your Splunk setup and how efficient things need to be. Neither of the two methods below have been instrumented to a great degree to see which is the optimal solution.

Method 1: use 'appendpipe' to sort the aggregate values and filter the original events data based on a ranking of the top 10 aggregates.
The splunk query would look like this.

index=YOUR_PERFMON_INDEX sourcetype=YOUR_CPU_SOURCETYPE
| bin _time span=1h
| stats sum(CPU) as cpu by _time host
| eventstats sum(cpu) as agg_cpu by host
| appendpipe run_in_preview=f 
    [ fields - _time CPU 
    | dedup host sortby -agg_cpu
    | head 10 
    | fields host 
    | mvcombine host 
    | rename host as filter 
    | eval _time = 0]
 | sort + _time
 | filldown filter
 | WHERE like(filter, host)
 | fields - filter
 | timechart sum(cpu) by host

Method 2: use a subsearch in the initial search that calculates the top 10 aggregate Hosts and filters the first search to include just those. The splunk query would look like this.

index=YOUR_PERFMON_INDEX sourcetype=YOUR_CPU_SOURCETYPE
   [ search index=YOUR_PERFMON_INDEX sourcetype=YOUR_CPU_SOURCETYPE
   | stats sum(CPU) as cpu by host 
   | sort 10 desc cpu 
   | fields host]
| bin _time span=1h
| stats sum(CPU) as cpu by _time host
| timechart sum(cpu) by host
0 Karma