Splunk Search

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

jenniferhao
Explorer

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

jenniferhao_0-1617737591436.png

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? 

 

Labels (1)
0 Karma

jenniferhao
Explorer

<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 &gt; pre1,mvappend(post1,"RED"),post1)
| eval post2=if( post2 &gt; 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.

jenniferhao_0-1617805169341.png

 

0 Karma

ITWhisperer
Ultra Champion

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

 

0 Karma

ITWhisperer
Ultra Champion

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>

 

0 Karma

jenniferhao
Explorer

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.

0 Karma

ITWhisperer
Ultra Champion

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.

0 Karma

jenniferhao
Explorer

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 &gt; pre1,mvappend(post1,"RED"),post1)
| eval post2=if( post2 &gt; 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>

jenniferhao_0-1617746867114.png

 

0 Karma

ITWhisperer
Ultra Champion
#tableRowColorWithoutJS

Needs to be changed to the id of your table

#tableWitColor

So that the style is applied to your table

0 Karma

jenniferhao
Explorer

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

0 Karma

ITWhisperer
Ultra Champion

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

0 Karma

jenniferhao
Explorer

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.

0 Karma

ITWhisperer
Ultra Champion

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>
0 Karma

ITWhisperer
Ultra Champion

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.

0 Karma