Solved: How do you highlight a table cell based on a field...

florianduhme · ‎09-13-2018

I am trying to highlight the cells of my result table. I have seen multiple examples showing how to highlight a cell based on the value shown in the actual result table.

What I need is for the cell to get highlighted based on another value of the search result. My search result looks like this:

1. Client  System  Timestamp                OrderCount Color
2. Client1 WebShop 2018-09-12T13:00:00.000Z 200        red
3. Client1 WebShop 2018-09-12T14:00:00.000Z 100        yellow
4. Client1 BizTalk 2018-09-12T13:00:00.000Z 50         green
5. Client1 BizTalk 2018-09-12T14:00:00.000Z 90         yellow
6. ...

My query looks like this:

base search | chart values(OrderCount) over Timestamp by System

Which will result in the following table:

1. Timestamp                WebShop BizTalk
2. 2018-09-12T13:00:00.000Z 200     50
3. 2018-09-12T14:00:00.000Z 100     90
4. ...

I want to highlight the OrderCount values (200, 100, 50, 90) based on their respective value of the field "Color" from my search result.
So the cell 200 of my table would be red.

Is there any way to accomplish this?

niketn · ‎09-13-2018

@florianduhme, there are couple of options you can try. Both options would need you to merge your OrderCount and Color fields together using a delimiter like | pipe.

Option 1) Use Table Format option using colorPalette with Expression: Only if you are on Splunk Enterprise 6.5 or higher using Simple XML Dashboards. Advantage of this approach is that it is Simple XML based option hence does not require JS and/or CSS extension.
However, this will show both Field Value and Label in the final table.

Option 2) Use Simple XML JS extension to access SplunkJS stack and apply Custom Table render. Using JavaScript split the field value as Values and Label. Value is used to apply CSS Class for Background Color override and the Label is used to display the final value in the table cell (drops the value for Value fields which is only required for coloring table cells). Refer to answer by @kamlesh_vaghela https://answers.splunk.com/answers/661894/how-to-color-cell-contents-with-css-and-js.html

Refer to attached screenshot/code for output by both the approaches as explained above:

Following is the Sample Simple XML and JavaScript Code for run anywhere search based on Sample Data Provided:

Simple XML Dashboard:

<dashboard script="table_color_by_another_field.js" >
  <label>Highlight Color Based on Another Value</label>
  <row>
    <panel>
      <table id="tableWithoutColor">
        <title>Table with Value and Color Combined (With Simple XML Color Palette Expression)</title>
        <search>
          <query>| makeresults 
| eval data= "Client1 WebShop 2018-09-12T13:00:00.000Z 200 red;Client1 WebShop 2018-09-12T14:00:00.000Z 100 yellow;Client1 BizTalk 2018-09-12T13:00:00.000Z 50 green;Client1 BizTalk 2018-09-12T14:00:00.000Z 90 yellow" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=" " 
| eval Client=mvindex(data,0), System=mvindex(data,1), TimeStamp=mvindex(data,2), OrderCount=mvindex(data,3), Color=mvindex(data,4)
| fields - _time data
| eval CountSLA=OrderCount."|".Color
| chart last(CountSLA) by TimeStamp System</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>
        <format type="color" field="WebShop">
          <colorPalette type="expression">if(match(value,"green"),"#65A637",if(match(value,"yellow"),"#FFFF00","#FF0000"))</colorPalette>
        </format>
        <format type="color" field="BizTalk">
          <colorPalette type="expression">if(match(value,"green"),"#65A637",if(match(value,"yellow"),"#FFFF00","#FF0000"))</colorPalette>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <html>
        <style>
           .css_for_green{ 
           background-color:#65A637 !important;
           }
           .css_for_yellow{ 
           background-color:#FFFF00 !important;
           }
           .css_for_red{
           background-color:#FF0000 !important;
           }
           .css_for_grey{
           background-color:#EEE000 !important;
           }
        </style>
      </html>
      <table id="tableWithColorBasedOnAnotherField">
        <title>Table with Value and Color Combined (With Simple XML JS Extension)</title>
        <search>
          <query>| makeresults 
| eval data= "Client1 WebShop 2018-09-12T13:00:00.000Z 200 red;Client1 WebShop 2018-09-12T14:00:00.000Z 100 yellow;Client1 BizTalk 2018-09-12T13:00:00.000Z 50 green;Client1 BizTalk 2018-09-12T14:00:00.000Z 90 yellow" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=" " 
| eval Client=mvindex(data,0), System=mvindex(data,1), TimeStamp=mvindex(data,2), OrderCount=mvindex(data,3), Color=mvindex(data,4)
| fields - _time data
| eval CountSLA=OrderCount."|".Color
| chart last(CountSLA) by TimeStamp System</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 code for JavaScript Extension code table_color_by_another_field.js, as required by above dashboard. PS: Since this is a static file needs to be placed under your App's appserver/static folder. If the folder does not exist the same needs to be created. For example: $SPLUNK_HOME/etc/apps/<yourAPPName>/appserver/static/table_color_by_another_field.js.

Because of the use of Static file in order for the changes to reflect you might have to refresh, bump or restart your Splunk Instance. Also you may need to clean up Internet Browser history.

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

    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function (cell) {
            return _(['BizTalk', 'WebShop']).contains(cell.field);
        },
        render: function ($td, cell) {
            var label = cell.value.split("|")[0];
            var val = cell.value.split("|")[1];

            if (val == "green") {
                $td.addClass("range-cell").addClass("css_for_green")
            }
            else if (val == "yellow") {
                $td.addClass("range-cell").addClass("css_for_yellow")
            }
            else if (val == "red") {
                $td.addClass("range-cell").addClass("css_for_red")
            } else {
                $td.addClass("range-cell").addClass("css_for_grey")
            }
            $td.text(label).addClass("string");
        }
    });

    var sh = mvc.Components.get("tableWithColorBasedOnAnotherField");
    if (typeof (sh) != "undefined") {
        sh.getVisualization(function (tableView) {
            // Add custom cell renderer and force re-render
            tableView.table.addCellRenderer(new CustomRangeRenderer());
            tableView.table.render();
        });
    }
});

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn · ‎04-25-2020

Documenting another answer for the Pure Simple XML based Table Cell coloring based another cell with delimited value (NO JS Required). Refer to the following answer for approach of converting delimited field into multi-value field and then color based on second index value and finally hiding the second div of multi-value which is used only for applying color and not for display.

Refer to the answer for details: https://answers.splunk.com/answers/820403/how-to-change-font-color-based-on-a-condition-for.html

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Rukmani_Splunk · ‎03-18-2020

Hi i need to color a column with help of the other.
For example color the sourcetype with the help of count. Here is my code. But the coloring is not happening .

test1

<panel>
   <html>
     <style>
        .css_for_green{ 
        background-color:#65A637 !important;
        }
        .css_for_yellow{ 
        background-color:#FFFF00 !important;
        }
        .css_for_red{
        background-color:#FF0000 !important;
        }
        .css_for_grey{
        background-color:#EEE000 !important;
        }
     </style>
   </html>
   <table id="tableWithColorBasedOnAnotherField">
    <search>
      <query>index=_internal |stats count by  sourcetype |eval sourcetype=sourcetype."|".count</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>

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

  var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
      canRender: function (cell) {
          return _(['sourcetype']).contains(cell.field);
      },
      render: function ($td, cell) {
          var label = cell.value.split("|")[0];
          var val = cell.value.split("|")[1];

          if (val > 4) {
             $td.addClass("range-cell").addClass("css_for_green")
          }
          else if (val > 5) {
             $td.addClass("range-cell").addClass("css_for_yellow")
          }
          else if (val > 6) {
             $td.addClass("range-cell").addClass("css_for_red")
          }
          $td.text(label).addClass("string");
      }
  });

  var sh = mvc.Components.get("tableWithColorBasedOnAnotherField");
  if (typeof (sh) != "undefined") {
      sh.getVisualization(function (tableView) {
          // Add custom cell renderer and force re-render
          tableView.table.addCellRenderer(new CustomRangeRenderer());
          tableView.table.render();
      });
  }

});
please help me where i am wrong

niketn · ‎03-18-2020

@Rukmani_Splunk works fine for me. Obviously I had to increase the thresholds for 4 , 5, 6 as there are definitely more than 4 events per sourcetype in last 24 hours and they all turned Green.

If you have put the JS file correctly in your Splunk App's appserver/static directory and the same has been included in the dashboard's root node as script="yourJSFileName.js", then it should work. Try debug/refresh or bump for non-prod instance or even restart if required. Also try clearing Browser History to ensure static file is not loaded from cache.

In order to ensure that script file has been uploaded correctly in your app's appserver/static folder, type the following in your browser URL, change your Splunk Server Name, Splunk App Name and JS file name as required:

http://<yourSplunkServer>:8000/en-US/static/app/<yourAppName>/<yourJSFile>.js

Please try above steps and confirm. Even with your existing code, you should see all Sourcetype with Green background.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

florianduhme · ‎09-17-2018

@niketnilay, do you have any suggestions on how to wait for the page/search to finish loading, before the script will override this color?

My query is actually running pretty long, and I seem to have a problem when the query runs too long, that the resulting table is truncated.

DevTools is also throwing this error: "TypeError: Cannot read property 'split' of null".
I think the script tries to override the color before the data has actually loaded completely within the dashboard.

niketn · ‎09-13-2018

@florianduhme, there are couple of options you can try. Both options would need you to merge your OrderCount and Color fields together using a delimiter like | pipe.

Option 1) Use Table Format option using colorPalette with Expression: Only if you are on Splunk Enterprise 6.5 or higher using Simple XML Dashboards. Advantage of this approach is that it is Simple XML based option hence does not require JS and/or CSS extension.
However, this will show both Field Value and Label in the final table.

Option 2) Use Simple XML JS extension to access SplunkJS stack and apply Custom Table render. Using JavaScript split the field value as Values and Label. Value is used to apply CSS Class for Background Color override and the Label is used to display the final value in the table cell (drops the value for Value fields which is only required for coloring table cells). Refer to answer by @kamlesh_vaghela https://answers.splunk.com/answers/661894/how-to-color-cell-contents-with-css-and-js.html

Refer to attached screenshot/code for output by both the approaches as explained above:

Following is the Sample Simple XML and JavaScript Code for run anywhere search based on Sample Data Provided:

Simple XML Dashboard:

<dashboard script="table_color_by_another_field.js" >
  <label>Highlight Color Based on Another Value</label>
  <row>
    <panel>
      <table id="tableWithoutColor">
        <title>Table with Value and Color Combined (With Simple XML Color Palette Expression)</title>
        <search>
          <query>| makeresults 
| eval data= "Client1 WebShop 2018-09-12T13:00:00.000Z 200 red;Client1 WebShop 2018-09-12T14:00:00.000Z 100 yellow;Client1 BizTalk 2018-09-12T13:00:00.000Z 50 green;Client1 BizTalk 2018-09-12T14:00:00.000Z 90 yellow" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=" " 
| eval Client=mvindex(data,0), System=mvindex(data,1), TimeStamp=mvindex(data,2), OrderCount=mvindex(data,3), Color=mvindex(data,4)
| fields - _time data
| eval CountSLA=OrderCount."|".Color
| chart last(CountSLA) by TimeStamp System</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>
        <format type="color" field="WebShop">
          <colorPalette type="expression">if(match(value,"green"),"#65A637",if(match(value,"yellow"),"#FFFF00","#FF0000"))</colorPalette>
        </format>
        <format type="color" field="BizTalk">
          <colorPalette type="expression">if(match(value,"green"),"#65A637",if(match(value,"yellow"),"#FFFF00","#FF0000"))</colorPalette>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <html>
        <style>
           .css_for_green{ 
           background-color:#65A637 !important;
           }
           .css_for_yellow{ 
           background-color:#FFFF00 !important;
           }
           .css_for_red{
           background-color:#FF0000 !important;
           }
           .css_for_grey{
           background-color:#EEE000 !important;
           }
        </style>
      </html>
      <table id="tableWithColorBasedOnAnotherField">
        <title>Table with Value and Color Combined (With Simple XML JS Extension)</title>
        <search>
          <query>| makeresults 
| eval data= "Client1 WebShop 2018-09-12T13:00:00.000Z 200 red;Client1 WebShop 2018-09-12T14:00:00.000Z 100 yellow;Client1 BizTalk 2018-09-12T13:00:00.000Z 50 green;Client1 BizTalk 2018-09-12T14:00:00.000Z 90 yellow" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=" " 
| eval Client=mvindex(data,0), System=mvindex(data,1), TimeStamp=mvindex(data,2), OrderCount=mvindex(data,3), Color=mvindex(data,4)
| fields - _time data
| eval CountSLA=OrderCount."|".Color
| chart last(CountSLA) by TimeStamp System</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 code for JavaScript Extension code table_color_by_another_field.js, as required by above dashboard. PS: Since this is a static file needs to be placed under your App's appserver/static folder. If the folder does not exist the same needs to be created. For example: $SPLUNK_HOME/etc/apps/<yourAPPName>/appserver/static/table_color_by_another_field.js.

Because of the use of Static file in order for the changes to reflect you might have to refresh, bump or restart your Splunk Instance. Also you may need to clean up Internet Browser history.

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

    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function (cell) {
            return _(['BizTalk', 'WebShop']).contains(cell.field);
        },
        render: function ($td, cell) {
            var label = cell.value.split("|")[0];
            var val = cell.value.split("|")[1];

            if (val == "green") {
                $td.addClass("range-cell").addClass("css_for_green")
            }
            else if (val == "yellow") {
                $td.addClass("range-cell").addClass("css_for_yellow")
            }
            else if (val == "red") {
                $td.addClass("range-cell").addClass("css_for_red")
            } else {
                $td.addClass("range-cell").addClass("css_for_grey")
            }
            $td.text(label).addClass("string");
        }
    });

    var sh = mvc.Components.get("tableWithColorBasedOnAnotherField");
    if (typeof (sh) != "undefined") {
        sh.getVisualization(function (tableView) {
            // Add custom cell renderer and force re-render
            tableView.table.addCellRenderer(new CustomRangeRenderer());
            tableView.table.render();
        });
    }
});

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

aftasuncion · ‎06-15-2022

Hi, i'm using the exact script and it is working. However I have cells that are multi-value and now it's not working, i'm suspecting it could be the JS. Are there any workarounds?

florianduhme · ‎09-17-2018

There is one more thing that I'm concerned about.

When I first tried this out, it worked as expected. Now I am having the problem, that I can see the result table with its colors while the query is loading. After the query finishes to load, the table disappears.

Is there anything I can do to avoid this?

florianduhme · ‎09-17-2018

I found the actual problem:

Some of my cells of the resulting table were empty (null value). The javascript file threw an exception: "TypeError: Cannot read property 'split' of null".

In order to fix this, I added the command "fillnull" at the end of my search query:

| fillnull value="0|green" <fieldname>

Where fieldname needs to be replaced with any fieldname that can have null values in the resulting table.

florianduhme · ‎09-14-2018

Thank you so much for this very detailed answer! I will try this out and reply as soon as possible.

A very good trick to just concatenate the two fields and color the cell based on that!

Thank again.

niketn · ‎09-14-2018

Sure try out and let us know if you need any help 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

florianduhme · ‎09-17-2018

Hi niketnilay,

I tried this out and it worked like a charm!

There is only one thing that I noticed. Some of my column names include an underscore, for example, "Sap_ABC". It looks like the script can't really handle this. After I renamed the column names and removed the underscore, everything worked as expected.

Thanks a lot!

How do you highlight a table cell based on a field of the search result?

Enterprise Security Content Update (ESCU) | New Releases

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

Index This | What are the 12 Days of Splunk-mas?