Why is the table cell highlighting, not reading the correct values?

[justinmboucher]

I'm using Splunk version 7.0.1 and I'm trying to highlight my table cell colors based off of two other fields. I have the script working, by modifying the one included in the dashboards app, but it randomly highlights some cells in the wrong color. I can't seem to figure out why.

The "Average Response Time" should be:
- Red if the value is greater than or equal to the Threshold
- Amber if the value is greater than or equal to the Objective but less than the Threshold
- Green if the value is less than the Objective

My Table:

Transaction    Count   "Average Response Time"    Objective   Threshold
A/P - Close Module - FM1    1   7.52    2.00    6.00  **<-Colored Red and correctly**
...
A/P - Diagnosis- Run Search - FM1   2   4.01    100.00  100.00 **<- Colored Amber incorrectly**

Here is the dashboard source of the table:

<table id="response_time_highlight">
        <title>NOTE: An Objective and/or Threshold of 100 indicates that an Objective and/or Threshold have not been identified for this particular transaction type.</title>
        <search>
          <query>index=arm sourcetype="arm:transaction" region="$region$" sitename="$sitename$" transaction_status IN ( $transaction_status$ ) username IN ( $username$ ) workstation_name IN ( $workstation_name$ ) transaction_name IN ( $transaction_name$ ) 
| stats count avg(response_time) AS response_time BY transaction_name objective threshold 
| eval threshold=round(threshold,2) 
| eval objective=round(objective,2) 
| eval response_time=round(response_time,2) 
| fields transaction_name count response_time objective threshold 
| rename response_time AS "Average Response Time" transaction_name AS "Transaction Type" objective as Objective threshold as Threshold count as "Transaction Count"
| sort +transaction_name</query>
          <earliest>$search_time.earliest$</earliest>
          <latest>$search_time.latest$</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="refresh.display">progressbar</option>
        <option name="rowNumbers">true</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>

Here is the Javascript code (modified version of the dashboard examples one):

    require([
        'underscore',
        'jquery',
        'splunkjs/mvc',
        'splunkjs/mvc/tableview',
        'splunkjs/mvc/simplexml/ready!'
    ], function (_, $, mvc, TableView) {

    var objective_value;
    var threshold_value;

    // Row Coloring Example with custom, client-side range interpretation
    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function (cell) {
            // Enable this custom cell renderer for response_time field
            return _(['Average Response Time', 'Objective', 'Threshold']).contains(cell.field);
        },
        render: function ($td, cell) {
            // Add a class to the cell based on the returned value
            var value = parseFloat(cell.value);

            if (cell.field === 'Objective') {
                objective_value = value;
            }

            if (cell.field === 'Threshold') {
                threshold_value = value;
            }

            // Apply interpretation for number of historical searches
            if (cell.field === 'Average Response Time') {
                if (value >= threshold_value) {
                    $td.addClass('range-cell').addClass('range-severe');
                }
                else if (value >= objective_value) {
                    $td.addClass('range-cell').addClass('range-elevated');
                }
                else if (value < objective_value) {
                    $td.addClass('range-cell').addClass('range-low');
                }
            }
            // Update the cell content
            $td.text(value.toFixed(2)).addClass('numeric');
        }
    });
    mvc.Components.get('response_time_highlight').getVisualization(function (tableView) {
        // Add custom cell renderer, the table will re-render automatically.
        tableView.addCellRenderer(new CustomRangeRenderer());
    });
});

EDIT: Added a console.log to the javascript showing the output of each field. Noticed that some are showing as undefined, and pushing the correct numbers down to the next row. Here is the output:

0 undefined undefined
8.64 100 100
7.52 2 6
0 2 6
7.52 2 6
1.11 10 25
2.28 2 6
2.92 2 6

[niketn · Accepted solution]

@justinmboucher, when you try to extend the TableView BaseCellRender, you have access only to single table cell value at a time in the render function, i.e. it can be either Average Response Time, Objective or Threshold at a time not all of them.

So you would need to perform your range calculation in the query itself and then use range cell value based on the examples provided in Splunk Dashboard Examples App for table formatting to create one of the following three options:

Following is a run anywhere dashboard example with JavaScript Extension for the attached screenshot based on mocked data as per your question.

Following is the eval you would need to add to your current search to create a new field Average Response Time SLA:

 <yourBaseSearch>
|  eval "Average Response Time SLA"=case('Average Response Time'>=Threshold,"severe",'Average Response Time'<Threshold AND 'Average Response Time'>=Objective,"elevated",true(),"low")

Following is Simple XML Dashboard Code:

<dashboard script="table_formatting_rangemap.js">
  <label>Color based on Other Cell Values</label>
  <row>
    <panel>
      <html>
        <style>
          /* CSS for table response_time_highlight_icon */
          #response_time_highlight_icon td.icon {
              text-align: center;
          }
          #response_time_highlight_icon td.icon i {
              font-size: 25px;
              text-shadow: 1px 1px #aaa;
          }
          #response_time_highlight_icon td.icon .severe {
              color: indianred;
          }
          #response_time_highlight_icon td.icon .elevated {
              color: orange;
          }
          #response_time_highlight_icon td.icon .low {
              color: #lightgreen ;
          }

          /* CSS for table response_time_highlight_cell */
          #response_time_highlight_cell td.range-low {
              background-color: lightgreen !important;
          }
          #response_time_highlight_cell td.range-elevated {
              background-color: orange !important;
              font-weight: bold;
          }
          #response_time_highlight_cell td.range-severe {
              background-color: indianred !important;
              font-weight: bold;
          }

          /* CSS for table response_time_highlight_row */
          #response_time_highlight_row tr td {
              background-color: lightgreen !important;
          }
          #response_time_highlight_row tr.range-elevated td {
              background-color: orange !important;
          }
          #response_time_highlight_row tr.range-severe td {
              background-color: indianred !important;
          }
          #response_time_highlight_row .table td {
              border-top: 1px solid #fff;
          }
        </style>
      </html>
      <table id="response_time_highlight_icon">
        <search>
          <query>|  makeresults
|  eval data="A/P - Close Module - FM1,1,7.52,2.00,6.00;A/P - Diagnosis- Run Search - FM1,2,4.01,100.00,100.00;A/P - Sample Test - FM1,3,4.5,3.00,6.00;"
|  makemv data delim=";" 
|  mvexpand data
|  eval data=split(data,",")
|  eval Transaction=mvindex(data,0),count=mvindex(data,1),"Average Response Time"=mvindex(data,2),Objective=mvindex(data,3),Threshold=mvindex(data,4)
|  table Transaction count "Average Response Time" Objective Threshold
|  eval "Average Response Time SLA"=case('Average Response Time'>=Threshold,"severe",'Average Response Time'<Threshold AND 'Average Response Time'>=Objective,"elevated",true(),"low")</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">10</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>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <table id="response_time_highlight_cell">
        <search>
          <query>|  makeresults
|  eval data="A/P - Close Module - FM1,1,7.52,2.00,6.00;A/P - Diagnosis- Run Search - FM1,2,4.01,100.00,100.00;A/P - Sample Test - FM1,3,4.5,3.00,6.00;"
|  makemv data delim=";" 
|  mvexpand data
|  eval data=split(data,",")
|  eval Transaction=mvindex(data,0),count=mvindex(data,1),"Average Response Time"=mvindex(data,2),Objective=mvindex(data,3),Threshold=mvindex(data,4)
|  table Transaction count "Average Response Time" Objective Threshold
|  eval "Average Response Time SLA"=case('Average Response Time'>=Threshold,"severe",'Average Response Time'<Threshold AND 'Average Response Time'>=Objective,"elevated",true(),"low")</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">10</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>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <table id="response_time_highlight_row">
        <search>
          <query>|  makeresults
|  eval data="A/P - Close Module - FM1,1,7.52,2.00,6.00;A/P - Diagnosis- Run Search - FM1,2,4.01,100.00,100.00;A/P - Sample Test - FM1,3,4.5,3.00,6.00;"
|  makemv data delim=";" 
|  mvexpand data
|  eval data=split(data,",")
|  eval Transaction=mvindex(data,0),count=mvindex(data,1),"Average Response Time"=mvindex(data,2),Objective=mvindex(data,3),Threshold=mvindex(data,4)
|  table Transaction count "Average Response Time" Objective Threshold
|  eval "Average Response Time SLA"=case('Average Response Time'>=Threshold,"severe",'Average Response Time'<Threshold AND 'Average Response Time'>=Objective,"elevated",true(),"low")</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">10</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>
      </table>
    </panel>
  </row>  
</dashboard>

Following is the Javascript code table_formatting_rangemap.js:

require([
    'underscore',
    'jquery',
    'splunkjs/mvc',
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/simplexml/ready!'
], function(_, $, mvc, TableView) {
    // Translations from rangemap results to CSS class
    var ICONS = {
        severe: 'alert-circle',
        elevated: 'alert',
        low: 'check-circle'
    };
    var RangeMapIconRenderer = TableView.BaseCellRenderer.extend({
        canRender: function(cell) {
            // Only use the cell renderer for the "Average Response Time SLA" field
            return cell.field === 'Average Response Time SLA';
        },
        render: function($td, cell) {
            var icon = 'check-circle';
            // Fetch the icon for the value
            if (ICONS.hasOwnProperty(cell.value)) {
                icon = ICONS[cell.value];
            }
            // Create the icon element and add it to the table cell
            $td.addClass('icon').html(_.template('<i class="icon-<%-icon%> <%- range %>" title="<%- range %>"></i>', {
                icon: icon,
                range: cell.value
            }));
        }
    });
    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function(cell) {
            // Enable this custom cell renderer for 'Average Response Time SLA'
            return _(['Average Response Time SLA']).contains(cell.field);
        },
        render: function($td, cell) {
            // Add a class to the cell based on the returned value
            var strSLA = cell.value;
            if (strSLA!==undefined){
                strSLA=strSLA.trim();
                $td.addClass('range-cell').addClass("range-"+strSLA);
            }
            $td.text(strSLA).addClass('string');
        }
    }); 
    mvc.Components.get("response_time_highlight_icon").getVisualization(function(tableView){
        // Register custom cell renderer, the table will re-render automatically
        tableView.addCellRenderer(new RangeMapIconRenderer());
    }); 
    mvc.Components.get("response_time_highlight_cell").getVisualization(function(tableView){
        tableView.on("rendered", function() {
            console.log("Table Rendered");
            setTimeout(function(){
                $("#response_time_highlight_cell tr").each(function() {
                    var strSLA=$(this).find("td:last").html();
                    if (strSLA!==undefined){
                        strSLA=strSLA.trim();
                        $(this).find("td:nth-child(3)").addClass("range-cell").addClass("range-"+strSLA);
                    }
                });
            },100);
        });
    });
    mvc.Components.get("response_time_highlight_row").getVisualization(function(tableView) {
        tableView.on('rendered', function() {
            setTimeout(function(){
                // Apply class of the cells to the parent row in order to color the whole row
                tableView.$el.find('td.range-cell').each(function() {
                    $(this).parents('tr').addClass(this.className);
                });
            },100);
        });
        // Add custom cell renderer, the table will re-render automatically.
        tableView.addCellRenderer(new CustomRangeRenderer());
    });
});

[tefa627]

@niketn Any assistance please. I have been trying to figure out this cell highlighting with multiple fields for the past week.

[tefa627]

I have copied the xml and js but the first 2 panels are still not highlighting. What am I doing wrong??

[shocko]

This was extremely useful for me in my use case. Thanks for posting this!

[justinmboucher]

That worked. Never thought about adding an additional column and running the code off of that. Thanks for the help.