Splunk Search

Add colors to a table for dynamic columns

robertlynch2020
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
1 Solution

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

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

View solution in original post

0 Karma

kaeleyt
Explorer

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!

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

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

View solution in original post

0 Karma

marxsabandana
Path Finder

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.

niketnilay
Legend

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

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

robertlynch2020
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

niketnilay
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);
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

robertlynch2020
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

robertlynch2020
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

niketnilay
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());
      });  
  });
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

robertlynch2020
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

niketnilay
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 🙂

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

robertlynch2020
Motivator

Great and thanks

Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!