topic Re: To Remove values with '0' from the calculation in Splunk Search

To Remove values with '0' from the calculation

sabari80 — Fri, 22 Mar 2024 14:06:53 GMT

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

Re: To Remove values with '0' from the calculation

ITWhisperer — Fri, 22 Mar 2024 13:58:46 GMT

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

Re: To Remove values with '0' from the calculation

sabari80 — Fri, 22 Mar 2024 14:06:28 GMT

getting error -

Error in 'mstats' command: Invalid token:

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

Re: To Remove values with '0' from the calculation

ITWhisperer — Fri, 22 Mar 2024 16:23:21 GMT

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

Re: To Remove values with '0' from the calculation

sabari80 — Fri, 22 Mar 2024 18:40:22 GMT

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

Re: To Remove values with '0' from the calculation

yuanliu — Sat, 23 Mar 2024 07:51:47 GMT

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

Re: To Remove values with '0' from the calculation

sabari80 — Sat, 23 Mar 2024 15:51:36 GMT

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

Re: To Remove values with '0' from the calculation

yuanliu — Sun, 24 Mar 2024 05:48:29 GMT

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)