Splunk Search

How to calculate percentage and percent difference?

wanda619
Path Finder

Hi community,

I am stuck on a problem where i have to calculate percentage and Percent Difference. 

 

I have 3 columns, for example -- 

Name |  Errorcode |  Result

abc     |   324   |   5

abc     |    999 |   1

abc     |  Total |    6

I want the output to look like this -- 

Name |  Errorcode |  Result | Percent of Total |  Percent Difference ( week over week)

abc     |   324   |   5 |     83.33 | 25

abc     |    999 |   1 | 16.67 |  100

abc     |  Total |    6 | 100 |  100 

for Percent Difference (week over week) should look at the errors for that Name from the prior week and understanding the percent difference to this week.  Example, if there were 3 1027 errorcodes last week and 6 1027 errors this week the percent difference would be 100%. 

Labels (4)
Tags (2)
0 Karma
1 Solution

martinpu
Communicator
| bucket _time span=1w
| stats c as Result by Name by _time Errorcode name
| eventstats sum(Result) as Result_Total by Errorcode name
| eval 'Percent of Total'=round(100*Result/Result_Total ,2)
| sort - _time
| streamstats current=f latest(Result) as Result_Prev  by Errorcode name
| eval 'Percent Difference'=round(((Result/Result_Prev)-1)*100 ,2))

Maybe like this then ? 

Result Result_Prev  Percent Difference'
3250
105100
39-66.77
330

 

It is a more accurate percent difference calculation 
 

View solution in original post

wanda619
Path Finder

@martinpu I am facing issues in getting previous week results, any other alternative than this, My code - 

| bucket _time span=1w
| lookup table_1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by CustomerName, ErrorCode
| eventstats sum(Result) as Total by CustomerName
| eval PercentOfTotal = round((Result/Total)*100,3)
| sort - _time
| streamstats current=f latest(Result) as Result_Prev by CustomerName
| eval PercentDifference = round(((Result/Result_Prev)-1)*100,2)
| fillnull value="0"
| append
[ search index=abc= xyz:123 ErrorCode!=0
| `DedupDHI`
| lookup Table_1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| stats count as Result by CustomerName
| eval ErrorCode="Total", PercentOfTotal=100]
| fillnull value="0"
| lookup Table_2 ErrorCode OUTPUT Description
| lookup Table_1 LicenseKey OUTPUT CustomerName
| eval CustomerName=coalesce(CustomerName,LicenseKey)
| eval Error=if(ErrorCode!="Total", ErrorCode+" ("+coalesce(Description,"Description Missing - Update Table_2")+")", ErrorCode)
| rename Result_Prev as "Previous Week Results", PercentDifference as " Percent Difference", PercentOfTotal as "Percent of Total"
| fields CustomerName, Error, Result,"Previous Week Results", " Percent Difference" , "Percent of Total"
| sort CustomerName, Error, PercentDifference

Output - 

CustomerNameErrorResultPrevious Week Results Percent DifferencePercent of Total
AIG Private Client Group1002 (abc)4003.252
AIG Private Client Group1003 (cxz)24-501.626
AIG Private Client Group1013 (Invalid Format)1242009.756
AIG Private Client Group1023 (Invalid Name)34-252.439
AIG Private Client Group1027 (Invalid )1024245082.927
AIG Private Client GroupTotal12300100
AIICO1023 (Invalid Name)80038.095
AIICO120118-87.54.762
AIICO1305 1285057.143
AIICOTotal2100100
Acceptance1023 (Invalid Name)30027.273
Acceptance102783166.6772.727
AcceptanceTotal1100100

 

The Previous Week Results column is appending 4, if noticed which is wrong. any suggestions to solve this

0 Karma

wanda619
Path Finder

@martinpu thank you! I have another question, what if the percent difference is neagtive. for example - previous week it was 9 , this week it is 3. how can i show it as -50% ???? 

0 Karma

martinpu
Communicator
| bucket _time span=1w
| stats c as Result by Name by _time Errorcode name
| eventstats sum(Result) as Result_Total by Errorcode name
| eval 'Percent of Total'=round(100*Result/Result_Total ,2)
| sort - _time
| streamstats current=f latest(Result) as Result_Prev  by Errorcode name
| eval 'Percent Difference'=round(((Result/Result_Prev)-1)*100 ,2))

Maybe like this then ? 

Result Result_Prev  Percent Difference'
3250
105100
39-66.77
330

 

It is a more accurate percent difference calculation 
 

martinpu
Communicator

Something like this?

| bucket _time span=1w
| stats c as Result by Name by _time Errorcode name
| eventstats sum(Result) as Result_Total by Errorcode name
| eval 'Percent of Total'=round(100*Result/Result_Total ,2)
| sort - _time
| streamstats current=f latest(Result) as Result_Prev  by Errorcode name
| eval 'Percent Difference'=round(100*Result/Result_Prev ,2)

And then follow it up with table command to reorder the fields.

I'm not sure if it's going to give you the right results, maybe the sort should be the other way around.

 

Get Updates on the Splunk Community!

Index This | Divide 100 by half. What do you get?

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

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...