topic Re: Splunk JavaScript question - How can I compare columns and set color based on degredation? in Dashboards & Visualizations

Splunk JavaScript question - How can I compare columns and set color based on degredation?

mschellhouse — Wed, 22 Nov 2017 18:49:53 GMT

This is really more of a JS question than a splunk question I guess...

I have a table where we need to compare columns and set color based on degradation. If column 2 is 15% greater than column 1, cell level formatting should be red. Have thresholds for each traffic light color. I have been working with the dashboard examples and can get he basic cell level formatting figured out. I am really stuck on the JS needed to compare column 2 to column 1 and iterate across the entire table.

Re: Splunk JavaScript question - How can I compare columns and set color based on degredation?

niketn — Wed, 22 Nov 2017 20:26:04 GMT

@mschellhouse, I have answered something on similar lines: https://answers.splunk.com/answers/583047/can-i-color-a-cell-based-on-condition.html

In you case you can have a Threshold % value based on which Rows will be colored. Here is the run anywhere example which you can modify as per your needs:

Simple XML Dashboard Code with CSS Style Inline:

<dashboard script="table_row_highlighting_by_threshold.js">
  <label>Color based on Increase percent Threshold</label>
  <row>
    <panel>
      <html depends="$alwaysHideCSSHTMLPanel$">
        <style>
          /* Row Coloring */

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

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

          #highlight tr.range-yellow td {
              background-color: #F7BC38 !important;
          }
          #highlight tr.range-red td {
              background-color: #D93F3C !important;
          }

          #highlight tr.range-grey td {
              background-color: #f3f3f3 !important;
          }

          #highlight .table td {
              border-top: 1px solid #fff;
          }
          /* 
          Apply bold font for rows with custom range colors other than Green(default).
          Bold font will ensure that jQuery is working fine to apply appropriate range Class. 
          */
          #highlight td.range-yellow, td.range-red, td.range-grey{
              font-weight: bold;
          }          
        </style>
      </html>
      <table id="highlight">
        <search>
          <query>|  makeresults
|  eval Column1="100",Column2="50"
|  append
    [|  makeresults
|  eval Column1="100",Column2="120"]
|  append
    [|  makeresults
|  eval Column1="100",Column2="130"]
|  fields - _time
|  eval Threshold%=round(((Column2-Column1)/Column1)*100,1)."%"</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="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</dashboard>

JavaScript code for table_row_highlighting_by_threshold.js

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

     // Row Coloring by String Comparision of Numeric Time Value in HH:MM:SS

    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function(cell) {
            // Enable this custom cell renderer for Threshold% field
            return _(["Threshold%"]).contains(cell.field);
        },
        render: function($td, cell) {
            // Add a class to the cell based on the returned value percent valuePerc
            var valuePerc = cell.value;
            // Remove trailing % sign
            var value = valuePerc.substring(0, valuePerc.length - 1);
            // Convert to floating number
            var value = parseFloat(value);
            // Apply interpretation for RAG status field
            // Since we have picked only one row for applying range Class, following if is not required.
            if (cell.field === "Threshold%") {
                //Add range class based on cell values.
                if (value <0) {
                    $td.addClass("range-cell").addClass("range-green");
                }
                else if (value >=0 && value <25 ) {
                    $td.addClass("range-cell").addClass("range-yellow");
                }
                else if (value >=25 ) {
                    $td.addClass("range-cell").addClass("range-red");
                }
                else {
                    $td.addClass("range-cell").addClass("range-grey");
                }           
            }
            // Update the cell content with percent valuePerc
            $td.text(valuePerc).addClass('string');
        }
    });

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

        tableView.on('rendered', function() {
            // Add a delay to ensure Custom Render applies to row without getting overridden with built in reder.           
            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());
    });
});

Please try out and confirm.

Re: Splunk JavaScript question - How can I compare columns and set color based on degredation?

kamlesh_vaghela — Tue, 29 Sep 2020 16:52:41 GMT

HI,

Can you please try below XML?

<dashboard script="myjs.js" stylesheet="mycss.css">
  <label>Traffic Signal</label>
  <row>
    <panel>
      <table id="my_table">
        <search>
          <query>| makeresults | eval count="red" | append [| makeresults | eval count="green"] | append [| makeresults | eval count="yellow"]</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
        <option name="count">2</option>
        <option name="drilldown">none</option>
      </table>
    </panel>
  </row>
</dashboard>

myjs.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']).contains(cell.field);
        },
        render: function($td, cell) {
            console.log("HI")
            if(cell.value=="red" || cell.value=="green" || cell.value=="yellow")
            {
                $td.html("<div class='circle_"+cell.value+"'></div>")
            }
            else if(cell.value=="NoData" || cell.value=="null")
            {
                $td.html("<div class='align_center'></div>")
            }
            else
            {
                $td.html("<div class='align_center'>"+cell.value+"</div>")
            }
        }
    });

    //List of table IDs to add icon
    var tableIDs = ["my_table"];
    for (i=0;i<tableIDs.length;i++) {
        var sh = mvc.Components.get(tableIDs[i]);
        if(typeof(sh)!="undefined") {
            sh.getVisualization(function(tableView) {
                // Add custom cell renderer and force re-render
                tableView.table.addCellRenderer(new CustomRangeRenderer());
                tableView.table.render();
            });
        }
    }    
});

mycss.css

 .circle_green { 
   width:1%;
   padding:10px 9px;
   margin:0 auto;
   border:0px solid #a1a1a1;
   border-radius:20px;
   background-color:rgb(7,245,7);
}
.circle_yellow { 
   width:1%;
   padding:10px 9px;
   margin:0 auto;
   border:0px solid #a1a1a1;
   border-radius:20px;
   background-color:rgb(245,245,0);
}
.circle_red { 
   width:1%;
   padding:10px 9px;
   margin:0 auto;
   border:0px solid #a1a1a1;
   border-radius:20px;
   background-color:rgb(245,7,7);
}

Put JS and CSS file in below folder.

SPLUNK_HOME/etc/apps/YOUR_APP/appserver/static/

Thanks

Re: Splunk JavaScript question - How can I compare columns and set color based on degredation?

mschellhouse — Mon, 27 Nov 2017 14:02:11 GMT

The initial answers that I received were excellent but I didn't do a good job of describing my actual problem. I referenced two columns for the example but I will really have multiple (10+). Each test column will compare back to the base column and I need to color code the cell based on the % difference with respect to the base column.

Example datatable I am working with...
KPI - Base - Test1 - Test2 - Test n
KPI1 - 1 - 1.1 - 0.9 - 1.5
KPI2 - 2 - 1.9 - 2.3 - 2.5

So I would calculate the % difference between 1 and 1.1, 1 and 0.9, 1 and 1.5, etc. I need to display the original value and just color code the cell. I am thinking I need to store both the base value and the test value in JS somehow and calculate the % difference there.