How to get a table cell color to change depending on the field value?

[fredkaiser]

Hi,

I've looked at a few answers now and can't make heads or tails of it, but what I am trying to do is, if the value says red/Green, the cell should be that color.

Here is what I have so far.

XML

test2

<panel>
  <table>
    <search>
      <query>index="monitor_t" sourcetype="ServiceStatus" earliest=-5m |  stats count(eval(Status="Running")) AS RunningCount |rangemap field=RunningCount red=0-60 default=green</query>
    </search>
    <option name="wrap">true</option>
    <option name="rowNumbers">true</option>
    <option name="dataOverlayMode">none</option>
    <option name="drilldown">cell</option>
    <option name="count">10</option>
  </table>
</panel>

Application.js

 if ((Splunk.util.getCurrentView() === "test2") && Splunk.Module.SimpleResultsTable) {
     Splunk.Module.SimpleResultsTable = $.klass(Splunk.Module.SimpleResultsTable, {
         onResultsRendered: function ($super) {
             var retVal = $super();
             this.myCustomHeatMapDecorator();
             return retVal;
         },
         myCustomHeatMapDecorator: function () {
             $("tr:has(td)", this.container).each(function () {
                 var tr = $(this);
                 if (tr.find("td:nth-child(1)").text() === "green") {
                     tr.addClass("greenClass");
                 }
                 if (tr.find("td:nth-child(1)").text() === "red") {
                     tr.addClass("redClass");
                 }
             });
         }
     });
 }

Application.css 

 tr.redClass td {
     background-color:#000000 !important;
     color: #FFFFFF !important;
 }

 tr.greenClass td {
     background-color:#FFFFFF !important;
     color: #000000 !important;
 }

[Flynt]

I use cell colors almost as much as column colors. The way I do this is as follows -

The dashboard xml

 <dashboard  script="cellvalue_highlight.js" stylesheet="cellvalue_highlight.css">
  <label>Throwaway</label>
  <row>
    <panel>
      <table id="A">
        <search>
          <query>index=_internal |head 100|table clientip component host group sourcetype</query>
          <earliest>-24h</earliest>
          <latest>now</latest>
        </search>
      </table>
    </panel>
  </row>
</dashboard>

Note that we include our custom cellvalue_highlight script and stylesheet (without them, no highlighting will happen!). Also notice that we have given the table an id. This allows our JavaScript to identify which tables we want to use the highlighting on.

Now let's take a look at the highlighting code - I'll be notating the code from within

// cellvalue_highlight.js
// Bringing in our required resources
require([
    'underscore',
    'jquery',
    'splunkjs/mvc',
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/simplexml/ready!'
    ],
function(_, $, mvc, TableView) {
 // Create some arbitrary table ids to iterate on (these can be any id's you prefer, I am using A for simplicity)
    var tableids = ["A"];
 // Determine how many tables we have
    var tableidlength=tableids.length;
 // Create our highlight function
    var Cellvaluerenderer = TableView.BaseCellRenderer.extend({
       canRender: function(cell) {
          return true;
       },
       render: function($td, cell) {
  // Add a class to the cell based on the returned value

          var value = cell.value;
          // In this example I am looking at the field "component" for values of ExecProcessor, Metrics, or LMDirective.
          if (cell.field == 'component') {
             if (value=='ExecProcessor') {
                $td.addClass('Red');
             }
             if (value=='Metrics') {
                $td.addClass('Amber');
             }
             if (value=='LMDirective') {
                $td.addClass('Green');
             }
          }
     // Update the cell content
          $td.text(value);
       }
    });
 // Iterate over each table in our tableids array, we need to make sure our XML tables include one of these ids for the highlighting to work
    for (var i = 0; i < tableidlength; i++) {

      if (mvc.Components.get(tableids[i])){
         mvc.Components.get(tableids[i]).getVisualization(function(tableView) {
            tableView.table.addCellRenderer(new Cellvaluerenderer());
            tableView.table.render();
        });
      }
    }
});

We're not finished yet. The javascript code above reads each cell, captures the value, assigns a class and then renders the cell back out with the original value. Now we need to add our stylesheet to do the actual highlighting.

/*  cellvalue_highlight.css*/
/*
 * #highlight td {
 * background-color: #c1ffc3 !important;
 * }
 * */

td.Amber {
background-color: #FFBF00 !important;
font-weight: bold;
color: #FFFFFF !important;
}
td.Red {
background-color: #FF0000 !important;
font-weight: bold;
color: #FFFFFF !important;
}
td.Green {
background-color: #00FF00 !important;
font-weight: bold;
color: #FFFFFF !important;
}

Notice I used 3 primary colors here, you can add as many as you like. The background-color is actually what highlights the cell. Note that !important is needed or the colors will not override the default table rendered colors.

Great! We have all the files we need. Where do they go? In your app's static directory. For instance if I was using an app called myapp, I would place the cellvalue_highlight.js and cellvalue_highlight.css in $SPLUNK_HOME/etc/apps/myapp/appserver/static

In order for this to take effect you will need to run these links in the browser (using the default Splunk location as an example) -

http://localhost:8000/en-US/_bump
http://localhost:8000/en-US/debug/refresh
You may also need to restart Splunkweb - a shortcut to doing this is running ./splunk restartss from your $SPLUNK_HOME$/bin directory. Yes, that is two s's.

[jaracan]

Hi Flynt,

What if I have 2 tables? How would I update the javascript above? Given this scenario:
Table ID "A" uses "component" field with values ExecProcessor, Metrics, or LMDirective.
Table ID "B" uses "total" field with values ( total>10 it will be highlighted with red) and default is green.

[Flynt]

Hi Jaracan. One thing I must say up front. The newer versions of Splunk will not allow single letter values for table IDs. You'll want to change them to 2 or more letters.

 // cellvalue_highlight.js
 // Bringing in our required resources
 require([
         'underscore',
         'jquery',
         'splunkjs/mvc',
         'splunkjs/mvc/tableview',
         'splunkjs/mvc/simplexml/ready!'
     ],
     function(_, $, mvc, TableView) {
         // Create some arbitrary table ids to iterate on (these can be any id's you prefer, I am using A for simplicity)
         var tableids = ["AA", "BB"];
         // Determine how many tables we have
         var tableidlength = tableids.length;
         // Create our highlight function
         var CellvaluerendererforAA = TableView.BaseCellRenderer.extend({
             canRender: function(cell) {
                 return true;
             },
             render: function($td, cell) {
                 // Add a class to the cell based on the returned value

                 var value = cell.value;
                 // In this example I am looking at the field "component" for values of ExecProcessor, Metrics, or LMDirective.
                 if (cell.field == 'component') {
                     if (value == 'ExecProcessor') {
                         $td.addClass('Red');
                     }
                     if (value == 'Metrics') {
                         $td.addClass('Amber');
                     }
                     if (value == 'LMDirective') {
                         $td.addClass('Green');
                     }
                 }
                 // Update the cell content
                 $td.text(value);
             }
         });

         var CellvaluerendererforBB = TableView.BaseCellRenderer.extend({
             canRender: function(cell) {
                 return true;
             },
             render: function($td, cell) {
                 // Add a class to the cell based on the returned value

                 var value = cell.value;
                 // In this example I am looking at the field "total" for values less than 10, if less than, we make the class green otherwise it will be flagged red
                 if (cell.field == 'total') {
                     if (value <= 10) {
                         $td.addClass('Green');
                     } else {
                         $td.addClass('Red');
                     };

                 }
                 // Update the cell content
                 $td.text(value);
             }


         });
         // Iterate over each table in our tableids array, we need to make sure our XML tables include one of these ids for the highlighting to work
         for (var i = 0; i < tableidlength; i++) {

             if (mvc.Components.get(tableids[i])) {
                 console.log(tableids[i])
                 if (tableids[i] == "AA") {
                     mvc.Components.get(tableids[i]).getVisualization(function(tableView) {
                         tableView.table.addCellRenderer(new CellvaluerendererforAA());
                         tableView.table.render();
                     });
                 };
                 if (tableids[i] == "BB") {
                     mvc.Components.get(tableids[i]).getVisualization(function(tableView) {
                         tableView.table.addCellRenderer(new CellvaluerendererforBB());
                         tableView.table.render();
                     });
                 };

             }
         }
     });

This is assuming you have now named the tables "AA" and "BB". Notice the logic, if you have more tables you can use a case statement or the like (or groups or what have you). In this way it can become pretty powerful and reusable by just having a common table naming convention and adding the script and css.

[roldancito]

Great post, I will implement this example, thanks Flynt

[tom_frotscher · Accepted solution]

Hi,

if you just want your table cells to be red or green, you can use a custom cell renderer. The best thing to do is, to take a look at the Splunk 6.x Dashboard Examples App. There you can find an example called "Table Cell Highlighting". This is used to color the cell based on a numeric value in the cell. If you can run this example, i am pretty sure you are able to customize it, to change the color depending on the words red or green.

Greetings

Tom

[fredkaiser]

After a bit of playing with the example dashboard. I finally got what I wanted. Thanks for you help Tom