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?
<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.
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">
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>
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.
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.
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>
#tableRowColorWithoutJS
Needs to be changed to the id of your table
#tableWitColor
So that the style is applied to your table
A good catch. But it doesn't work. The "RED" still shows up.
Can you post your actual SimpleXML as the example you posted with the correction for the table id works for me?
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.
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>
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.