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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...