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!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...