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