Splunk Search

sum function with conditions

Raghav2384
Motivator

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

Tags (1)
0 Karma
1 Solution

MuS
SplunkTrust
SplunkTrust

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

View solution in original post

MuS
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

0 Karma

Raghav2384
Motivator

Awesome!!! Thank you

0 Karma

MuS
SplunkTrust
SplunkTrust

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
Motivator

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...