topic Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers in Splunk Search

on Simple XML with colorPalette to select the color by comparing 2 fields numbers

jenniferhao — Tue, 06 Apr 2021 19:37:27 GMT

Is there a way to get field's background color by compare with 2 fields numbers? for example:

If "POST IPTV CALLS"'s value > "PRE IPTV CALLS"'s value, then its background become in red.

As I know JS can do it, but somehow we need to keep the dashboard as Simple XML. Any idea?

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

ITWhisperer — Mon, 12 Apr 2021 07:58:54 GMT

Here is an example of how you might do it. Essentially, you convert your fields to multi-value fields with the second element being an indicator of the colour you want. Then you use the colour palette to set the colour and you use CSS style to hide the second element of the multi-value field.

<panel depends="$stayhidden$"> <html> <div/> <style> #tableCellColourWithoutJS table tbody td div.multivalue-subcell[data-mv-index="1"]{ display: none; } </style> </html> </panel> <panel> <table id="tableCellColourWithoutJS"> <title>Colour Cell by Previous</title> <search> <query>| makeresults | eval _raw="Messages Nov_20 Dec_20 Jan_21 Feb_21 Messge 0 0 1 0 0 Messge 1 1 3 1 1 Messge 2 11 0 0 0 Messge 3 1 0 0 0 Messge 4 9 5 0 0 Messge 5 1 1 0 0 Messge 6 1 1 0 0 Messge 7 0 1 0 0" | multikv forceheader=1 | fields - _raw _time linecount | fields - _mkv* | transpose 0 header_field=Messages column_name=Month | eval Month=strptime(Month . "_01", "%b_%y_%d") | sort - Month | autoregress Month as next p=1 | eval months=mvappend(Month, next) | fields - next | mvexpand months | stats list(*) as * by months | where months != "months" | foreach * [ eval sign=(tonumber(mvindex('<<FIELD>>',0)) - tonumber(mvindex('<<FIELD>>', 1))) | eval sign = sign / abs(sign) | fillnull value=0 sign | eval sign=if(sign < 0, "RED", if(sign > 0, "GREEN", "YELLOW")) | eval <<FIELD>>=mvappend(mvindex('<<FIELD>>', 0),sign) | fields - sign ] | fields - months | eval Month=mvindex(Month,0) | sort Month | eval Month=strftime(Month, "%b %y") | transpose 0 header_field=Month column_name=Messages</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="refresh.display">progressbar</option> <option name="rowNumbers">false</option> <option name="totalsRow">false</option> <option name="wrap">true</option> <format type="color"> <colorPalette type="expression">case (match(value,"RED"), "#ff0000",match(value,"YELLOW"), "#ffff00",match(value,"GREEN"),"#00ff00",true(),"#ffffff")</colorPalette> </format> </table> </panel>

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

jenniferhao — Tue, 06 Apr 2021 21:07:39 GMT

This is a very smart idea. But it's not what I want. I just want to post columns have red/green colors, but not compare every column. And the colors names cannot show up with the data.

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

ITWhisperer — Tue, 06 Apr 2021 21:16:42 GMT

You don't need to compare all the columns (foreach), you can just check the column you want

| eval 'POST IPTV CALLS'=if('POST IPTV CALLS'>'PRE IPTV CALLS',mvappend('POST IPTV CALLS',"RED"),'POST IPTV CALLS')

The display:none; for data-mv-index="1" stops the colour being shown.

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

jenniferhao — Tue, 06 Apr 2021 22:08:40 GMT

The display:none; for data-mv-index="1" doesn't stop the color name being shown. See below:

<dashboard>
<row>
<panel>
<title>Only SimpleXML no JS required</title>
<html>
<style>
#tableRowColorWithoutJS table tbody td div.multivalue-subcell[data-mv-index="1"]{
display: none;
}
</style>
</html>
<table id="tableWitColor">
<title>Table with Value and Color Combined (With Simple XML Color Palette Expression)</title>
<search>
<query>| makeresults
| eval data= "C1 1 2 10 20;C2 3 2 5 4;C3 5 4 5 3;C4 6 4 8 2;"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval Client=mvindex(data,0), pre1=mvindex(data,1), post1=mvindex(data,2), pre2=mvindex(data,3), post2=mvindex(data,4)
| fields - _time data
| eval post1=if( post1 > pre1,mvappend(post1,"RED"),post1)
| eval post2=if( post2 > pre2,mvappend(post2,"RED"),post2)
|table pre1 post1 pre2 post2
</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">10</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>
<format type="color" field="post1">
<colorPalette type="expression">if(match(value,"RED"),"#FF0000", "#65A637")</colorPalette>
</format>
<format type="color" field="post2">
<colorPalette type="expression">if(match(value,"RED"),"#FF0000","#65A637")</colorPalette>
</format>
</table>
</panel>
</row>
</dashboard>

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

ITWhisperer — Tue, 06 Apr 2021 22:34:25 GMT

#tableRowColorWithoutJS

Needs to be changed to the id of your table

#tableWitColor

So that the style is applied to your table

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

jenniferhao — Wed, 07 Apr 2021 02:12:51 GMT

A good catch. But it doesn't work. The "RED" still shows up.

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

ITWhisperer — Wed, 07 Apr 2021 05:38:27 GMT

Can you post your actual SimpleXML as the example you posted with the correction for the table id works for me?

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

jenniferhao — Wed, 07 Apr 2021 14:19:39 GMT

<dashboard>
<row>
<panel>
<title>Only SimpleXML no JS required</title>
<html>
<style>
#tableWitColor table tbody td div.multivalue-subcell[data-mv-index="1"]{
display: none;
}
</style>
</html>
<table id="tableWitColor">
<title>Table with Value and Color Combined (With Simple XML Color Palette Expression)</title>
<search>
<query>| makeresults
| eval data= "C1 1 2 10 20;C2 3 2 5 4;C3 5 4 5 3;C4 6 4 8 2;"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval Client=mvindex(data,0), pre1=mvindex(data,1), post1=mvindex(data,2), pre2=mvindex(data,3), post2=mvindex(data,4)
| fields - _time data
| eval post1=if( post1 > pre1,mvappend(post1,"RED"),post1)
| eval post2=if( post2 > pre2,mvappend(post2,"RED"),post2)
|table pre1 post1 pre2 post2
</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">10</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>
<format type="color" field="post1">
<colorPalette type="expression">if(match(value,"RED"),"#FF0000", "#65A637")</colorPalette>
</format>
<format type="color" field="post2">
<colorPalette type="expression">if(match(value,"RED"),"#FF0000","#65A637")</colorPalette>
</format>
</table>
</panel>
</row>
</dashboard>

You can see the "RED" still on the table.

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

ITWhisperer — Mon, 12 Apr 2021 07:57:46 GMT

The html with the style needs to be in its own panel (which you can hide with depends)

<dashboard> <row> <panel depends="$stayhidden$"> <html> <div/> <style> #tableWitColor td div.multivalue-subcell[data-mv-index="1"]{ display: none; } </style> </html> </panel> <panel> <table id="tableWitColor">

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

jenniferhao — Fri, 09 Apr 2021 21:05:20 GMT

I tested same above code on different browsers and got same result. But when I tested with different Splunk versions and got the different results. Tested it on V7.3.3 and V8.1.2. The results are wrong which display the number with "RED". ONLY on Splunk V8.1.3. I got the correct result.

Thank for help.

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

ITWhisperer — Fri, 09 Apr 2021 21:16:06 GMT

Interesting. I am working on 8.0.3 and 8.1.3 and it works there, although to be fair, the styles are slightly different.

Re: on Simple XML with colorPalette to select the color by comparing 2 fields numbers

ITWhisperer — Mon, 12 Apr 2021 07:56:38 GMT

Hi @jenniferhao

It looks like 7.3.3 handles the hidden html slightly differently, so just add an empty div block to the html so it gets passed for rendering

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