topic Re: sum function with conditions in Splunk Search

sum function with conditions

Raghav2384 — Mon, 28 Sep 2020 16:22:34 GMT

Hey there,

I am trying to get stats for one of our OpEx metrics

Working query : index=summary source="c:\\users\\njln0dr\\desktop\\splunk-use cases\\December.csv" |eval BTTR = Actual_Time_to_Resolve/3600|stats count(Number),sum(BTTR) as BTTR_Sum, perc95(BTTR) as P95 by "Group service"

every event has Actual_Time_to_Resolve based on which,i calculate BTTR and then P95 which is the 95th percentile of all events BTTR. Now, i want to calculate
P95 sum : sum of all the BTTRs which have a value greater than or equals to P95. Here's what i tried

index=summary source="c:\\users\\njln0dr\\desktop\\splunk-use cases\\December.csv" |eval BTTR = Actual_Time_to_Resolve/3600|stats count(Number),sum(BTTR) as BTTR_Sum, perc95(BTTR) as P95,sum(BTTR >= P95) as P95_Sum by "Group service"

I get the summary with all the evals but, P95_Sum is blank. Please help.

Thanks,
Raghav

Re: sum function with conditions

MuS — Mon, 14 Apr 2014 07:01:13 GMT

Hi Raghav2384,

this will not work, because the P95 field is only available after your stats not while it is running. This means you can use the P95 only in a next search command after the stats.

Here you have a run everywhere example how it could be done:

index=_internal source=*metrics.log | stats count(series) as count, sum(kbps) as sum_kbps, perc95(kbps) as P95 by series | where sum_kbps >= P95 | stats  values(count) as count, sum(P95) as P95_sum by series, P95

adapt this to your needs and it should work....if your search looks something like this:

index=summary source="c:\\users\\njln0dr\\desktop\\splunk-use cases\\December.csv" | eval BTTR = Actual_Time_to_Resolve/3600 | stats count(Number) as Count, sum(BTTR) as BTTR_Sum, perc95(BTTR) as P95 by "Group service" | where BTTR >= P95 | stats values(Count) AS Count, values(BTTR_Sum) AS BTTR_Sum, sum(BTTR) as P95_Sum by "Group service"

hope this helps to get you started ...

cheers, MuS

Re: sum function with conditions

Raghav2384 — Mon, 14 Apr 2014 23:04:08 GMT

Hey MuS,
I tried as you suggested but i get an error.Nothing moves forward after pipe |where
Error: Error in 'where' command: The 'sum' function is unsupported or undefined.
Here's the query i tried after your suggestion,

index=summary source="c:\\users\\njln0dr\\desktop\\splunk-use cases\\December.csv" | eval BTTR = Actual_Time_to_Resolve/3600 |stats count(Number) as Count, sum(BTTR) as BTTR_Sum, perc95(BTTR) as P95 by "Group service"|where sum(BTTR) >= P95 | stats values(Count) as Count,values(BTTR_Sum) as BTTR_Sum,values(P95) as P95,sum(BTTR) as P95_Sum by "Group service"

Thanks,Rag

Re: sum function with conditions

MuS — Tue, 15 Apr 2014 05:41:05 GMT

try this:

index=summary source="c:\\users\\njln0dr\\desktop\\splunk-use cases\\December.csv" | eval BTTR = Actual_Time_to_Resolve/3600 | stats count(Number) as Count, sum(BTTR) as BTTR_Sum, perc95(BTTR) as P95 by "Group service" | where BTTR_Sum >= P95 | stats values(Count) as Count,values(BTTR_Sum) as BTTR_Sum,values(P95) as P95,sum(BTTR) as P95_Sum by "Group service"

use the BTTR_sum name in where and not the sum() function. the sum name was created by the stats command and contains the summary of the BTTR values.

Re: sum function with conditions

Raghav2384 — Thu, 01 May 2014 16:26:43 GMT

Awesome!!! Thank you

Re: sum function with conditions

MuS — Thu, 01 May 2014 17:41:37 GMT

Feel free to accept this answer by ticking the tick, thank you.