topic Re: Cell Coloring of a Dynamic Column in Dashboards & Visualizations

Cell Coloring of a Dynamic Column

kabiraj — Sun, 07 Jun 2020 02:23:02 GMT

How to highlight cells of fields which are dynamic? My table looks like below if you select last 3 days from timepicker

Channel 24-Jun-15 23-Jun-15 22-Jun-15 21-Jun-15
BBC Sport 6 3 7 9
Discovery 2 6 4 3

As you can see the fields names here are time dependent. I want to color all cells for fields other than 'Channel' here.
How can I do that? Please help.

Re: Cell Coloring of a Dynamic Column

vganjare — Wed, 24 Jun 2015 09:14:26 GMT

Do you want to highlight all the cells except Channel cell?

Re: Cell Coloring of a Dynamic Column

kabiraj — Wed, 24 Jun 2015 09:16:10 GMT

Yes. And other cells belong to fields which are time dependent. Based on the threshold ranges i will color the cells for all fields other than 'Channel'

Re: Cell Coloring of a Dynamic Column

vganjare — Mon, 28 Sep 2020 20:20:58 GMT

In this case, you can mark the field which should not be highlighted. e.g. | eval Channel="NO_HIGHLIGHT_".Channel ... In the cell renderer, see if the field value has string token : "NO_HIGHLIGHT_". If this token is present, then do not highlight the cell, and remove the NO_HIGHLIGHT token from the text. If the token is not present, then highlight the cell.

Thanks!!

Re: Cell Coloring of a Dynamic Column

kabiraj — Wed, 24 Jun 2015 09:24:45 GMT

Can you give an example to explain what you said? Below is my javascript

require([
    'underscore',
    'jquery',
    'splunkjs/mvc',
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/simplexml/ready!'
], function(_, $, mvc, TableView) {
     // Row Coloring Example with custom, client-side range interpretation
    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function(cell) {
            // Enable this custom cell renderer for both the active_hist_searches and the active_realtime_searches field
            return true;
        },
        render: function($td, cell) {
            // Add a class to the cell based on the returned value
            // Apply interpretation for number of historical searches
                var tdPosition = $td.parent().children().index($td[0]);
                var value = cell.value;
            if (tdPosition > 0) {
                    var value = parseInt(value);
                if ((value > 0) && (value <= 50)) {
                    $td.addClass('range-cell').addClass('range-severe');
                }
                else if ((value > 50) && (value <= 100)) {
                    $td.addClass('range-cell').addClass('range-elevated');
                }
                else if ((value > 100) && (value <= 150)) {
                    $td.addClass('range-cell').addClass('range-low');
                }
                    else {
                        $td.addClass('range-cell').addClass('range-empty');
                    }

            }
            // Update the cell content
                $td.text(value).addClass('numeric');
        }
    });
    mvc.Components.get('samptable').getVisualization(function(tableView) {
        // Add custom cell renderer
        tableView.table.addCellRenderer(new CustomRangeRenderer());
        // tableView.on('rendered', 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).addClass(this.className);
           // });
        //});
        // Force the table to re-render
        tableView.table.render();
    });
});

Re: Cell Coloring of a Dynamic Column

vganjare — Mon, 28 Sep 2020 20:21:01 GMT

Following are the change:
1. Change the splunk query. The columns which do not need highlighting, use the above eval statement.
2. In JS, you get the cell value using var value = cell.value;, using this value, identify if the column needs highlighting or not. e.g. If the cell value is NO_HIGHLIGHT_BBC Sport, then don't highlight. If not present, then highlight. Here, NO_HIGHLIGHT_ is the token we added (purposefully) in the search result (for column Channel).
3. Lastly, remove NO_HIGHLIGHT_ from the cell value (and show this value in dashboard).

Thanks!!

Re: Cell Coloring of a Dynamic Column

kabiraj — Wed, 24 Jun 2015 09:41:19 GMT

I understood your point. Understood the eval condition. But i am confused about how to write that condition in js file. It would be great if you share a small example of the js file.

Also i am using return=true. previously it was return _(['Fieldname']).contains(cell.field); Is it correct. As I am new to javascript, so if you could share a similar example it would be better for me to understand.

Re: Cell Coloring of a Dynamic Column

vganjare — Wed, 24 Jun 2015 09:51:07 GMT

See if this helps. Replace the update the render function to following. The varible isHighlight will be true if the highlight is required otherwise it will be false.

render: function($td, cell) {
    // Add a class to the cell based on the returned value
    // Apply interpretation for number of historical searches
    var tdPosition = $td.parent().children().index($td[0]);
    var value = cell.value;
    var isHighlight = (value.search('NO_HIGHLIGHT_') == -1)
    if (isHighlight) {
        var value = parseInt(value);
        if ((value > 0) && (value <= 50)) {
            $td.addClass('range-cell').addClass('range-severe');
        }else if ((value > 50) && (value <= 100)) {
            $td.addClass('range-cell').addClass('range-elevated');
        }else if ((value > 100) && (value <= 150)) {
            $td.addClass('range-cell').addClass('range-low');
        }else {
            $td.addClass('range-cell').addClass('range-empty');
        }
    }
    value = value.replace('NO_HIGHLIGHT_','');
    // Update the cell content
    $td.text(value).addClass('numeric');
}

Re: Cell Coloring of a Dynamic Column

kabiraj — Wed, 24 Jun 2015 11:58:14 GMT

Hi vganjare. I tried your solution but the table shows empty if i apply the javascript and css to the dashboard.

Splunk Query

sourcetype=ret | rex "popularityIndex=(\")(?P<PI>[^>]+)(\")" | search PI=* | rex "<!--(?P<Channel_Name>[^-]+)" | eval Channel_Name = substr(Channel_Name, 0, len(Channel_Name)-18) | dedup Channel_Name | table Channel_Name PI| rename PI as "Popularity Index" Channel_Name as "Channel" | sort "Popularity Index" | appendcols [search sourcetype=shmapplogs "getMS3SAS ended for - deviceId" | bucket span=1d _time | stats count by _time channelId | sort count desc | lookup youview_channels.csv service_id_truncated AS channelId OUTPUT channel_name_letter | streamstats count AS position by _time | fields channel_name_letter position _time | convert timeformat="%d-%b-%Y" ctime(_time) As Time | chart max(position) over channel_name_letter by Time | rename channel_name_letter as "Channel"] | eval Channel = "NO_HIGHLIGHT_"+Channel

Javascript

require([
    'underscore',
    'jquery',
    'splunkjs/mvc',
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/simplexml/ready!'
], function(_, $, mvc, TableView) {
     // Row Coloring Example with custom, client-side range interpretation
    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function(cell) {
            // Enable this custom cell renderer for both the active_hist_searches and the active_realtime_searches field
            return true;
        },
        render: function($td, cell) {
            // Add a class to the cell based on the returned value
            // Apply interpretation for number of historical searches
                //var tdPosition = $td.parent().children().index($td[0]);
                var value = cell.value;
                var isHighlight = (value.search('NO_HIGHLIGHT_') == -1);
                //console.log(tdposition + value);
                //alert(tdposition + value);
            if (isHighlight) {
                    var value = parseInt(value);
                if ((value > 0) && (value <= 50)) {
                    $td.addClass('range-cell').addClass('range-severe');
                }
                else if ((value > 50) && (value <= 100)) {
                    $td.addClass('range-cell').addClass('range-elevated');
                }
                else if ((value > 100) && (value <= 150)) {
                    $td.addClass('range-cell').addClass('range-low');
                }
                    else {
                        $td.addClass('range-cell').addClass('range-empty');
                    }

            }
                else {
                value = value.replace('NO_HIGHLIGHT_','');              
                }
            // Update the cell content
                $td.text(value);
        }
    });
    mvc.Components.get('rettable').getVisualization(function(tableView) {
        // Add custom cell renderer
        tableView.table.addCellRenderer(new CustomRangeRenderer());
        // tableView.on('rendered', 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).addClass(this.className);
           // });
        //});
        // Force the table to re-render
        tableView.table.render();
    });
});

CSS

#rettable td.range-low {
    color: #C0D9D9;
}
#rettable td.range-elevated {
    background-color: #ffc57a !important;
    font-weight: bold;
}
#rettable td.range-severe {
    background-color: #d59392 !important;
    font-weight: bold;
}

#rettable td.range-empty {
    color: #C0D9D9;
}

Source Code

<panel>
  <table id="rettable">
    <title>RET Statistics</title>
    <search>
      <query>sourcetype=ret | rex "popularityIndex=(\")(?P&lt;PI&gt;[^&gt;]+)(\")" | search PI=* | rex "&lt;!--(?P&lt;Channel_Name&gt;[^-]+)" | eval Channel_Name = substr(Channel_Name, 0, len(Channel_Name)-18) | dedup Channel_Name | table Channel_Name PI| rename PI as "Popularity Index" Channel_Name as "Channel" | sort "Popularity Index" | appendcols [search sourcetype=shmapplogs "getMS3SAS ended for - deviceId" | bucket span=1d _time | stats count by _time channelId | sort count desc | lookup youview_channels.csv service_id_truncated AS channelId OUTPUT channel_name_letter | streamstats count AS position by _time | fields channel_name_letter position _time | convert timeformat="%d-%b-%Y" ctime(_time) As Time | chart max(position) over channel_name_letter by Time | rename channel_name_letter as "Channel"] | eval Channel = "NO_HIGHLIGHT_"+Channel</query>
      <earliest>$time.earliest$</earliest>
      <latest>$time.latest$</latest>
    </search>
    <option name="wrap">undefined</option>
    <option name="rowNumbers">undefined</option>
    <option name="drilldown">row</option>
    <option name="dataOverlayMode">none</option>
    <option name="count">10</option>
  </table>
</panel>

Re: Cell Coloring of a Dynamic Column

kabiraj — Wed, 24 Jun 2015 12:41:08 GMT

Thank you vganjare. It works now!

Re: Cell Coloring of a Dynamic Column

richielynch89 — Thu, 28 Jun 2018 17:38:05 GMT

Hi, I noticed in your 2nd last comment on this thread that you said vganjare's solution did not work and then right after you said it did work. Did you do any more to your most recent code to make it work. I was thinking that maybe I missed something between your 2nd last comment and you rlast comment?

Re: Cell Coloring of a Dynamic Column

PowerPacked — Mon, 27 Apr 2020 06:21:19 GMT

Is it possible to do the same for string values in fields instead of numeric.

Am trying this but not working any help provided is appreciated.

if (cell.field !== "source_subnet") {
                  if (value !== "100%") {
                      $td.addClass("range-cell").addClass("range-severe");
                  }
                  // Update the cell content
                  $td.text(value).addClass('string');
              }

Please ignore am using "100%" as string.

Thanks