Splunk Search

## help for calculating a trend in a table panel

Motivator

hello

from the code below, i would like to be able to add a new colum in my table panel which calculate the percentage trend between the previous count and the next count for each line
Example

Month count Trend
January 200 --
February 300 +34%
March 100 -66%

index="toto" sourcetype="tutu" assigned_group="titi"
| dedup incident_number
| eval Month=strftime(_time,"%Y-%m")
| stats count by Month
| sort -Month

thanks for your help

Tags (1)
SplunkTrust

Are your example numbers what you want?

Between Jan and Feb

• 300 is 50% more than 200 (b > a)
• 200 is 33.3% less than 300 (a < b)

by between Feb and Mar

• 300 is 200% more than 100 (a > b)
• 100 is 66% less than 200 (b < a)

so in your example are you looking to show,

a) when negative, what % the smaller value is of the larger value
b) when positive, what % the difference between the values is of the larger value

or something else.

Note that the sort -Month will sort in reverse chronological order, so with the %Y-%m your order will be March,Feb,Jan. Naturally if you then use delta, that will give different results.

So you could use

| eval Month=strftime(_time,"%Y-%m")
| stats count by Month
| sort Month
| eval diff=0
| delta count as diff
| eval percentage=round(diff/count*100,0)

Note that if you change the sort order the delta values are different. In the ascending sort, then change from 300 to 100 shows -200%.

So it sort of depends if Feb in your example is needed to show

a) Feb is a 50% growth over Jan
b) Jan was 33% lower than Feb

but to give you your example, you would have to do

| sort Month
| eval diff=0
| delta count as diff
| eval percentage=round(if(diff>0,diff/count*100,diff/(count-diff)*100),0)

NB: Ascending sort

Run anywhere example

| makeresults
| rename COMMENT as "Setting up data to show example"
| eval f="2019-01,200#2019-02,300#2019-03,100"
| makemv delim="#" f
| mvexpand f
| rex field=f "(?<Month>[^,]*),(?<count>.*)"
| fields - f, _time
| sort Month
| eval diff=0
| delta count as diff
| eval percentage=round(if(diff>0,diff/count*100,diff/(count-diff)*100),0)
Motivator

hi perfect demonstration but for the percentage calculation i have the same question asked to
jitendragupta

SplunkTrust

I guess you need to express what the percentage is that you wish to see

Path Finder

try this:

index="toto" sourcetype="tutu" assigned_group="titi"
| dedup incident_number
| eval Month=strftime(_time,"%Y-%m")
| stats count by Month
| sort -Month
| delta count as Diff
| eval percentageTrend =(Diff/count)*100

Motivator

thanks it works but the calculation of the percentageTrend is strange
to my mind its not Diff/ count but (count-1/count)*100?

Ultra Champion
| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"-1y@month"), _time)
| makecontinuous span=1d
| eval incident_number="incedent_number_".(random() % 25 + 1)
| bin span=1month _time
| stats dc(incident_number) as count by _time
| eval Month=strftime(_time,"%B, %y")
| delta count as diff
| eval Trend=(diff / count * 100)."%"
| table Month count Trend

Is Month in this format?

Motivator

Thanks it seems to be good
Month format is 😐 eval Month=strftime(_time,"%Y-%m")

Motivator

last question : is it possible to have + between the Trend when the Trend is positive??

Ultra Champion
| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"-2y@month"), _time)
| makecontinuous span=1d
| eval incident_number="incedent_number_".(random() % 25 + 1)
| bin span=1month _time
| stats dc(incident_number) as count by _time
| eval Month=strftime(_time,"%Y-%m")
| delta count as diff
| eval Trend=(diff / count * 100)
| autoregress Trend as Trend_diff
| eval positive_diff=if(Trend >= 0 , Trend - Trend_diff,NULL)
| eval Trend=Trend."%", Trend_diff=positive_diff."%"
| table Month count Trend Trend_diff

Is this it?

Motivator

yes thanks

Ultra Champion

Get Updates on the Splunk Community!

#### Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

#### Index This | How many sides does a circle have?

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

#### Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...