To Remove values with '0' from the calculation

[sabari80]

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

[yuanliu]

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'"?

[sabari80]

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

[yuanliu]

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)

[ITWhisperer]

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

[sabari80]

getting error - 

Error in 'mstats' command: Invalid token: 

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

[ITWhisperer]

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

[sabari80]

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