on Simple XML with colorPalette to select the colo...

jenniferhao · ‎04-06-2021

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?

jenniferhao · ‎04-07-2021

<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.

ITWhisperer · ‎04-07-2021

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">

ITWhisperer · ‎04-06-2021

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('&lt;&lt;FIELD&gt;&gt;',0)) - tonumber(mvindex('&lt;&lt;FIELD&gt;&gt;', 1)))
  | eval sign = sign / abs(sign)
  | fillnull value=0 sign
  | eval sign=if(sign &lt; 0, "RED", if(sign &gt; 0, "GREEN", "YELLOW"))
  | eval &lt;&lt;FIELD&gt;&gt;=mvappend(mvindex('&lt;&lt;FIELD&gt;&gt;', 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>

jenniferhao · ‎04-06-2021

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.

ITWhisperer · ‎04-06-2021

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.

jenniferhao · ‎04-06-2021

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>

ITWhisperer · ‎04-06-2021

#tableRowColorWithoutJS

Needs to be changed to the id of your table

#tableWitColor

So that the style is applied to your table

jenniferhao · ‎04-06-2021

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

ITWhisperer · ‎04-06-2021

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

jenniferhao · ‎04-09-2021

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.

ITWhisperer · ‎04-11-2021

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>

ITWhisperer · ‎04-09-2021

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.

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

table

Introducing the 2024 SplunkTrust!

Introducing the 2024 Splunk MVPs!

Splunk Custom Visualizations App End of Life