Dashboards & Visualizations

Trend Indicator always picking the last two days values

aditsss
Motivator

Hi Everyone,

I have one panel TimeOut . I have set the Trend Indicator in it to compare the count by percentage.

The problem I am facing is its always taking the last 2 days value and showing the percentage increase/decrease by comparing last two days value only.

I want if I select last 7 days from the date drop down

that is september 10 to september 16 .I want it should should show the percentage increase/decrease of september 10 and september 16. (First and last value)

But its showing percentage difference of september 15 and september 16.

what ever date Range I am selecting its always showing of last two days percentage increase/decrease  only.

I want the percentage difference of 1st value and last value.

Can someone guide me where I am wrong.

Below is my XML Code.

<row>
<panel>
<single>
<title>TIMEOUT</title>
<search>
<query>index="ABC" sourcetype=XYZTimeout $OrgName$ | bin span=1d _time |stats count by _time</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="colorBy">value</option>
<option name="drilldown">all</option>
<option name="height">100</option>
<option name="numberPrecision">0</option>
<option name="rangeValues">[0,10,25,40]</option>
<option name="trendDisplayMode">percent</option>
<option name="unit"></option>
<option name="rangeColors">["0xFF0000","0xFF0000","0xFF0000","0xFF0000","0xFF0000"]</option>
<option name="useColors">1</option>
<option name="showSparkline">1</option>
<option name="trendDisplayMode">percent</option>
<drilldown>
<set token="show_panel3">true</set>
<set token="selected_value3">$click.value$</set>
</drilldown>
</single>
</panel>

Labels (1)
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

So now you want the first non-zero count and the last non-zero count, right?

<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ | bin span=1d _time
| stats count by _time
| eventstats first(_time) as firsttime last(_time) as lasttime
| where _time = firsttime OR _time = lasttime
| fields _time count
</query>

 

View solution in original post

thambisetty
SplunkTrust
SplunkTrust

https://community.splunk.com/t5/Splunk-Search/How-to-set-the-Trend-indicator-to-show-the-increase-an...

————————————
If this helps, give a like below.
0 Karma

aditsss
Motivator

Hi thambisetty,

I dont want to compare 3 values .I want to compare two values only .

But its always taking last two days value only.

I want it should take 1st value and the and the last value.

Like I select 12th sep to 14 th september then I want to show the pecentage difference of 12th and 14th but its taking 13th and 14th last two days.

Can someone guide me on that.

Below is my xml code.

<panel>
<single>
<title>TIMEOUT</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ | bin span=1d _time |stats count by _time</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="colorBy">value</option>
<option name="drilldown">all</option>
<option name="height">100</option>
<option name="numberPrecision">0</option>
<option name="rangeValues">[0,10,25,40]</option>
<option name="trendDisplayMode">percent</option>
<option name="unit"></option>
<option name="rangeColors">["0xFF0000","0xFF0000","0xFF0000","0xFF0000","0xFF0000"]</option>
<option name="useColors">1</option>
<option name="showSparkline">1</option>
<option name="trendDisplayMode">percent</option>
<drilldown>
<set token="show_panel3">true</set>
<set token="selected_value3">$click.value$</set>
</drilldown>
</single>
</panel>

ITWhisperer
SplunkTrust
SplunkTrust

The span has to match the difference in days you are interested in i.e.

| bin span=6d _time

aditsss
Motivator

Hi ITWhisperer.

I cant change the bin as its not always the 7 days that I need to compare. By default as of now I have selected as 7 days.

Is there any other way I can do that.

Also by changing like this its first calculating all 6 days values (Total of 6 days value ) and then comparing with 7th day.

I just want first and last value to be compared what ever time range I should select.

| bin span=6d _time

 

XML CODE

<panel>
<single>
<title>TIMEOUT</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ | bin span=1d _time |stats count by _time</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="colorBy">value</option>
<option name="drilldown">all</option>
<option name="height">100</option>
<option name="numberPrecision">0</option>
<option name="rangeValues">[0,10,25,40]</option>
<option name="trendDisplayMode">percent</option>
<option name="unit"></option>
<option name="rangeColors">["0xFF0000","0xFF0000","0xFF0000","0xFF0000","0xFF0000"]</option>
<option name="useColors">1</option>
<option name="showSparkline">1</option>
<option name="trendDisplayMode">percent</option>
<drilldown>
<set token="show_panel3">true</set>
<set token="selected_value3">$click.value$</set>
</drilldown>
</single>
</panel>

ITWhisperer
SplunkTrust
SplunkTrust

You could make the value of the span in the bin a token and calculate the value of the token in your timepicker. Alternatively, you could take the earliest and latest values from your stats table and remove the other values so that the trend is based on those

aditsss
Motivator

Hi ITWhisperer,

 

Thanks for all your guidance  which always works for me.

Can you please provide the code for it. Suppose I need to check the trend for last 7 days .

How Can I compare first and last values. Please guide.

Below is my xml code:

Salesforce Performance Overview

<row>
<panel>
<single>
<title>TIMEOUT</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ | bin span=1d _time |stats count by _time</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="colorBy">value</option>
<option name="drilldown">all</option>
<option name="height">100</option>
<option name="numberPrecision">0</option>
<option name="rangeValues">[0,10,25,40]</option>
<option name="trendDisplayMode">percent</option>
<option name="unit"></option>
<option name="rangeColors">["0xFF0000","0xFF0000","0xFF0000","0xFF0000","0xFF0000"]</option>
<option name="useColors">1</option>
<option name="showSparkline">1</option>
<option name="trendDisplayMode">percent</option>
<drilldown>
<set token="show_panel3">true</set>
<set token="selected_value3">$click.value$</set>
</drilldown>
</single>
</panel>

 

ITWhisperer
SplunkTrust
SplunkTrust
<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ 
| bin span=1d _time 
| stats count by _time
| addinfo
| eval first=round(info_min_time,0)
| eval last=round(info_max_time,0)-(60*60*24)
| where _time = first OR _time = last
| fields _time count
</query>

aditsss
Motivator

Hi ITWhisperer,

 

Its showing no result found for me .Complete code

<panel>
<single>
<title>TIMEOUT</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ | addinfo
| eval first=round(info_min_time,0)
| eval last=round(info_max_time,0)-(60*60*24)
| where _time = first OR _time = last
| fields _time count
</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="colorBy">value</option>
<option name="drilldown">all</option>
<option name="height">100</option>
<option name="numberPrecision">0</option>
<option name="rangeValues">[0,10,25,40]</option>
<option name="trendDisplayMode">percent</option>
<option name="unit"></option>
<option name="rangeColors">["0xFF0000","0xFF0000","0xFF0000","0xFF0000","0xFF0000"]</option>
<option name="useColors">1</option>
<option name="showSparkline">1</option>
<option name="trendDisplayMode">percent</option>
<drilldown>
<set token="show_panel3">true</set>
<set token="selected_value3">$click.value$</set>
</drilldown>
</single>
</panel>

ITWhisperer
SplunkTrust
SplunkTrust

Hi @aditsss 

Please read the suggestion carefully - you missed out two important lines

aditsss
Motivator

Hi ITWhisperer,

 I am facing one issue.

Can you guide me with this.

I want last 7 days data to be populated  correctly if we consider like for today it should be from 14th to 20th September data and suppose there will be no data on 20th like 0 value then in that case it should show the percentage difference trend of 14th and 19th . What changes I should make to show it correct data.

when I select the range from 14th to 20th September then its showing correctly(percentage Trend) but when I select last 7 days from date/Time drop-down its not showing correctly.

Why its going wrong for last 7 days.

Below is my xml query:

<input type="time" token="field1" searchWhenChanged="true">
<label>Date/Time</label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>
</fieldset>
<row>
<panel>
<single>
<title>TIMEOUT</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ | bin span=1d _time
| stats count by _time| addinfo
| eval first=round(info_min_time,0)
| eval last=round(info_max_time,0)-(60*60*24)
| where _time = first OR _time = last
| fields _time count
</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="colorBy">value</option>
<option name="drilldown">all</option>
<option name="height">100</option>
<option name="numberPrecision">0</option>
<option name="rangeValues">[0,10,25,40]</option>
<option name="trendDisplayMode">percent</option>
<option name="unit"></option>
<option name="rangeColors">["0xFF0000","0xFF0000","0xFF0000","0xFF0000","0xFF0000"]</option>
<option name="useColors">1</option>
<option name="showSparkline">1</option>
<option name="trendDisplayMode">percent</option>
<drilldown>
<set token="show_panel3">true</set>
<set token="selected_value3">$click.value$</set>
</drilldown>
</single>
</panel>

aditsss
Motivator

Hi ITWhisperer,

 

Can you guide me on this.

Thanks in advance.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try this for the query

<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ | bin span=1d _time
| stats count by _time
| addinfo
| eval first=relative_time(info_min_time,"@d")
| eval last=relative_time(info_max_time-1,"@d")
| eval prev=last-(60*60*24)
| eventstats last(count) as lastcount
| where _time = first OR (_time = last AND lastcount > 0) OR (_time = prev AND lastcount = 0)
| fields _time count
</query>

aditsss
Motivator

Hi ITWhisperer,

I am again facing the same Issue.

Its like I have select last 7 days means 15th to 21st  september.

I have no data for 22nd September that is 0 as of today and 21st September is also 0 (yesterday)so in that case.

I want the TimeOut panel to display the value of 20th september that is 1903 but its displaying september15 value and its not showing any trend indicator.

In short I want if I select last 7 days and data is only available suppose till 19th September.

Then in that case the TimeOut panel should display the value of 19th september and the percentage trend should show the difference between 15th and 19th.

Previously by the query you provided this one(BELOW one)It was showing the correct value always for the timeout panel but its always calculating the trend of last two days only . 

<query>index="ABC" sourcetype=XYZTimeout $OrgName$ | bin span=1d _time |stats count by _time</query>

I have attached the screenshots .

Can you guide me on this:

Below is my latest XML Code .

 

<input type="time" token="field1" searchWhenChanged="true">
<label>Date/Time</label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>
</fieldset>
<row>
<panel>
<single>
<title>TIMEOUT</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ | bin span=1d _time
| stats count by _time| addinfo
| eval first=relative_time(info_min_time,"@d")
| eval last=relative_time(info_max_time-1,"@d")
| eval prev=last-(60*60*24)
| eventstats last(count) as lastcount
| where _time = first OR (_time = last AND lastcount > 0) OR (_time = prev AND lastcount = 0)
| fields _time count
</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="colorBy">value</option>
<option name="drilldown">all</option>
<option name="height">100</option>
<option name="numberPrecision">0</option>
<option name="rangeValues">[0,10,25,40]</option>
<option name="trendDisplayMode">percent</option>
<option name="unit"></option>
<option name="rangeColors">["0xFF0000","0xFF0000","0xFF0000","0xFF0000","0xFF0000"]</option>
<option name="useColors">1</option>
<option name="showSparkline">1</option>
<option name="trendDisplayMode">percent</option>
<drilldown>
<set token="show_panel3">true</set>
<set token="selected_value3">$click.value$</set>
</drilldown>
</single>
</panel>

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

So now you want the first non-zero count and the last non-zero count, right?

<query>index="ABC" sourcetype=XYZ Timeout $OrgName$ | bin span=1d _time
| stats count by _time
| eventstats first(_time) as firsttime last(_time) as lasttime
| where _time = firsttime OR _time = lasttime
| fields _time count
</query>

 

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...