sum function with conditions

[Raghav2384]

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

[MuS · Accepted solution]

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

[MuS]

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

[Raghav2384]

Awesome!!! Thank you

[MuS]

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.

[Raghav2384]

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