Dashboards & Visualizations

How to make a report panel in a Splunk Dashboard that shows the difference between two fields?

revanthammineni
Path Finder

Hi Splunkers,

I’m working on a report panel in a dashboard where I need to show the difference of two fields in colors. Any one help me in doing it in a right way using xml? 

For example:

Nov       Feb

10           5

20           10

30            40

If it’s less in Feb then it should show as green, if not, it should be in red. How can I do this?

can we do this using xml instead of Javascript?

Labels (1)
Tags (3)
0 Karma
1 Solution

yeahnah
Motivator

Hi @revanthammineni 

I suggest you create a third column with the difference (Nov - Feb) and then use range colors on this column.

Here's a run anywhere example with dummy generated data

<dashboard>
<label>Column Compare Example</label>
<row>
<panel>
<title>Month totals diff comparison</title>
<table>
<search>
<query>| makeresults
| eval Nov=split("10 20 30 40", " ")
| mvexpand Nov
| eval Feb=case(Nov=10, 5, Nov=20, 10, Nov=30, 40, Nov==40, 40)
,month_diff=(Feb - Nov)
| table _time Nov Feb month_diff</query>
<earliest>-15m</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">100</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">true</option>
<format type="color" field="diff">
<colorPalette type="list">[#DC4E41,#006D9C,#53A051]</colorPalette>
<scale type="threshold">0,0.5</scale>
</format>
<format type="color" field="month_diff">
<colorPalette type="list">[#DC4E41,#006D9C,#53A051]</colorPalette>
<scale type="threshold">-0.5,0.5</scale>
</format>
</table>
</panel>
</row>
</dashboard>

yeahnah_0-1678827721741.png


Hope that helps

 

View solution in original post

jlit259
Engager

Yeah, nah @yeahnah has the answer. Create a third column with the diff, then edit the color and ranges to your needs

You probably already know setting the colors/values, but just in case

https://docs.splunk.com/Documentation/Splunk/latest/Viz/TableFormatsFormatting

bowesmana
SplunkTrust
SplunkTrust

You can do it without a third column in XML with a tiny bit of CSS. The CSS here will cause the second value of the multivalue field of Feb to be hidden, but the colorPalette expression can still use that field.

<dashboard>
  <label>Column Compare Example</label>
  <row>
    <panel>
      <title>Month totals diff comparison</title>
      <html depends="$hidden$">
        <style>
          #coloured_cell table tbody td div.multivalue-subcell[data-mv-index="1"]{
            display: none;
          }
        </style>
      </html>
      <table id="coloured_cell">
        <search>
          <query>
| makeresults
 | eval _raw="Nov       Feb
10           5
20          10
30          40"
| multikv forceheader=1
| table Nov Feb
| eval Comparison=if(Feb &lt; Nov,-1, 1)
| eval Feb=mvappend(Feb, Comparison)
| fields - Comparison
          </query>
          <earliest>-15m</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">100</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="Feb">
          <colorPalette type="expression">if(mvindex(value, 1) == "-1", "#008000", "#FF0000")</colorPalette>
        </format>
      </table>
    </panel>
  </row>
</dashboard>

There is a good generic example from @ITWhisperer here

https://community.splunk.com/t5/Splunk-Search/How-to-color-the-columns-based-on-previous-column-valu...

 

yeahnah
Motivator

Hi @revanthammineni 

I suggest you create a third column with the difference (Nov - Feb) and then use range colors on this column.

Here's a run anywhere example with dummy generated data

<dashboard>
<label>Column Compare Example</label>
<row>
<panel>
<title>Month totals diff comparison</title>
<table>
<search>
<query>| makeresults
| eval Nov=split("10 20 30 40", " ")
| mvexpand Nov
| eval Feb=case(Nov=10, 5, Nov=20, 10, Nov=30, 40, Nov==40, 40)
,month_diff=(Feb - Nov)
| table _time Nov Feb month_diff</query>
<earliest>-15m</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">100</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">true</option>
<format type="color" field="diff">
<colorPalette type="list">[#DC4E41,#006D9C,#53A051]</colorPalette>
<scale type="threshold">0,0.5</scale>
</format>
<format type="color" field="month_diff">
<colorPalette type="list">[#DC4E41,#006D9C,#53A051]</colorPalette>
<scale type="threshold">-0.5,0.5</scale>
</format>
</table>
</panel>
</row>
</dashboard>

yeahnah_0-1678827721741.png


Hope that helps

 

revanthammineni
Path Finder

Thanks man!! It worked

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...