Splunk Search

To Remove values with '0' from the calculation

sabari80
Explorer

I have below query to calculate average response times. For some reason some times the value is coming as '0'. i wanted to remove those values from my calculation. 

| mstats sum(calc:service.thaa_stress_requests_count_lr_tags) As "Count" ,avg(calc:service.thaa_stress_requests_lr_tags) As "Response" where index=itsi_im_metrics by Dimension.id
| eval Response=round((Response/1000000),2), Count=round(Count,0)
| search Dimension.id IN ("*Process.aspx")

-- Sample Values 

metric_name:calc:service.thaa_stress_requests_lr_tags: 4115725

metric_name:calc:service.thaa_stress_requests_lr_tags: 0

metric_name:calc:service.thaa_stress_requests_lr_tags: 3692799

Labels (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

I do not believe that mstats supports filtering by metric value.  But the question is also too vague.  Maybe you can explain your use case?  What does "remove values with '0'" mean?  From what calculation?  Given the three sample values you illustrated for metric calc:service.thaa_stress_requests_lr_tags, what is desired result from avg(calc:service.thaa_stress_requests_lr_tags)?  If your search period contains these three values, is the actual result 2602841.3333333335, i.e., (4115725 + 0 + 3692799) / 3?  Why do you want it to be different from the definition?

Further more, what method do you use to reveal those three values?  Metrics index cannot be searched as index search.  mstats can only give you aggregations.  Even if you group by _timeseries, you still only get aggregations.  This returns to the fundamental question: What is the point of "removing values with '0'"?

0 Karma

sabari80
Explorer

using mpreview command to explore the results 

| mpreview index=itsi_im_metrics | search "calc:service.thaa_stress_requests_lr_tags" "Dimension.id"="*Process.aspx"

Those values with '0' is not actual response, for some reason these entries are there and its affecting the overall average response. so wanted to remove those values from the calculation. 

 

instead of this --> (4115725 + 0 + 3692799) / 3, i want this --> (4115725 + 3692799) / 2

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Thank you for introducing msearch aka mpreview.  As I mentioned before, mstats doesn't allow filtering by value.  So, you need to take care of stats after mpreview.  Something like

| mpreview index=itsi_im_metrics
| search Dimension.id IN ("*Process.aspx")
 calc:service.thaa_stress_requests_count_lr_tags>0  calc:service.thaa_stress_requests_lr_tags > 0
| stats sum(calc:service.thaa_stress_requests_count_lr_tags) As "Count" ,
  avg(calc:service.thaa_stress_requests_lr_tags) As "Response"
  by Dimension.id
| eval Response=round((Response/1000000),2), Count=round(Count,0)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

| mstats sum(eval(if('calc:service.thaa_stress_requests_count_lr_tags'>0, 'calc:service.thaa_stress_requests_count_lr_tags', null()))) As "Count", avg(eval(if('calc:service.thaa_stress_requests_lr_tags'>0, 'calc:service.thaa_stress_requests_lr_tags', null()))) As "Response" where index=itsi_im_metrics by Dimension.id
0 Karma

sabari80
Explorer

getting error - 

Error in 'mstats' command: Invalid token: 

sum(eval(if('calc:service.thaa_stress_requests_count_lr_tags'>0
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try without quotes around the field names (perhaps there is something significant about the colon?

| mstats sum(eval(if(calc:service.thaa_stress_requests_count_lr_tags>0, calc:service.thaa_stress_requests_count_lr_tags, null()))) As "Count", avg(eval(if(calc:service.thaa_stress_requests_lr_tags>0, calc:service.thaa_stress_requests_lr_tags, null()))) As "Response" where index=itsi_im_metrics by Dimension.id
0 Karma

sabari80
Explorer

yes tried it out with all options already, with quote, without quote & double quotes. All are giving the same error.

Error in 'mstats' command:

Invalid token: sum(eval(if(calc:service.thaa_stress_requests_count_lr_tags>0

 

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...