Color-Code Stats table by row

wwhite12 · ‎08-13-2020

Hello,

I have a table that looks similar to the example below, there are some values in columns that do not need to be color coded at all, while other values in the same column need to be color coded based on value range. Is it possible to color code cells based on row rather than column? For example, below I would only need the "% Completed" row's values to be color coded but not the other row's values.

Thanks in advance.

	Team 1	Team 2	Team 3
Total Stories	5	10	20
Stories Completed	5	0	10
% Completed	100%	0%	50%

niketn · ‎08-15-2020

@wwhite12 Ideally you should use Simple XML JS extension as your requirement it complex. However, with colorPalette expression option and table allowing multi value fields, you can refer to one of my older answers for applying Color based on Simple XML: https://community.splunk.com/t5/Dashboards-Visualizations/How-to-change-font-color-based-on-a-condit...

In your case you will need additional SPL step to convert only the cells with % into multi-value cell and based on value apply range for applying color expression. Finally use Simple XML CSS extension to hide the multi-value cell for applying required color range.

Following is a run anywhere example that you can use!

<dashboard>
  <label>Table with color Based on Percent</label>
  <row>
    <panel>
      <title>Color Ranges | 0-40: Red | 40-90: Yellow | 90+: Green</title>
      <html depends="$alwaysHideHTMLCSSPanel$">
        <style>
          #tableColorFinalRowBasedOnData table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
      <table id="tableColorFinalRowBasedOnData">
        <search>
          <query>| makeresults
| eval data="Total Stories,5,10,20;Stories Completed,5,0,10;% Completed,100%,0%,50%"
| makemv data delim=";"
| mvexpand data
| makemv data delim=","
| eval State=mvindex(data,0),Team1=mvindex(data,1),Team2=mvindex(data,2),Team3=mvindex(data,3)
| table State Team*
| foreach Team* [| eval "&lt;&lt;FIELD&gt;&gt;"=case(State="% Completed" AND 
                                       tonumber(substr('&lt;&lt;FIELD&gt;&gt;',1,len('&lt;&lt;FIELD&gt;&gt;')-1))&gt;=0 AND 
                                       tonumber(substr('&lt;&lt;FIELD&gt;&gt;',1,len('&lt;&lt;FIELD&gt;&gt;')-1))&lt;40 ,'&lt;&lt;FIELD&gt;&gt;'."|LOW",
                                       State="% Completed" AND 
                                       tonumber(substr('&lt;&lt;FIELD&gt;&gt;',1,len('&lt;&lt;FIELD&gt;&gt;')-1))&gt;=40 AND 
                                       tonumber(substr('&lt;&lt;FIELD&gt;&gt;',1,len('&lt;&lt;FIELD&gt;&gt;')-1))&lt;90 ,'&lt;&lt;FIELD&gt;&gt;'."|MEDIUM",
                                       State="% Completed" AND
                                       tonumber(substr('&lt;&lt;FIELD&gt;&gt;',1,len('&lt;&lt;FIELD&gt;&gt;')-1))&gt;=90,'&lt;&lt;FIELD&gt;&gt;'."|HIGH",
                                       true(),'&lt;&lt;FIELD&gt;&gt;')]
| foreach Team* [| eval "&lt;&lt;FIELD&gt;&gt;"=split('&lt;&lt;FIELD&gt;&gt;',"|")]</query>
          <earliest>-24h@h</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>
        <format type="color" field="Team1">
          <colorPalette type="expression">case (match(value,"LOW"), "#DC4E41",match(value,"MEDIUM"), "#F8BE34",match(value,"HIGH"),"#53A051")</colorPalette>
        </format>
        <format type="color" field="Team2">
          <colorPalette type="expression">case (match(value,"LOW"), "#DC4E41",match(value,"MEDIUM"), "#F8BE34",match(value,"HIGH"),"#53A051")</colorPalette>
        </format>
        <format type="color" field="Team3">
          <colorPalette type="expression">case (match(value,"LOW"), "#DC4E41",match(value,"MEDIUM"), "#F8BE34",match(value,"HIGH"),"#53A051")</colorPalette>
        </format>
      </table>
    </panel>
  </row>
</dashboard>

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

Color-Code Stats table by row

panel

table

Enter the Dashboard Challenge and Watch the .conf24 Global Broadcast!

Join Us at the Builder Bar at .conf24 – Empowering Innovation and Collaboration

Combine Multiline Logs into a Single Event with SOCK - a Guide for Advanced Users