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

[pancham]

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

[pancham]

@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.

[somesoni2 · Accepted solution]

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

OR 

|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

[somesoni2]

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