topic Re: How to use AND clause between different count from Mstats queries? in Splunk Search

How to use AND clause between different count from Mstats queries?

pancham — Wed, 03 Aug 2022 18:49:09 GMT

query 1
|mstats count(_value) as count1 WHERE metric_name="*metric1*" AND metric_type=c AND status="success" by metric_name,env,status| where count1>0

query 2

|mstats count(_value) as count2 WHERE metric_name="*metric2*" AND metric_type=c AND status="success" by metric_name,env,status| where count2=0

These queries are working fine individually I need combine show results only if

count1>0 and count2=0

Re: How to use AND clause between different count from Mstats queries?

somesoni2 — Wed, 03 Aug 2022 20:35:31 GMT

Try like this

|mstats count(_value) as count1 WHERE metric_name="*metric1*" AND metric_type=c AND status="success" by metric_name,env,status | append [|mstats count(_value) as count2 WHERE metric_name="*metric2*" AND metric_type=c AND status="success" by metric_name,env,status] | stats values(count1) as count1 values(count2) as count2 by by metric_name,env,status | where count1>0 AND count2>0

Re: How to use AND clause between different count from Mstats queries?

pancham — Thu, 04 Aug 2022 03:23:57 GMT

@somesoni2 Below is the result without WHERE clause, If I apply count1>0 and count2>0 condition it is getting applied on each individual row but I'm looking for AND operator between these 2 queries. if count from metric1 query AND count 2 from metric2 query is >0 then show results.

Re: How to use AND clause between different count from Mstats queries?

somesoni2 — Thu, 04 Aug 2022 13:05:01 GMT

Give this a try

|mstats count(_value) as count WHERE (metric_name="*metric1*" OR metric_name="*metric2*") AND metric_type=c AND status="success" by metric_name,env,status | eval temp=env."#".status | eval metric_name=if(match(metric_name,"metric1"),"metric1","metric2") | chart values(count) over temp by metric_name | WHERE metric1>0 AND metric>0

|mstats count(_value) as count1 WHERE metric_name="*metric1*" AND metric_type=c AND status="success" by metric_name,env,status | appendcols [|mstats count(_value) as count2 WHERE metric_name="*metric2*" AND metric_type=c AND status="success" by metric_name,env,status] | where count1>0 AND count2>0