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

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

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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...