Dashboards & Visualizations

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

mschellhouse
Path Finder

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.

0 Karma

mschellhouse
Path Finder

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

niketn
Legend

@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:

alt text

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...