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!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...