Splunk Search

How to display the delta value in percentage

akarivaratharaj
Communicator

I have a requirement to show the monthly growth percentage of an object. Basically need to find out the growth of an object in percentage when compared to the previous month.Below is the query which I am using

index=main
|timechart span=1mon sum(OBJECT_SIZE_GB) as Size
|delta Size AS Data_Growth p=1

So first I am displaying the total size of my object as "Size" and then displaying the size difference between consecutive months with the help of delta. Below is the sample how the results look like (added '|' for more clarity):

_time | Size | Data_Growth
2018-01 | 2429833.14 |
2018-02 | 2511015.66 | 81182.52
2018-03 | 2571432.37 | 60416.71

Now I need to find the Growth percentage of each month. I am not sure how to achieve this.

Could anyone please help me on this.

0 Karma
1 Solution

somesoni2
Revered Legend

Try like this

index=main 
|timechart span=1mon sum(OBJECT_SIZE_GB) as Size
|delta Size AS Data_Growth p=1
| eval Perc_Growth=round(Data_Growth*100/(Size-Data_Growth),2)

In denominator, I'm subtracting Data_Growth from current rows Size to get the Size of previous row, so that we can compare how much it grew from previous time span.

View solution in original post

somesoni2
Revered Legend

Try like this

index=main 
|timechart span=1mon sum(OBJECT_SIZE_GB) as Size
|delta Size AS Data_Growth p=1
| eval Perc_Growth=round(Data_Growth*100/(Size-Data_Growth),2)

In denominator, I'm subtracting Data_Growth from current rows Size to get the Size of previous row, so that we can compare how much it grew from previous time span.

View solution in original post

niketnilay
Legend

@akarivaratharaj, try the following search:

index=main OBJECT_SIZE_GB=*
| timechart span=1mon sum(OBJECT_SIZE_GB) as Size
| delta Size as Delta_Growth
| eval Growth_Perc=round((Delta_Growth/(Size-Delta_Growth))*100,1)
| fillnull value=0 Growth_Perc
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

akarivaratharaj
Communicator

@p_gurav & @niketnilay,

Thanks both for the quick response.

I have tried both the query, I can see there is little difference between both the results. Because one is just dividing the data_growth by total size and multiplying with 100 to get the percentage and the other one is dividing the data_growth by sum of total size & data_growth and then multiplying with 100.

Can I know the difference between both the queries and which gives me the effective/accurate results.

0 Karma

akarivaratharaj
Communicator

Actually Data_Growth field is nothing but delta of two consecutive sizes. But why that needs to be added or subtracted to/from Size field.

I am not clear on that part. Can anyone please explain me.

0 Karma

niketnilay
Legend

@akarivaratharaj, In simple terms if in the month of March Data Volume was 100 GB and in April it became 110 GB, the Data Growth is 10% i.e.

(10/100)*100 or (delta/old value)*100 or (delta/(new value-delta))*100 or (10/(110-10))*100

Where old value=new value - delta

Try the following run any where search on similar lines as per example here:

|  makeresults
|  eval data="Time=\"03/01/2018\",Size=100;Time=\"04/01/2018\",Size=110;"
|  makemv data delim=";"
|  mvexpand data
|  rename data as _raw
|  KV
|  table Time Size
|  delta Size as deltaGrowth
|  eval percentGrowth=round((deltaGrowth/(Size-deltaGrowth))*100,1)
|  fillnull percentGrowth value=0

Hope it clarifies the math ( I am not good at it myself ;)).

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

akarivaratharaj
Communicator

Thankyou so much for everyone's help

0 Karma

niketnilay
Legend

@akarivaratharaj, please accept @somesoni2 's answer. Also up vote all the comments that helped.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

akarivaratharaj
Communicator

sure!! Thankyou!!

somesoni2
Revered Legend

Generally when you calculate percent growth (or just growth), so compare what it was (previous value) with what it's now (current value). So, for 2nd row growth, you'd compare the change (Data_Growth) considering value of 1st row as base. Now to value of first row, we're using current value (Size)-change from previous value(Data_Growth) to calculate previous row. If instead of Delta, you'd use streamstats, you'd be doing like this:

index=main 
 |timechart span=1mon sum(OBJECT_SIZE_GB) as Size
| streamstats current=f window=1 values(Size) as previous_Size
| eval Data_Growth=Size-previous_Size
| eval Perc_Growth=round((Size-previous_Size)*100/previous_Size,2)

akarivaratharaj
Communicator

I have used streamstats only. Thankyou!

0 Karma

niketnilay
Legend

@akarivaratharaj, Sorry I did a mistake as pointed out by @somesoni2. I was supposed to do a Size-Delta_Growth to get the previous Size. I have corrected my query. Do try out @somesoni2 's answer and accept the same.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

p_gurav
Champion

Can you try something like this after your search query:

 | eval Data_Growth=round(abs(Data_Growth/Size)*100,0) 
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!