Sorting timechart fields by field value

[peiffer]

I would like to visualize using the Single Value visualization with and Trellis Layout and sort panels by the value of the latest field in the BY clause.  I can follow the timechart with a table and order the rows manually, but I would like something more automatic.

Is there a way of specifying a field projection order via some sort of sort that can be used with timechart. I can't seem to find anything and may need to rely upon something that is an outside the box.

Please advise,
Tim

Here is my SPL and the resulting visualization below

| mstats latest(_value) as value
         WHERE index="my_metrics" 
         AND metric_name="my.app.metric.count"
         BY group
         span=15m
| timechart span=15m usenull=false useother=false partial=false
         sum(value) AS count
         BY group
         WHERE max in top6

[bowesmana]

There are a couple of ways (at least) to do this

Here is an example dashboard - that shows three techniques.

1. Uses a token in a base search to define the sort order as a token ($sort_order$) - there is an annoying issue with this method, which means that once the trellis is shown with the order, it will NOT reorder the trellis if the underlying table order changes.

2. Uses 6 separate single panels aligned horizontally and six tokens that define the display for the viz. ($f0$ to $f5$). This re-orders on change

3. Uses @chrisyounger number set viz - https://splunkbase.splunk.com/app/4537 which will do all this for you and does not require tokens and will re-order when things change

There is possibly a search way directly to do it (appendpipe...?), but with the dashboard, it's pretty simple

<dashboard version="1.1" theme="light">
  <label>Sort_TC</label>
  <row>
    <panel>
      <table depends="$hidden$">
        <search id="base">
          <query>
| makeresults count=3600 
| streamstats c 
| eval _time=relative_time(now(), "@m") - c 
| eval type=mvindex(split("Budgeting,General Ledger,Payables,Expenses,eProcurement,Purchasing",","), random() % 6) 
| timechart fixedrange=f span=1m count by type
          </query>
          <earliest>-60m@m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
      <table depends="hidden$">
        <title>Sorting order is $sort_order$</title>
        <search base="base">
          <done>
            <set token="sort_order">$result.sort$</set>
            <set token="f0">$result.f0$</set>
            <set token="f1">$result.f1$</set>
            <set token="f2">$result.f2$</set>
            <set token="f3">$result.f3$</set>
            <set token="f4">$result.f4$</set>
            <set token="f5">$result.f5$</set>
          </done>
          <query>| tail 1
| fields - _span _time
| transpose 0
| sort - "row 1"
| stats list(column) as sort list("row 1") as counts
| foreach 0 1 2 3 4 5 [ eval f&lt;&lt;FIELD&gt;&gt;=mvindex(sort, &lt;&lt;FIELD&gt;&gt;) ]
| eval sort="\"".mvjoin(sort, "\" \"")."\""</query>
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
      <single>
        <search base="base">
          <query>| table _time $sort_order$</query>
        </search>
        <option name="colorMode">block</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">1</option>
        <option name="useColors">1</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <single>
        <title>$f0$</title>
        <search base="base">
          <query>
| table _time $f0|s$            
          </query>
        </search>
        <option name="colorMode">block</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="useColors">1</option>
      </single>
      <single>
        <title>$f1$</title>
        <search base="base">
          <query>
| table _time $f1|s$            
          </query>
        </search>
        <option name="colorMode">block</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="useColors">1</option>
      </single>
      <single>
        <title>$f2$</title>
        <search base="base">
          <query>
| table _time $f2|s$           
          </query>
        </search>
        <option name="colorMode">block</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="useColors">1</option>
      </single>
      <single>
        <title>$f3$</title>
        <search base="base">
          <query>
| table _time $f3|s$            
          </query>
        </search>
        <option name="colorMode">block</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="useColors">1</option>
      </single>
      <single>
        <title>$f4$</title>
        <search base="base">
          <query>
| table _time $f4|s$            
          </query>
        </search>
        <option name="colorMode">block</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="useColors">1</option>
      </single>
      <single>
        <title>$f5$</title>
        <search base="base">
          <query>
| table _time $f5|s$            
          </query>
        </search>
        <option name="colorMode">block</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="useColors">1</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <viz type="number_set_viz.number_set_viz">
        <search base="base">
          <query>
| stats sparkline(max(*)) as sparkline_* latest(*) as * 
| appendpipe [
  | foreach sparkline_* [ eval &lt;&lt;MATCHSTR&gt;&gt;='&lt;&lt;FIELD&gt;&gt;']
]
| fields - sparkline_*
| transpose 0
| rename "row 1" as value, column as title "row 2"  as sparkline
| sort - value
          </query>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </viz>
    </panel>
  </row>
</dashboard>

[bowesmana · Accepted solution]

There is also two pure search versions which are very ugly

Assuming this is a base search that creates the timechart

| makeresults count=3600 
| streamstats c 
| eval _time=relative_time(now(), "@m") - c 
| eval type=mvindex(split("Budgeting,General Ledger,Payables,Expenses,eProcurement,Purchasing",","), random() % 6) 
| timechart fixedrange=f span=1m count by type 

then this version does a double transpose - the good thing about the double transpose is that it does not change the column order on the second transpose - however it does require that you know the sort column number - although you could work that out in a separate dashboard search

| transpose 0
| sort - "row 60"
| transpose 0 header_field=column
| fields - column

this version uses a mechanism to get the column names, sort them and then prefix the column with a numeric which will cause the columns to be ordered correctly, however, you can't rename them back as Splunk will then reorder alphabetically.

| appendpipe [
  | stats latest(*) as *
  | transpose 0
  | sort - "row 1"
  | streamstats c
  | eval name="_".c."_".column
  | fields name
]
| eventstats values(name) as _name
| fields - name
| foreach * [ eval f=mvindex(_name, mvfind(_name, "<<FIELD>>")), f=replace(f, "^_", ""), {f}='<<FIELD>>' | fields - "<<FIELD>>" ]
| fields - f
| where isnotnull(_time)

[peiffer]

I would rather have a straight search SPL solution, to drive the Trellis layout

The proposed solutions I have seen have the effect of sorting the resulting | timechart fields by name rather than by value.  The goal is sort by field value. 

I appreciate the attempt so far.

[bowesmana]

These all sort by field value of the last time value of the timechart, they are not sorting by name - what makes you think that?

[peiffer]

I re-implemented your solutions and found #2 sorted by name.

Your solution #3 does indeed sort by value.  There is a limitation of 9 or less fields/columns due lexical sorting, and the fields now have additional ##_ prepended.  The limitation of 9 or less is significant if you watch a couple of dozen items and rank them.

I will accept the answer.  I am thinking there is a simpler subsearch to drive the | table projection of the columns and I will continue to look in that direction.  For now, I will probably save as a macro.

Thank you.

[bowesmana]

Number 2 does not do sorting in the table itself, that is simply used as the base search in the dashboard to drive the sorting of the visualisation panels, which is what I understood you wanted to do. 

There is no practical column limit to the prefix solution, you just need to make the prefix fit the requirement, i.e. change the | eval name=... to

| eval name=printf("_%02d_%s", c, column)

and you will have a sortable 01_xxx 02_yyy syntax.

As for a subsearch, the problem you face is that generally a subsearch runs BEFORE the primary search, so the subsearch cannot generate the structure for the table command as the timechart has not yet run.

The exception to that is the appendpipe subsearch, which runs inline with the primary search, which I gave as an example, however, this subsearch is different in that it is creating new rows so it can't be used to push data into the commands in the existing pipeline.

I did figure out how to do the double transpose without knowing the column count

| transpose 0
| sort - [ 
  | makeresults earliest=-60m@m latest=@m
  | timechart fixedrange=t count
  | stats count as row
  | eval search="row ".row
  | format "" "" "" "" "" ""
  ]
| transpose 0 header_field=column
| fields - column

the earliest/latest may not be needed in the real world, as long as the timechart and time range matches the outer search, it will get the same row count, so the sort will work with correct column name.

If you do find another way, please post here - it's an interesting SPL challenge.

[peiffer]

I was batting around the double transpose method with a peer and got a minor variation to work regardless of whether I was throwing the top5 or top40 at it.  And it doesn't muck with the column names. 

Given the simplicity, I think I am going to drop the idea of going after a table subsearch following the timechart.   There is no need since the double transpose method is clean and elegant.

Thank you for the suggestion and the follow ups.
Tim

|fields - _span*
|transpose 
|sort - "row 5" ```uses values from the last row (latest time) ```
|transpose header_field="column" 0
|fields - column