Splunk Search

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

pancham
Explorer

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

Labels (5)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

pancham
Explorer

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

Screenshot 2022-08-04 at 8.40.48 AM.png

0 Karma

somesoni2
Revered Legend

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
Revered Legend

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
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...