Dashboards & Visualizations

Defining different intervals for the Trend & Sparkline indicators on a single value visualization.

donfarland
Explorer

I have a simple tstats based query that looks for how many hosts have checked in over a period of time and then displays it as a single value visualization on a dashboard. The query and visualization work perfectly, but I was trying to figure out if I can get the trend indicator and the trendline to use different intervals.

| tstats dc(host) WHERE index="$site$" earliest=-14d@d latest=@d by _time span=7d

Currently, the visualization will show:

  • Value: The # of hosts that reported in over the past 7 days.
  • Trend Indicator: The difference between the last 7 days and the previous 7-day period.
  • Trendline: A simple line with two points showing that difference.

Ideally, I'd like to be able to define a 1d interval for the trendline to communicate to the user when the increases/decreases occurred. 

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The sparkline is the full output of the timechart or tstats command.  Value is the last value of the command and the trend indicator is the difference between the last two values.  There's no way to change that.  If you can make a case for different behavior, then do so at https://ideas.splunk.com

---
If this reply helps you, Karma would be appreciated.

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

To get 1day and 7day distinct counts you will first need to calculate the 1day dc() and then the 7 day(). In order to calculate the 7 day after the 1 day you need to retain ALL host values from the 1 day (by day) so you can then dc for 7 days.

In terms of getting the data

| tstats values(host) as host dc(host) as dc_host_1day WHERE index="$site$" earliest=-14d@d latest=@d by _time span=1d
| bin _time span=7d 
| eventstats dc(host) as dc_host_7day by _time
| fields - host

this will give you both the 1 day and 7 day dc count, however, I believe the challenge is to make the trendline work on the 1 day field, but the value to show for the 7 day field.

I don't believe that's going to be possible...

You might be better off to sit two single value panels side by side and have one show the 1 day and the other the 7 day.

You could also do some CSS manipulation to show one SV above the other and hide the second number. e.g. this CSS row 

  <row depends="$CSS$">
    <panel>
      <html>
        <style>
        #day1 .single-value-main-body {
          display:none !important;
        }
        #day1 .svg-container {
          top:-80px !important;
        }
        </style>
      </html>
    </panel>
  </row>

 would cause the main number of the 1 day display to be hidden and the sparkline for the 1 day to be shifted up, but this is rabbit hole territory... Note that the search below is creating dummy 1 day data in my example, but this gives you an idea of what can be done with CSS.

  <row>
    <panel>
      <single>
        <search base="base">
          <query>| stats values(dc_host_7day) by _time
          </query>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[0,30,70,100]</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">absolute</option>
        <option name="unitPosition">after</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
      </single>
      <html/>
      <single id="day1">
        <search id="base">
          <query>| tstats values(host) as host dc(host) as dc_host_1day WHERE index="*" earliest=-14d@d latest=@d by _time span=1d
| eval dc_host_1day=random() % 500
| bin _time span=7d 
| eventstats dc(host) as dc_host_7day by _time
| fields - host
| table _time dc_host_1day dc_host_7day</query>
          <earliest>1670270400.000</earliest>
          <latest>1670359017.000</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="drilldown">none</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x53a051","0x0877a6","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="rangeValues">[0,30,70,100]</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">absolute</option>
        <option name="unitPosition">after</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
      </single>
    </panel>
  </row>

 

donfarland
Explorer

That is a very interesting approach, but definitely a rabbit hole I'm going to avoid. I definitely think I'll just go for two visualizations as you suggest. Cleaner and simpler. It has the added benefit of being able to customize the drill-down behavior for both visualizations. The 7d can provide a list of changes for the week and the 1d can show daily counts in greater detail.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The sparkline is the full output of the timechart or tstats command.  Value is the last value of the command and the trend indicator is the difference between the last two values.  There's no way to change that.  If you can make a case for different behavior, then do so at https://ideas.splunk.com

---
If this reply helps you, Karma would be appreciated.

donfarland
Explorer

Thank you. I expected that was going to be the answer. I'll have to consider my use case and submit a request but will probably just take a different approach and splitting it into two visualizations.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...