topic Re: Can I color a cell based on condition? in Dashboards & Visualizations

Can I color a cell based on condition?

shreyasathavale — Wed, 18 Oct 2017 08:42:22 GMT

Hi,
I have 2 columns that shows run times for a job (ReallDuration and RunDuration) . Real duration is how much time the job should run and RunDuration is job ran for how much duration. The values are like RunDuraion=00:35:45.0000 and RealDuration=00:28:35 . I want to color the cell of RunDuration as Red if RunDuration > RealDuration using simple xml.

Re: Can I color a cell based on condition?

myriadic — Wed, 18 Oct 2017 14:06:45 GMT

is there a little paintbrush to the top right of your column? if so, click that and select color/range

Re: Can I color a cell based on condition?

niketn — Fri, 20 Oct 2017 16:54:36 GMT

@shreyasathavale, you can use Splunk Dashboard Examples App to take a look at Table Row Highlighting example.

In your case you would need to convert RealDuration to same format as RunDuration i.e. HH:MM:SS.%4N and then use strptime() to convert them to epoch time (so that they can be used for evaluating numerical difference) using %H:%M:%S.%4N. Once you calculate the Difference, the same can be used to apply row color as per your use case. Following is the run any where search example:

|  makeresults
|  eval RunDuration="00:35:45.0000"
|  eval RealDuration="00:28:35".".0000"
|  append
    [|  makeresults
|  eval RunDuration="00:25:20.0000"
|  eval RealDuration="00:30:50".".0000"]
|  append
    [|  makeresults
|  eval RunDuration="00:40:30.0000"
|  eval RealDuration="00:35:40".".0000"]
|  fields - _time
|  eval RunDurationEpoch=strptime(RunDuration,"%H:%M:%S.%4N")
|  eval RealDurationEpoch=strptime(RealDuration,"%H:%M:%S.%4N")
|  eval diffEpoch=RunDurationEpoch-RealDurationEpoch
|  fields - RunDurationEpoch RealDurationEpoch

Commands till | fields - _time generate mock data as per your requirement. You would need to use your existing Splunk query instead. Remaining pipes calculate required difference in Epoch Time for RunDuration and RealDuration. You will also need to add %4N to RealDuration to have both time fields in similar format. If RunDuration %4N is always 0000, you can ignore the following and also just use "%H:%M:%S" in your strptime() functions.

|  eval RealDuration=RealDuration.".0000"

Final command | fields - RunDurationEpoch RealDurationEpoch removes Epoch times not required.

Following is the Run anywhere dashboard code.

1) JavaScript and CSS files have been used in the dashboard i.e. script="table_row_highlighting_numeric_diff.js" stylesheet="table_decorations.css"
2) Table has been given id="highlight"

<dashboard script="table_row_highlighting.js" stylesheet="table_decorations.css">
  <label>Color Based on Two Columns</label>
  <row>
    <panel>
      <title>Color Table Row Based on duration difference</title>
      <table id="highlight">
        <search>
          <query>|  makeresults
|  eval RunDuration="00:35:45.0000"
|  eval RealDuration="00:28:35".".0000"
|  append
    [|  makeresults
|  eval RunDuration="00:25:20.0000"
|  eval RealDuration="00:30:50".".0000"]
|  append
    [|  makeresults
|  eval RunDuration="00:40:30.0000"
|  eval RealDuration="00:35:40".".0000"]
|  fields - _time
|  eval RunDurationEpoch=strptime(RunDuration,"%H:%M:%S.%4N")
|  eval RealDurationEpoch=strptime(RealDuration,"%H:%M:%S.%4N")
|  eval diffEpoch=RunDurationEpoch-RealDurationEpoch
|  fields - RunDurationEpoch RealDurationEpoch</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>

Javascript file table_row_highlighting.js (based on the Splunk Dashboard Examples App)
PS: Somehow the JavaScript to apply custom Table Row Classes was getting overridden with Splunk Default Table Renderer. Hence colors were not applying. I have added 1 sec delay in adding table row class based on cell class using setTimeOut() JavaScript function. Please adjust or remove as per your use case.

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

     // Row Coloring by Numeric Comparison of Epoch Time Value in HH:MM:SS.%4N

    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function(cell) {
            // Enable this custom cell renderer for diffEpoch field
            return _(['diffEpoch']).contains(cell.field);
        },
        render: function($td, cell) {
            // Add a class to the cell based on the returned value
            var value = cell.value;
            // Update the cell content with numeric diffEpoch value HH:MM:SS.%4N
            $td.text(value).addClass('numeric');

            // Apply interpretation for diffEpoch field
            if (cell.field === 'diffEpoch') {
                if (value >= 0) {
                        $td.addClass('range-cell').addClass('range-severe');
                }
            }
        }
    });

    mvc.Components.get('highlight').getVisualization(function(tableView) {
        // Add custom cell renderer, the table will re-render automatically.
        tableView.addCellRenderer(new CustomRangeRenderer());
        tableView.on('rendered', function() {
            // Add a delay to ensure Custom Render applies to row without getting overridden with built in reder.
            setTimeout(function() { 
                tableView.$el.find('td.range-cell').each(function() {
                        $(this).parents('tr').addClass(this.className);
                        console.log("Applying Class",this.className);
                });
            }, 100);
        });
    });
});

Following is the table_decorations.css code similar to the one used in example.

/* Row Coloring */

#highlight tr td {
    background-color: #65A637 !important;
}

#highlight tr.range-elevated td {
    background-color: #F7BC38 !important;
    font-weight: bold;
}

#highlight tr.range-severe td {
    background-color: #D93F3C !important;
    font-weight: bold;
}

#highlight .table td {
    border-top: 1px solid #fff;
}

#highlight td.range-severe, td.range-elevated {
    font-weight: bold;
}

PS: Since this example uses JavaScript and CSS static files. It might require you to Refresh/Restart Splunk and also clear your Internet Browser history for changes to reflect.
Please try out and confirm.

Re: Can I color a cell based on condition?

niketn — Wed, 01 Nov 2017 19:21:17 GMT

@shreyasathavale, were you able to test out the run anywhere dashboard as per your use case?

Re: Can I color a cell based on condition?

rrobe07 — Mon, 05 Feb 2018 21:21:17 GMT

We've run into the same issue with the table row highlighting no longer working in some cases after a Splunk upgrade. We've implemented the setTimeout which ultimately accomplishes the goal, but it also results in a flash of the default color which is then overlaid with the correct color. Since we use a number of auto-refreshing panels, the flashes looks rather unprofessional. We've also found that the time value needs to be adjusted based on how long the corresponding search takes to finish. If you set the number too low, the correct colors never show up. If you set it too high, the brief default color is more noticeable. This makes it very difficult to know which number to choose since dashboards can run in several different environments. Is there another alternative to this?

Re: Can I color a cell based on condition?

rrobe07 — Mon, 05 Feb 2018 22:34:22 GMT

Incidentally, version 6.5.4 works fine in all cases with no timeout code needed at all. When we attempted to upgrade to 6.6.4, that's when this issue started.

Re: Can I color a cell based on condition?

niketn — Tue, 13 Feb 2018 15:42:42 GMT

Yes we also noted the same. It has definitely been called out on separate question with a BUG tag.

https://answers.splunk.com/answers/614788/splunk-dashboard-examples-table-row-highlighting-b.html?childToView=615859#answer-615859

If you have a valid Splunk Entitlement you can reach out to Splunk Support.

Re: Can I color a cell based on condition?

qbolbk59 — Sat, 13 Oct 2018 11:10:19 GMT

Hi @niketnilay ,

I tried the custom script you provided above to add custom color to my table (id= mytable) rows based on the value in the Column "Severity" (Red for critical, Amber for High....), however for some reason it's not providing me intended results.

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 _(['Severity']).contains(cell.field);
         },
         render: function($td, cell) {

             var value = cell.value;


             $td.text(value).addClass('string');


             if (cell.field === 'Severity') {

                 if (value == "Critical") {
                     $td.addClass('range-cell').addClass('Critical');
                 }
                 if (value == 'High' ) {
                     $td.addClass('range-cell').addClass('High');
                 }

             }


         }
     });

     mvc.Components.get('mytable').getVisualization(function(tableView) {

         tableView.on('rendered', function() {

             setTimeout(function(){    

                 tableView.$el.find('td.range-cell').each(function() {
                     $(this).parents('tr').addClass(this.className);
                 });
             },100);
         });

         tableView.addCellRenderer(new CustomRangeRenderer());
     });
 });

Stylesheet:

 #highlight tr.Critical td {
     background-color: #FF0000 !important;
 }

 #highlight tr.High td {
     background-color: #FF7F50 !important;
 }


 #highlight tr.High, tr.Critical{
     font-weight: bold;
 }

Any idea what's going wrong here ?

Re: Can I color a cell based on condition?

niketn — Thu, 18 Oct 2018 12:30:22 GMT

@qbolbk59 seems like your table id is mytable but you are trying to apply CSS override for incorrect table id (highlight which was applicable to the example in my previous answer). So please try the following CSS instead!

      #mytable tr.Critical td {
          background-color: #FF0000 !important;
      }

      #mytable tr.High td {
          background-color: #FF7F50 !important;
      }


      #mytable tr.High, tr.Critical{
          font-weight: bold;
      }

Re: Can I color a cell based on condition?

qbolbk59 — Thu, 18 Oct 2018 13:22:12 GMT

Hi @niketnilay ,,,, That was so stupid of me.... 😛

Thanks for pointing out the mistake. However after changing the css, the results are still not as i intended. I can assure that the js and CSS are loaded in the dashboard, but somehow the color codes that i have provided are not getting loaded. All the rows are having same color.
I tried clearing the browser history/cache, but no luck.

sorry if my queries are to naive.

Re: Can I color a cell based on condition?

niketn — Thu, 18 Oct 2018 14:00:40 GMT

If this is non prod have you also tried _bump or debug/refresh? I have tested out this on Splunk 7.2 and seems to work fine!