Splunk Search

help for calculating a trend in a table panel

jip31
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)
0 Karma

bowesmana
SplunkTrust
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)
0 Karma

jip31
Motivator

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

jitendragupta
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

0 Karma

jip31
Motivator

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

0 Karma

to4kawa
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?

0 Karma

jip31
Motivator

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

0 Karma

jip31
Motivator

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

0 Karma

to4kawa
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?

0 Karma

jip31
Motivator

yes thanks

0 Karma

to4kawa
Ultra Champion

please accept the answer

0 Karma
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 ...