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
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
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
Feel free to accept this answer by ticking the tick, thank you.
Awesome!!! Thank you
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.
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