topic Re: Highlight row if unique values exist within dynamic pivot table in Dashboards & Visualizations

Highlight row if unique values exist within dynamic pivot table

shakSplunk — Thu, 22 Jul 2021 15:34:25 GMT

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

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

E.g. 2

CartridgeType	Cartridge	E:::MCAS1	E:::MCAS2	D:::MCAS1	D:::MCAS2
user	etf	4	4	4	4
product	brd	4	4	5	5

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.

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

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.

Re: Highlight row if unique values exist within dynamic pivot table

ITWhisperer — Thu, 22 Jul 2021 15:56:53 GMT

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

Re: Highlight row if unique values exist within dynamic pivot table

shakSplunk — Tue, 27 Jul 2021 02:10:29 GMT

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-the-other/m-p/521927/highlight/true#M147153) 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,"|")

Re: Highlight row if unique values exist within dynamic pivot table

ITWhisperer — Tue, 27 Jul 2021 06:19:21 GMT

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>

Re: Highlight row if unique values exist within dynamic pivot table

shakSplunk — Tue, 27 Jul 2021 06:34:37 GMT

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.

Re: Highlight row if unique values exist within dynamic pivot table

ITWhisperer — Tue, 27 Jul 2021 06:48:07 GMT

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?

Re: Highlight row if unique values exist within dynamic pivot table

shakSplunk — Tue, 27 Jul 2021 06:57:57 GMT

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

<row> <panel> <table> <search> <query>| pivot (command) | fields - _* linecount | foreach * [| eval value=if("<<FIELD>>"=="CartridgeType" OR "<<FIELD>>"="CartridgeName", 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</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?

Re: Highlight row if unique values exist within dynamic pivot table

ITWhisperer — Tue, 27 Jul 2021 07:07:36 GMT

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("<<FIELD>>"=="CartridgeType" OR "<<FIELD>>"="CartridgeName", 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</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>

Re: Highlight row if unique values exist within dynamic pivot table

shakSplunk — Mon, 16 Aug 2021 14:25:19 GMT

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:

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("<<FIELD>>"=="CartridgeType" OR "<<FIELD>>"="CartridgeName", 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

Any help would be greatly appreciated!

Re: Highlight row if unique values exist within dynamic pivot table

ITWhisperer — Mon, 16 Aug 2021 15:22:09 GMT

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.