Dashboards & Visualizations
Highlighted

How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

Explorer

Hi there,
I have a visualisation project that I am working on which tracks calls into a call centre - 90% of calls should succeed and I want to highlight that with colour on the visualisation based upon a single number values. However, during daytime the volume of calls is in the 10000s but at night time the calls drop down to low tens and twenties. As shown in the graph below.

alt text

The single value visualisation colour range is based on absolute values. What I prefer is to display is the absolute value and apply colour coding (red, amber, green) based the call volume success percentage.

I know how to generate all the raw numbers and percentages but I don't know how to make it work in the visualisation or even if it is possible. Any pointers please.

Tags (2)
0 Karma
Highlighted

Re: How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

SplunkTrust
SplunkTrust

hello there,
can you share the search you are using?
do you want to see the percentage on the same panel or on a separate panel?

0 Karma
Highlighted

Re: How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

Explorer

Hi Adonio,

The search looks something like this.
index="rtivr" sourcetype="ivr:businesstask_trace" action="finish" | stats count(eval(action="finish")) as totalCalls, count(eval(status="complete")) as "Complete Calls" | eval "% Complete"=round((('Complete Calls' / totalCalls) * 100),2 )

Check back on my description... What I want to achieve is a display of the number of "Complete Calls" but colour rules applied applied to the number displayed based on the value of "%Complete" value red=<20%, green=80%+ etc

0 Karma
Highlighted

Re: How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

SplunkTrust
SplunkTrust

hmmm, to be clear, you would like to keep same visualization but have a "background calculation" that colors the "Complete Calls" stacked chart with different colors matching the roles you set on it?

0 Karma
Highlighted

Re: How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

Explorer

No charts required - just a simple value. The visualisation is just a big number using something like the single value panel.

So just a simple message like.

**12234  Total Calls Received**

But the text is coloured differently according to the value of the % percentage value.

0 Karma
Highlighted

Re: How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

Motivator
    <search>
      <query>| inputlookup splunk-stock.csv | eval _time=strptime(date, "%Y-%m-%d") | timechart last(close) span=7d | fillnull value=NULL</query>
      <earliest>-4h@m</earliest>
      <latest>now</latest>
      <sampleRatio>1</sampleRatio>
    </search>
   <option name="trendColorInterpretation">standard</option>
    <option name="trendDisplayMode">percent</option>
    <option name="trendInterval">-1y</option>
    <option name="colorBy">trend</option>
    <option name="colorMode">block</option>
    <option name="numberPrecision">0.00</option>
    <option name="showTrendIndicator">1</option>
    <option name="showSparkline">1</option>
    <option name="useColors">1</option>
    <option name="useThousandSeparators">1</option>
    <option name="height">143</option>
    <option name="underLabel">compared to 1 year before</option>
    <option name="drilldown">none</option>
    <option name="unit">$</option>
    <option name="unitPosition">before</option>
  </single>

more details check simplexmlexamples app from splunkbase

0 Karma
Highlighted

Re: How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

Legend

alt text
For a Single Value Panel like the one attached you need following modifications:

PS:
- I have added OR action="complete" to your base search.
- Converted from stats to timechart to show trending. Here the Trend Indicator 2.56% implies increase in Success percentage in the final 1 hour as compared to the hour before final (since the timechart span is 1 hour span=1h). Also 99.14% is the Success % in the final hour.
- For getting total count of calls action="finish" logically seems incorrect (are you sure you dont want to count all calls started as action="start" or similar based on what you have in your IVR logs?
- This code utilizes HTML panel and CSS Styling.

  <search id="baseSuccessCall">
    <query>index="rt_ivr" sourcetype="ivr:business_task_trace" action="finish" OR action="complete" 
| timechart span=1h count(eval(action=="finish")) as totalCalls count(eval(status=="complete")) as completedCalls </query>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
    <sampleRatio>1</sampleRatio>
  </search>
  <search base="baseSuccessCall">
    <query>tail 1
| eval incompleteCalls=totalCalls-completedCalls
| table completedCalls incompleteCalls</query>
    <done>
      <condition match="$job.resultCount$==0">
        <set token="tok_Success">0</set>
        <set token="tok_Failed">0</set>
      </condition>
      <condition>
        <set token="tok_Success">$result.completedCalls$</set>
        <set token="tok_Failed">$result.incompleteCalls$</set>
      </condition>
    </done>
  </search>
  <row>
    <panel>
      <single>
        <title></title>
        <search base="baseSuccessCall">
          <query>| eval perc=round(((completedCalls)/totalCalls)*100,2)
| fillnull value=0 perc
| table _time perc</query>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0.00</option>
        <option name="rangeColors">["0xd93f3c","0xf58f39","0xf7bc38","0x65a637"]</option>
        <option name="rangeValues">[20,80,90]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">percent</option>
        <option name="underLabel">Success %</option>
        <option name="unit">%</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">0</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <html>
        <style>
          .dashboard-row .dashboard-panel {
            margin: 0 0px 0px 0;
          }
          .left_align_text {
            float: left;
            width: 125px;
            text-align: left;
            margin: 2px 10px;
            display: inline;
            color:green;
            font-size:120%;
          }

          .right_align_text {
            float: right;
            text-align: right;
            margin: 2px 10px;
            display: inline;
            color:red;
            font-size:120%;            
          }          
        </style>
        <div class="left_align_text">Success:$tok_Success$</div><div class="right_align_text">Failed:$tok_Failed$</div>
      </html>
    </panel>
  </row>

Finally only one Single Value Visualization with a HTML panel might not look if left alone in their separate rows. You might have to plan for adding relevant timechart or table or else several single values (5 or 6) on similar lines bur representing different systems/KPIs

Refer to the dummy run anywhere sample dashboard created on similar lines:

  <search id="baseSuccessCall">
    <query>index=_internal sourcetype="*ui_access" status=*
| timechart span=1h count(eval(status="200")) as "Success" count(eval(status!="200")) as "Failed"</query>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
    <sampleRatio>1</sampleRatio>
  </search>
  <search base="baseSuccessCall">
    <query>tail 1
| table Success Failed</query>
    <done>
      <condition match="$job.resultCount$==0">
        <set token="tok_Success">0</set>
        <set token="tok_Failed">0</set>
      </condition>
      <condition>
        <set token="tok_Success">$result.Success$</set>
        <set token="tok_Failed">$result.Failed$</set>
      </condition>
    </done>
  </search>
  <row>
    <panel>
      <single>
        <title></title>
        <search base="baseSuccessCall">
          <query>| eval perc=round(((Success-Failed)/Success)*100,2)
| fillnull value=0 perc
| table _time perc</query>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0.00</option>
        <option name="rangeColors">["0xd93f3c","0xf58f39","0xf7bc38","0x65a637"]</option>
        <option name="rangeValues">[20,80,90]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">percent</option>
        <option name="underLabel">Success %</option>
        <option name="unit">%</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">0</option>
      </single>
      <single>
        <title></title>
        <search base="baseSuccessCall">
          <query>| eval perc=round(((Success-Failed)/Success)*100,2)
| fillnull value=0 perc
| table _time perc</query>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0.00</option>
        <option name="rangeColors">["0xd93f3c","0xf58f39","0xf7bc38","0x65a637"]</option>
        <option name="rangeValues">[20,80,90]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">percent</option>
        <option name="underLabel">Success %</option>
        <option name="unit">%</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">0</option>
      </single>
      <single>
        <title></title>
        <search base="baseSuccessCall">
          <query>| eval perc=round(((Success-Failed)/Success)*100,2)
| fillnull value=0 perc
| table _time perc</query>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0.00</option>
        <option name="rangeColors">["0xd93f3c","0xf58f39","0xf7bc38","0x65a637"]</option>
        <option name="rangeValues">[20,80,90]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">percent</option>
        <option name="underLabel">Success %</option>
        <option name="unit">%</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">0</option>
      </single>
      <single>
        <title></title>
        <search base="baseSuccessCall">
          <query>| eval perc=round(((Success-Failed)/Success)*100,2)
| fillnull value=0 perc
| table _time perc</query>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0.00</option>
        <option name="rangeColors">["0xd93f3c","0xf58f39","0xf7bc38","0x65a637"]</option>
        <option name="rangeValues">[20,80,90]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">percent</option>
        <option name="underLabel">Success %</option>
        <option name="unit">%</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">0</option>
      </single>
      <single>
        <title></title>
        <search base="baseSuccessCall">
          <query>| eval perc=round(((Success-Failed)/Success)*100,2)
| fillnull value=0 perc
| table _time perc</query>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0.00</option>
        <option name="rangeColors">["0xd93f3c","0xf58f39","0xf7bc38","0x65a637"]</option>
        <option name="rangeValues">[20,80,90]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">percent</option>
        <option name="underLabel">Success %</option>
        <option name="unit">%</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">0</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <html>
        <style>
          .dashboard-row .dashboard-panel {
            margin: 0 0px 0px 0;
          }
          .left_align_text {
            float: left;
            width: 125px;
            text-align: left;
            margin: 2px 10px;
            display: inline;
            color:green;
            font-size:120%;
          }

          .right_align_text {
            float: right;
            text-align: right;
            margin: 2px 10px;
            display: inline;
            color:red;
            font-size:120%;            
          }          
        </style>
        <div class="left_align_text">Success:$tok_Success$</div><div class="right_align_text">Failed:$tok_Failed$</div>
      </html>
    </panel>
    <panel>
      <html>
        <div class="left_align_text">Success:$tok_Success$</div><div class="right_align_text">Failed:$tok_Failed$</div>
      </html>
    </panel>
    <panel>
      <html>
        <div class="left_align_text">Success:$tok_Success$</div><div class="right_align_text">Failed:$tok_Failed$</div>
      </html>
    </panel>
    <panel>
      <html>
        <div class="left_align_text">Success:$tok_Success$</div><div class="right_align_text">Failed:$tok_Failed$</div>
      </html>
    </panel>    
    <panel>
      <html>
        <div class="left_align_text">Success:$tok_Success$</div><div class="right_align_text">Failed:$tok_Failed$</div>
      </html>
    </panel>    
  </row>



| eval message="Happy Splunking!!!"


View solution in original post

Highlighted

Re: How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

Explorer

Wow, that is pretty much exactly what I asked for, thank you very much indeed. Sorry for the late reply but was snowed under yesterday afternoon.

I think for my application I want to make the percentage display smaller and the absolute numbers smaller as I am trying to match an existing monitoring display that will be going in to an operations centre and they tend to have fixed ideas about how things look.

So as I understand it the approach is to use one base search to get the absolute numbers but in a time series so that I have the option of using the sparkline etc so that trending will be visible. Next feed those numbers into two visualisations first visualisation shows the percentage and colours (fab) and the second displays the absolute value and could be colour coded as well - I would choose grey for "zero" since there should always be numbers coming in and if the value is positive set the appropriuately.

One thing I notice is that I see a validation error in the xml for the three elements below. I am on Splunk v6.5.2 in case that has a bearing on this.
option name="trellis.enabled">0 option name="trellis.scales.shared">1 option name="trellis.size">medium</option

A little explanation on the base search that I provided. This application records events as either start or finish. Finish means that they have completed. Then there is a further code to indicate in what condition they finished which can OK, technical error, business error etc. So I did simplify my original qustion to try and keep it generic and therefore generally useful for other splunk answers users.

0 Karma
Highlighted

Re: How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

Legend

Trellis got introduced in 6.6. Since, you are on 6.5 you can remove the lines with validation error (Do check out trellis example it is a cool new visualization feature).

For reducing the size of Single Value font, you have two options:
Option 1) Simple quick fix is to reduce the Single Value Panel height which reduces Font Accordingly. Following is Simple XML code for the same

<option name="height">100</option>

Option 2) Through CSS you can override font-size of specific/all single value results using !important in CSS (add to existing Style section).

.single-value .single-result{
    font-size:75% !important;
}

PS: You will have to make changes as per your need. I have give percent option so that you can easily change and understand impact.

Absolute numbers are displayed in HTML Panel with their respective CSS Styles you can override as per you need.

Finally color ranges (grey) as you have intended can also be done via UI. I think currently since it is on Percent Trending it should be Grey for 0%.




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: How to set the colour of a single value absolute number based on its value relative to another value e.g. percentage of total

Explorer

Here is the resulting dashboard. It shows colour based on percentage and absolute values. So I am accepting this answer happily.

alt text

0 Karma