Dashboards & Visualizations

Splunk 6 Simple XML, dataOverlayMode on Table, can we specify which column to show heatmap on?

SplunkTrust
SplunkTrust

Hi,

I have a table in my dashboard (Splunk 6 Simple xml) which has columns like this

User      Count   %Success
User1     10      90
User2     2       100
User3     5       10

I have seen an option for dataOverlayMode which highlights the max and min value in the "Count" field. Will it be possible to show heatmap for %Success column? I don't see any options to select the column on which it shows the same.

SplunkTrust
SplunkTrust

@somesoni2 @frobinson_splunk with a tweak in SPL to identify min/max values, there could be two options to do this. Similar approaches already explained in one of older answer of mine: https://answers.splunk.com/answers/686288/how-do-you-highlight-a-table-cell-based-on-a-field.html

The slight change in SPL required would be to use eventstats to find out the min and max values for each column and prefix one space and two space characters respectively. (PS: For colorPalette space character is used so that it is not obvious from UI. For JS Extension any character can be prefixed or suffixed as delimiter as the same can be cleaned up in JS after applying required background color.)

Option 1 Using SImple XML CSS and JS Extension apply color range for values that are minimum or maximum.

Option 2 Pure Simple XML based code change using colorPalette expression type for table cell formatting (available in 6.5 and higher), regular expression can be used to identify values with two spaces and single space prefixed. Refer to documentation: https://docs.splunk.com/Documentation/Splunk/latest/Viz/TableFormatsXML#Color_palette_types_and_opti...

PS: Limitations of Option 2 using colorPalette expression approach are:
1. complex regular expression fails for example "\s\d+" does not work but "\s100" works (provided value is 100). Also " \d+" works where actual space character is used instead of \s.
2. If more that one rows have min or max values for example two rows have 100 % which is highest success rate, then table formatting (for alignment) is messed up.
3. Manual <format> Simple XML Configurations need to be created for each field which needs min/max applied.
4. Can not work on Splunk version prior to 6.5.

So, unless there is any restriction on the use of JS, the Option 1 using Simple XML CSS and JS extension is better approach which gives better control over required output and can be made generic to be applied across fields.

alt text

Following is the run anywhere example with Simple XML and required JS for Option 1 (for screenshot attached).

<dashboard>
  <label>Table with Min Max Highlighted in each column</label>
  <row>
    <panel>
      <table>
        <search>
          <query>| makeresults count=5 
| fields - _time
| streamstats count as sno
| eval User="User", data=random(), Count=substr(data,1,2), "Success%"=tonumber(substr(data,3,2)), User=User.sno
| sort - "Success%"
| table User Count "Success%"
| fillnull Count,"Success%" value=0
| eventstats max(Count) as maxCount min(Count) as minCount max("Success%") as maxSuccess min("Success%") as minSuccess
| eval Count=case(Count=maxCount," ".Count,Count=minCount,"  ".Count,true(),Count),
       "Success%"=case('Success%'=maxSuccess," ".'Success%','Success%'=minSuccess,"  ".'Success%',true(),'Success%')
| table User Count "Success%"
</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="Count">
          <colorPalette type="expression">if (match(value,"  \d+"), "#DC4E41",if(match(value," \d+"),"#65A637","#FFFFFF"))</colorPalette>
        </format>
        <format type="color" field="Success%">
          <colorPalette type="expression">if (match(value,"  \d+"), "#DC4E41",if(match(value," \d+"),"#65A637","#FFFFFF"))</colorPalette>
        </format>
      </table>
    </panel>
  </row>
</dashboard>

Following is the required JS (table_column_with_min_max_color.js😞

 require([
     'underscore',
     'jquery',
     'splunkjs/mvc',
     'splunkjs/mvc/tableview',
     'splunkjs/mvc/simplexml/ready!'
 ], function (_, $, mvc, TableView) {
     var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
         canRender: function (cell) {
             return _(['Count', 'Success%']).contains(cell.field);
         },
         render: function ($td, cell) {
             var label = cell.value.split("|")[0];
             var val = cell.value.split("|")[1];

             if (val == "max") {
                 $td.addClass("range-cell").addClass("css_for_max")
             }
             else if (val == "min") {
                 $td.addClass("range-cell").addClass("css_for_min")
             }else {
                 $td.addClass("range-cell").addClass("css_for_none")
             }
             $td.text(label).addClass("string");
         }
     });

     var sh = mvc.Components.get("tableWithMinMaxColor");
     if (typeof (sh) != "undefined") {
         sh.getVisualization(function (tableView) {
             // Add custom cell renderer and force re-render
             tableView.table.addCellRenderer(new CustomRangeRenderer());
             tableView.table.render();
         });
     }
 });

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Splunk Employee
Splunk Employee

Hi @somesoni2,
Thanks for this question. Data overlay is applied generally to the table and it is not possible at this time to specify which column shows the heat map or high/low values. Please stay tuned for an upcoming release, though. You might find some updates to table formatting capabilities that suit your use case!

Hope this helps!

0 Karma

Path Finder

Did you find a way to do this? I really want to be able to do this too.

0 Karma