topic Re: Add colors to a table for dynamic columns in Splunk Search

How to add colors to a table for dynamic columns?

robertlynch2020 — Wed, 27 Jul 2022 13:57:45 GMT

I am producing a table with time as the column header. However i can only use hour not the full date as i have to hard code it for the color to take effect.

How can i make this dynamic so i can add date or use wild characters ?

Example of the code i have written, very long and not great to look at.

 <format type="color" field="00:00:00">
          <colorPalette type="list">[#6DB7C6,#F7BC38,#D93F3C]</colorPalette>
          <scale type="threshold">1,5</scale>
        </format>
        <format type="color" field="01:00:00">
          <colorPalette type="list">[#6DB7C6,#F7BC38,#D93F3C]</colorPalette>
          <scale type="threshold">1,5</scale>
        </format>
        <format type="color" field="02:00:00">
          <colorPalette type="list">[#6DB7C6,#F7BC38,#D93F3C]</colorPalette>
          <scale type="threshold">1,5</scale>
        </format>
        <format type="color" field="03:00:00">
          <colorPalette type="list">[#6DB7C6,#F7BC38,#D93F3C]</colorPalette>
          <scale type="threshold">1,5</scale>
        </format>
        <format type="color" field="04:00:00">
          <colorPalette type="list">[#6DB7C6,#F7BC38,#D93F3C]</colorPalette>
          <scale type="threshold">1,5</scale>
        </format>
        <format type="color" field="05:00:00">
          <colorPalette type="list">[#6DB7C6,#F7BC38,#D93F3C]</colorPalette>
          <scale type="threshold">1,5</scale>
        </format>
        <format type="color" field="06:00:00">
          <colorPalette type="list">[#6DB7C6,#F7BC38,#D93F3C]</colorPalette>
          <scale type="threshold">1,5</scale>
        </format>
        <format type="color" field="07:00:00">
          <colorPalette type="list">[#6DB7C6,#F7BC38,#D93F3C]</colorPalette>
          <scale type="threshold">1,5</scale>
        </format>

Thanks in Advance
Robert

Re: Add colors to a table for dynamic columns

niketn — Thu, 17 Jan 2019 14:09:00 GMT

@robertlynch2020 refer to one of my older answers where based on Search results Dynamic color can be applied to all fields using Simple XML JS Extension and Splunk JS Stack: https://answers.splunk.com/answers/618930/how-can-i-get-the-table-cell-colorization-renderin-1.html

In the example it excludes _time from applying color. In your case it will be the TAG field which will not have color applied.

Re: Add colors to a table for dynamic columns

robertlynch2020 — Thu, 17 Jan 2019 14:54:17 GMT

Great and thanks

Re: Add colors to a table for dynamic columns

robertlynch2020 — Fri, 18 Jan 2019 12:38:40 GMT

Thanks for the replay, however i am having issues getting it to work without _time in the javascript.
I have replaced _time with TAG, but i get NaN for the values.

An ideas?

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

     var mySearch = splunkjs.mvc.Components.getInstance("myTableSearch");
     var myResults = mySearch.data("results", {count:0});
     var allFields,filteredFields;
     myResults.on("data", function(){
         allFields=myResults.data().fields;
         filteredFields=allFields.filter(filterFields);
         console.log("Filtered Fields:",filteredFields);
     });
     function filterFields(field) {
         return field !== "TAG";
     }
     // Row Coloring Example with custom, client-side range interpretation
     var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
         canRender: function(cell) {
             // Enable this custom cell renderer for all fields except TAG
             return _(filteredFields).contains(cell.field);
         },
         render: function($td, cell) {
             // Add a class to the cell based on the returned value
             var value = parseInt(cell.value);

             // Apply interpretation based on count of errors per TAG for each field
             if(cell.field !== "TAG"){
                 if (value >= 5) {
                     $td.addClass("range-cell").addClass("range-severe");
                 }
                 else if (value >= 1 && value < 5) {
                     $td.addClass("range-cell").addClass("range-elevated");
                 }
                 else if (value < 1) {
                     $td.addClass("range-cell").addClass("range-low");
                 }
                 // Update the cell content
                 $td.text(value).addClass("numeric");
             }
         }
     });
     mvc.Components.get("highlight").getVisualization(function(tableView) {
         // Add custom cell renderer, the table will re-render automatically.
         tableView.addCellRenderer(new CustomRangeRenderer());
     });

 });

For record this is the full code i am running

  <row>
    <panel depends="$alaysHideCSS$">
      <html>
         <style>
           #highlight td {
               background-color: #c1ffc3 !important;
           }
           */
           #highlight td.range-low {
               color: #C0D9D9;
           }
           #highlight td.range-elevated {
               background-color: #ffc57a !important;
               font-weight: bold;
           }
           #highlight td.range-severe {
               background-color: #d59392 !important;
               font-weight: bold;
           }          
         </style>
       </html>
    </panel>
    <panel>
      <table id="highlight">
        <search id="myTableSearch">
          <query>| tstats summariesonly=true values(All_TPS_Logs.duration) AS Duration max(All_TPS_Logs.duration) AS All_TPS_Logs.duration FROM datamodel=TPS_V5 WHERE (nodename=All_TPS_Logs host=LUAS_2019_01_01 All_TPS_Logs.duration &lt;= 1000000000000 All_TPS_Logs.duration &gt;= -10000000 (All_TPS_Logs.user=* OR NOT All_TPS_Logs.user=*) All_TPS_Logs.operationIdentity="*") All_TPS_Logs.name =*** GROUPBY _time, All_TPS_Logs.fullyQualifiedMethod span=1s 
    | rename All_TPS_Logs.fullyQualifiedMethod as series 
    | rename All_TPS_Logs.duration as value 
    | table _time series value 
    | search (series=**murex**) 
    | lookup TPS_TAGS_HIGH_LEVEL Method#Class AS "series" OUTPUT TAG Threshold 
    | where value &gt; Threshold 
    | fillnull Threshold 
    | fillnull TAG 
    | eval TAG=if(TAG=0,"PLEASE_ADD_TAG",TAG) 
     | search NOT TAG = "PLEASE_ADD_TAG"
| timechart span=1h count(value) by TAG 
| untable _time TAG value 
| eval c_time=strftime(_time,"%T") 
| xyseries TAG c_time value | rename TAG as _time</query>
            <earliest>$time_token.earliest$</earliest>
          <latest>$time_token.latest$</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="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>

Re: Add colors to a table for dynamic columns

niketn — Fri, 18 Jan 2019 15:04:26 GMT

@robertlynch2020 Based on the Screenshot you had attached in the question your TAG field had text. I am not sure why in your SPL you are performing | rename TAG as _time

Having said that in the Tabular view without the JS ensure that all Field values are populated. Using eval or fillnull. If not you might have to handle NULL values in the JavaScript, because the following expects cell value as integer not null.

var value = parseInt(cell.value);

Re: Add colors to a table for dynamic columns

robertlynch2020 — Fri, 18 Jan 2019 16:05:24 GMT

Thanks for your replay.
I added in the workaround of renaming it to _time as if i leave it as TAG i will get NaN.
I don't have any NULL values.

So if i try the follwing i will get NaN, but if i use my workaround of renaming | rename TAG as _time it working. The data does not change.

The below code does not work. (Unless i change 2 things, 1st rename TAG to _time and update script from TAG to _time)

<query>| tstats summariesonly=true values(All_TPS_Logs.duration) AS Duration max(All_TPS_Logs.duration) AS All_TPS_Logs.duration FROM datamodel=TPS_V5 WHERE (nodename=All_TPS_Logs host=LUAS_2019_01_01 All_TPS_Logs.duration &lt;= 1000000000000 All_TPS_Logs.duration &gt;= -10000000 (All_TPS_Logs.user=* OR NOT All_TPS_Logs.user=*) All_TPS_Logs.operationIdentity="*") All_TPS_Logs.name =*** GROUPBY _time, All_TPS_Logs.fullyQualifiedMethod span=1s 
    | rename All_TPS_Logs.fullyQualifiedMethod as series 
    | rename All_TPS_Logs.duration as value 
    | table _time series value 
    | search (series=**murex**) 
    | lookup TPS_TAGS_HIGH_LEVEL Method#Class AS "series" OUTPUT TAG Threshold 
    | where value &gt; Threshold 
    | fillnull Threshold 
    | fillnull TAG 
    | eval TAG=if(TAG=0,"PLEASE_ADD_TAG",TAG) 
     | search NOT TAG = "PLEASE_ADD_TAG"
| timechart span=1h count(value) by TAG 
| untable _time TAG value 
| eval c_time=strftime(_time,"%F %T") 
| xyseries TAG c_time value </query>

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

     var mySearch = splunkjs.mvc.Components.getInstance("myTableSearch");
     var myResults = mySearch.data("results", {count:0});
     var allFields,filteredFields;
     myResults.on("data", function(){
         allFields=myResults.data().fields;
         filteredFields=allFields.filter(filterFields);
         console.log("Filtered Fields:",filteredFields);
     });
     function filterFields(field) {
         return field !== "TAG";
     }
     // Row Coloring Example with custom, client-side range interpretation
     var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
         canRender: function(cell) {
             // Enable this custom cell renderer for all fields except TAG
             return _(filteredFields).contains(cell.field);
         },
         render: function($td, cell) {
             // Add a class to the cell based on the returned value
             var value = parseInt(cell.value);

             // Apply interpretation based on count of errors per TAG for each field
             if(cell.field !== "TAG"){
                 if (value >= 5) {
                     $td.addClass("range-cell").addClass("range-severe");
                 }
                 else if (value >= 1 && value < 5) {
                     $td.addClass("range-cell").addClass("range-elevated");
                 }
                 else if (value < 1) {
                     $td.addClass("range-cell").addClass("range-low");
                 }
                 // Update the cell content
                 $td.text(value).addClass("numeric");
             }
         }
     });
     mvc.Components.get("highlight").getVisualization(function(tableView) {
         // Add custom cell renderer, the table will re-render automatically.
         tableView.addCellRenderer(new CustomRangeRenderer());
     });

 });

Re: Add colors to a table for dynamic columns

robertlynch2020 — Tue, 29 Sep 2020 22:50:15 GMT

On a separate question.
If i have 2 tables with different colors needs on the same page.
AS you can have 2 tables with the same ID i hvae tried to duplicate as much as i can... but i am missing something
I have tried to duplicate your cool script

Then i have renamed parts, but

    <search id="myTableSearch1">

Then in the script
var mySearch = splunkjs.mvc.Components.getInstance("myTableSearch1");

But i cant get it to work, so sorry my java script is not great, what else do i need to change.

so this is my trying to call the new script

<panel depends="$alaysHideCSS$">
  <html>
     <style>
       #highlight1 td {
           background-color: #6DB7C6 !important;
       }
       */
       #highlight1 td.range-low {
           color: #6DB7C6;
       }
       #highlight1 td.range-elevated {
           background-color: #ffc57a !important;
           font-weight: bold;
       }
       #highlight1 td.range-severe {
           background-color: #d59392 !important;
           font-weight: bold;
       }          
     </style>
   </html>
</panel>
<panel>
 <title>OK - Real Time (Count)</title>
  <table id="highlight1">
    <search id="myTableSearch1">
      <query>| tstats summariesonly=true values(All_TPS_Logs.duration) AS Duration max(All_TPS_Logs.duration) AS All_TPS_Logs.duration FROM datamodel=TPS_V5 WHERE (nodename=All_TPS_Logs host=LUAS_2019_01_01 All_TPS_Logs.duration &lt;= 1000000000000 All_TPS_Logs.duration &gt;= -10000000 (All_TPS_Logs.user=* OR NOT All_TPS_Logs.user=*) All_TPS_Logs.operationIdentity="*") All_TPS_Logs.name =*** GROUPBY _time, All_TPS_Logs.fullyQualifiedMethod span=1s 
| rename All_TPS_Logs.fullyQualifiedMethod as series 
| rename All_TPS_Logs.duration as value 
| table _time series value 
| search (series=**murex**) 
| lookup TPS_TAGS_HIGH_LEVEL Method#Class AS "series" OUTPUT TAG Threshold 
| where value &gt; Threshold 
| fillnull Threshold 
| fillnull TAG 
| eval TAG=if(TAG=0,"PLEASE_ADD_TAG",TAG) 
 | search NOT TAG = "PLEASE_ADD_TAG"

| timechart span=1h count(value) by TAG
| untable _time TAG value
| eval c_time=strftime(_time,"%F %T")
| xyseries TAG c_time value | rename TAG as _time
$time_token.earliest$
$time_token.latest$
1

20
none
none
false
false
false
true

and this is the new scripts. I have only change one line
var mySearch = splunkjs.mvc.Components.getInstance("myTableSearch1");

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

     var mySearch = splunkjs.mvc.Components.getInstance("myTableSearch1");
     var myResults = mySearch.data("results", {count:0});
     var allFields,filteredFields;
     myResults.on("data", function(){
         allFields=myResults.data().fields;
         filteredFields=allFields.filter(filterFields);
         console.log("Filtered Fields:",filteredFields);
     });
     function filterFields(field) {
         return field !== "_time";
     }
     // Row Coloring Example with custom, client-side range interpretation
     var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
         canRender: function(cell) {
             // Enable this custom cell renderer for all fields except _time
             return _(filteredFields).contains(cell.field);
         },
         render: function($td, cell) {
             // Add a class to the cell based on the returned value
             var value = parseInt(cell.value);

             // Apply interpretation based on count of errors per _time for each field
             if(cell.field !== "_time"){
                 if (value >= 5) {
                     $td.addClass("range-cell").addClass("range-severe");
                 }
                 else if (value >= 1 && value < 5) {
                     $td.addClass("range-cell").addClass("range-elevated");
                 }
                 else if (value < 1) {
                     $td.addClass("range-cell").addClass("range-low");
                 }
                 // Update the cell content
                 $td.text(value).addClass("numeric");
             }
         }
     });
     mvc.Components.get("highlight1").getVisualization(function(tableView) {
         // Add custom cell renderer, the table will re-render automatically.
         tableView.addCellRenderer(new CustomRangeRenderer());
     });

 });

Thanks in advance
Rob

Re: Add colors to a table for dynamic columns

niketn — Sat, 19 Jan 2019 20:43:07 GMT

Following is a run anywhere example I have created similar to data in the screenshot attached in the question. Seems to work fine for me. Can you add table command in your search to ensure that only required fields are present and no internal fields?

Have you checked Console log using Internet Browser's Inspector tool? See if there are any JavaScript Error. My JavaScript was logging list of fields from the search as array. You can check and validate that TAG field should not show up there. Also no fields other than 00:##:## should show up. You can add a console.log for parseInt() function as well to see whether it works.

Please try the following Simple XML Run anywhere Dashboard which creates a subset of your data:

<dashboard script="table_cell_highlight_all_fields.js">
  <label>Table with Color on all fields dynamically with excluded fields</label>
  <row>
    <panel>
      <html depends="$alwaysHideCSSStyle$">
        <style>
          #highlight td {
              background-color: #c1ffc3 !important;
          }
          */
          #highlight td.range-low {
              color: #C0D9D9;
          }
          #highlight td.range-elevated {
              background-color: #ffc57a !important;
              font-weight: bold;
          }
          #highlight td.range-severe {
              background-color: #d59392 !important;
              font-weight: bold;
          }
        </style>
      </html>
      <table id="highlight">
        <search id="myTableSearch">
          <query>| makeresults
| eval data="Limits Preview,0,0,5,82;Trade Insertion,1,56,0,0"
| makemv data delim=";"
| mvexpand data
| makemv data delim=","
| eval TAG=mvindex(data,0),"00:00:00"=mvindex(data,1),"00:01:00"=mvindex(data,2),"00:02:00"=mvindex(data,3),"00:03:00"=mvindex(data,4)
| table TAG "00:*"</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="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</dashboard>

Following is the code for table_cell_highlight_all_fields.js.js file

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

      var mySearch = splunkjs.mvc.Components.getInstance("myTableSearch");
      var myResults = mySearch.data("results", {count:0});
      var allFields,filteredFields;
      myResults.on("data", function(){
          allFields=myResults.data().fields;
          filteredFields=allFields.filter(filterFields);
          console.log("Filtered Fields:",filteredFields);
      });
      function filterFields(field) {
          return field !== "TAG";
      }
      // Row Coloring Example with custom, client-side range interpretation
      var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
          canRender: function(cell) {
              // Enable this custom cell renderer for all fields except TAG
              return _(filteredFields).contains(cell.field);
          },
          render: function($td, cell) {
              // Add a class to the cell based on the returned value
              var value = parseInt(cell.value);

              // Apply interpretation based on count of errors per TAG for each field
              if(cell.field !== "TAG"){
                  if (value >= 5) {
                      $td.addClass("range-cell").addClass("range-severe");
                  }
                  else if (value >= 1 && value < 5) {
                      $td.addClass("range-cell").addClass("range-elevated");
                  }
                  else if (value < 1) {
                      $td.addClass("range-cell").addClass("range-low");
                  }
                  // Update the cell content
                  $td.text(value).addClass("numeric");
              }
          }
      });
      mvc.Components.get("highlight").getVisualization(function(tableView) {
          // Add custom cell renderer, the table will re-render automatically.
          tableView.addCellRenderer(new CustomRangeRenderer());
      });  
  });

Re: Add colors to a table for dynamic columns

robertlynch2020 — Sun, 20 Jan 2019 12:06:57 GMT

Thank very much for this, i think i have found the issue.
If i use table it works, i was using | xyseries TAG c_time value (This does not work) .

However i need to use | xyseries TAG c_time value as the values i am producing are dynamic (Its time and a date[I have also now need the year and month etc..] so its changing all the time) so unless i can use the table command and sat TAG and everything..not sure that is possible.

However this is not the end of the world if i use _time that table does not look so bad..

So again thanks for you help you have been very very good 🙂

Re: Add colors to a table for dynamic columns

niketn — Sun, 20 Jan 2019 17:21:53 GMT

I am not sure why xyseries would not work. How about trying the following?

| table TAG *

Hope your issue gets resolved. If the comments have helped do upvote answer/comment 🙂

Re: Add colors to a table for dynamic columns

marxsabandana — Tue, 04 Jun 2019 09:13:37 GMT

Hi @niketnilay

I have the same problem, but is it possible to only do it through the use of tokens instead of going through JavaScript? I already tried using this:

<done>
            <condition match="'job.resultCount' &gt; 0">
                        <set token="Date">$result.date$</set>
            </condition>
</done>

It captures the result of the "date" field from my search query, which serves as table columns. Then the $Date$ token should be the field name of formatting the table:

<format type="color" field="$Date$">
          <colorPalette type="list">[#BF3939,#36B1C1,#414A75,#414A75]</colorPalette>
          <scale type="threshold">80,99,100</scale>
</format>

My desired output should have different color ranges depending on each table cell value.

Re: Add colors to a table for dynamic columns

niketn — Tue, 04 Jun 2019 11:19:39 GMT

@marxsabandana I will try and see if this is possible. However, I doubt that when token changes visualization will be reset the formatting. If you know what your field names are going to be then Simple XML would be sufficient, otherwise JS is the better and more dynamic way.

Re: Add colors to a table for dynamic columns

kaeleyt — Fri, 29 May 2020 17:54:18 GMT

A little late to this but you could also try using the following for each table:

<format type="color">
          <colorPalette type="map">{"0":#DC4E41,"1":#53A051}</colorPalette>
</format>

Taking the "field=" specification off seems to apply it to the entire row by default.

Hope this helps!

Re: Add colors to a table for dynamic columns

black_bagel — Wed, 27 Jul 2022 12:22:27 GMT

Wow this made my life so much easier! Thank you @kaeleyt 😍