Dashboards & Visualizations

Highlight row if unique values exist within dynamic pivot table

shakSplunk
Path Finder

Hi all, 

I have a pivot that changes the number of columns based on a drop-down selection.  The first two columns remain consistent however the remaining columns can change (1st e.g. has 6 additional columns with auto-generated column names whereas 2nd has 4 additional columns).

E.g. 1

CartridgeTypeCartridgeE:::MCAS1E:::MCAS2S:::MCAS1S:::MCAS2S:::MCAS3S:::MCAS4
useretf444444
productbrd445555

 

E.g. 2

CartridgeTypeCartridgeE:::MCAS1E:::MCAS2D:::MCAS1D:::MCAS2
useretf4444
productbrd4455

 

Is it possible (purely through the html or css AKA through the 'Source' button when editing a dashboard) to highlight rows red if they have different values along a row in the dynamically generated columns? For instance, in e.g. 1 since the second row's mcas columns don't all have the same value, highlight the whole row.

CartridgeTypeCartridgeE:::MCAS1E:::MCAS2S:::MCAS1S:::MCAS2S:::MCAS3S:::MCAS4
useretf444444
productbrd445555

 

The confusion I'm having here is due to

i ) non-static column names

ii) Number of columns can change in quantity based on the dropdown selection.

 

Any help would be hugely appreciated! I've tried looking at the sample dashboard however haven't been able to figure out a solution based of them and an unable to implement a js option.

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

The style should be in a separate html panel (which you can hide with a depends attribute) - the table (or enclosing panel) you want to format should have a matching id

  <row>
    <panel depends="$alwayshide$">
      <html>
        <style>
          #cartridges table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
    </panel>
    <panel>
      <table id="cartridges">
        <search>
          <query>| pivot (command)
| fields - _* linecount
| foreach *
    [| eval value=if("&lt;&lt;FIELD&gt;&gt;"=="CartridgeType" OR "&lt;&lt;FIELD&gt;&gt;"="CartridgeName", value, if(isnull(value),'&lt;&lt;FIELD&gt;&gt;',mvappend(value,'&lt;&lt;FIELD&gt;&gt;')))]
| eval value=mvdedup(value)
| foreach *
    [| eval &lt;&lt;FIELD&gt;&gt;=if("&lt;&lt;FIELD&gt;&gt;"="value", '&lt;&lt;FIELD&gt;&gt;', if(mvcount(value)=1,'&lt;&lt;FIELD&gt;&gt;',mvappend('&lt;&lt;FIELD&gt;&gt;',"RED")))]
| fields - value</query>
          <earliest>1614999151.000</earliest>
          <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">true</option>
        <option name="totalsRow">false</option>
        <option name="wrap">false</option>
        <format type="color">
          <colorPalette type="expression">case (match(value,"RED"), "rgb(255,0,0)")</colorPalette>
        </format>
      </table>
    </panel>
  </row>

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Build on the answer to the previous version of this question, you could use a multi-value field (populated by a foreach command) and mvdedup and mvcount to determine if the row contains differences

| makeresults
| eval _raw="CartridgeType	Cartridge	E:::MCAS1	E:::MCAS2	S:::MCAS1	S:::MCAS2	S:::MCAS3	S:::MCAS4
user	etf	4	4	4	4	4	4
product	brd	4	4	5	5	5	5"
| multikv forceheader=1
| fields - _* linecount



| foreach *
    [| eval value=if("<<FIELD>>"=="Cartridge" OR "<<FIELD>>"="CartridgeType", value, if(isnull(value),'<<FIELD>>',mvappend(value,'<<FIELD>>')))]
| eval value=mvdedup(value)
| foreach *
    [| eval <<FIELD>>=if("<<FIELD>>"="value", '<<FIELD>>', if(mvcount(value)=1,'<<FIELD>>',mvappend('<<FIELD>>',"RED")))]
| fields - value
0 Karma

shakSplunk
Path Finder

It works! Thank you so much! It adds the string "RED" to the row with the differences. However, would it be possible to automatically highlight it red instead of adding a string? @ITWhisperer 

I had a look at this question (https://community.splunk.com/t5/Splunk-Search/How-to-add-a-color-to-the-field-in-one-column-based-on...) however I understand how to apply to one column, however how to apply to all columns? In the linked answer, one specific field is being selected 

--- your query
| eval service=service."|".status
| eval service=split(service,"|")
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Have you added the CSS to hide the second mv value (i.e. the "RED" string)?

Have you added format with colorPalette to colour the cells - in your case you would want it to apply to all fields so it would be something like this

        <format type="color">
          <colorPalette type="expression">case (match(value,"RED"), "rgb(255,0,0)")</colorPalette>
        </format>

shakSplunk
Path Finder

The coloring is working (it is highlighting red), however I'm having trouble with removing the "RED" multi value. 

When I try to add the following:

<html>
        <style>
          #cartridges table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
      <table id="cartridges">

I'm assuming that this gets the first index of the multivalue cells, however its giving me an unexpected close tag error.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Indexing is from zero (0) in this case so this is stopping the second mv from being displayed which should be the "RED" string where it appears. If you are getting an unexpected close tag error, does it highlight where this error is in the SimpleXML?

0 Karma

shakSplunk
Path Finder

The error is highlighting the "</panel>" (second last line) 

   <row>
	<panel>
      <table>
        <search>
          <query>| pivot (command)
| fields - _* linecount
| foreach *
    [| eval value=if("&lt;&lt;FIELD&gt;&gt;"=="CartridgeType" OR "&lt;&lt;FIELD&gt;&gt;"="CartridgeName", value, if(isnull(value),'&lt;&lt;FIELD&gt;&gt;',mvappend(value,'&lt;&lt;FIELD&gt;&gt;')))]
| eval value=mvdedup(value)
| foreach *
    [| eval &lt;&lt;FIELD&gt;&gt;=if("&lt;&lt;FIELD&gt;&gt;"="value", '&lt;&lt;FIELD&gt;&gt;', if(mvcount(value)=1,'&lt;&lt;FIELD&gt;&gt;',mvappend('&lt;&lt;FIELD&gt;&gt;',"RED")))]
| fields - value</query>
          <earliest>1614999151.000</earliest>
          <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">true</option>
        <option name="totalsRow">false</option>
        <option name="wrap">false</option>
        <html>
        <style>
          #cartridges table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
      <table id="cartridges">
        <format type="color">
          <colorPalette type="expression">case (match(value,"RED"), "rgb(255,0,0)")</colorPalette>
        </format>
      </table>
    </panel>
</row>

is the area that I'm putting the multivalue remover incorrect?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The style should be in a separate html panel (which you can hide with a depends attribute) - the table (or enclosing panel) you want to format should have a matching id

  <row>
    <panel depends="$alwayshide$">
      <html>
        <style>
          #cartridges table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
    </panel>
    <panel>
      <table id="cartridges">
        <search>
          <query>| pivot (command)
| fields - _* linecount
| foreach *
    [| eval value=if("&lt;&lt;FIELD&gt;&gt;"=="CartridgeType" OR "&lt;&lt;FIELD&gt;&gt;"="CartridgeName", value, if(isnull(value),'&lt;&lt;FIELD&gt;&gt;',mvappend(value,'&lt;&lt;FIELD&gt;&gt;')))]
| eval value=mvdedup(value)
| foreach *
    [| eval &lt;&lt;FIELD&gt;&gt;=if("&lt;&lt;FIELD&gt;&gt;"="value", '&lt;&lt;FIELD&gt;&gt;', if(mvcount(value)=1,'&lt;&lt;FIELD&gt;&gt;',mvappend('&lt;&lt;FIELD&gt;&gt;',"RED")))]
| fields - value</query>
          <earliest>1614999151.000</earliest>
          <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">true</option>
        <option name="totalsRow">false</option>
        <option name="wrap">false</option>
        <format type="color">
          <colorPalette type="expression">case (match(value,"RED"), "rgb(255,0,0)")</colorPalette>
        </format>
      </table>
    </panel>
  </row>

View solution in original post

shakSplunk
Path Finder

Hi @ITWhisperer 

This solution was working a few weeks ago, however I altered the query to create the table from the search function instead of the pivot table function , and now its not working.

New Query:

| makeresults | eval _raw="Environment,Application,CartridgeType,Cartridge,Version
SVP,A-1,User,Alpha,1.4
SVP,A-1,Product,Beta,1.5
SVP,A-1,System,Sigma,1.5
SVP,A-2,User,Alpha,1.4
SVP,A-2,Product,Beta,1.5
SVP,A-2,System,Sigma,1.5
SVP,A-3,User,Alpha,1.4
SVP,A-3,Product,Beta,1.4
SVP,A-3,System,Sigma,1.5
" | multikv forceheader=1
| table Environment, Application, CartridgeType, Cartridge, Version
| eval {Environment}:{Application}=Version
| fields - Environment, Application, Version
| stats values by CartridgeType, Cartridge
| rename values(*) as *

Adding the "RED" insertion by combining previous answer:

| makeresults | eval _raw="Environment,Application,CartridgeType,Cartridge,Version
SVP,A-1,User,Alpha,1.4
SVP,A-1,Product,Beta,1.5
SVP,A-1,System,Sigma,1.5
SVP,A-2,User,Alpha,1.4
SVP,A-2,Product,Beta,1.5
SVP,A-2,System,Sigma,1.5
SVP,A-3,User,Alpha,1.4
SVP,A-3,Product,Beta,1.4
SVP,A-3,System,Sigma,1.5
" | multikv forceheader=1
| table Environment, Application, CartridgeType, Cartridge, Version
| eval {Environment}:{Application}=Version
| fields - Environment, Application, Version
| stats values by CartridgeType, Cartridge
| rename values(*) as *
| fields - _* linecount
| foreach *
    [| eval value=if("&lt;&lt;FIELD&gt;&gt;"=="CartridgeType" OR "&lt;&lt;FIELD&gt;&gt;"="CartridgeName", value, if(isnull(value),'&lt;&lt;FIELD&gt;&gt;',mvappend(value,'&lt;&lt;FIELD&gt;&gt;')))]
| eval value=mvdedup(value)
| foreach *
    [| eval &lt;&lt;FIELD&gt;&gt;=if("&lt;&lt;FIELD&gt;&gt;"="value", '&lt;&lt;FIELD&gt;&gt;', if(mvcount(value)=1,'&lt;&lt;FIELD&gt;&gt;',mvappend('&lt;&lt;FIELD&gt;&gt;',"RED")))]
| fields - value

 Any help would be greatly appreciated!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You have changed back to Cartridge rather than CartridgeName but you haven't changed the first foreach to take this into account, so the name ends up being included in the list of values and therefore always more than one value in the list.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!