Splunk Search
Highlighted

Add colors to a table for dynamic columns

Motivator

Hi

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 ?

alt text

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

Labels (1)
0 Karma
Highlighted

Re: Add colors to a table for dynamic columns

Legend

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




| eval message="Happy Splunking!!!"


View solution in original post

0 Karma
Highlighted

Re: Add colors to a table for dynamic columns

Motivator

Great and thanks

Highlighted

Re: Add colors to a table for dynamic columns

Motivator

Hi

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>
0 Karma
Highlighted

Re: Add colors to a table for dynamic columns

Legend

@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);



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Add colors to a table for dynamic columns

Motivator

Hi

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());
     });

 });
0 Karma
Highlighted

Re: Add colors to a table for dynamic columns

Motivator

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

0 Karma
Highlighted

Re: Add colors to a table for dynamic columns

Legend

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());
      });  
  });



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Add colors to a table for dynamic columns

Motivator

Hi

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 🙂

0 Karma
Highlighted

Re: Add colors to a table for dynamic columns

Legend

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 🙂




| eval message="Happy Splunking!!!"


0 Karma