[FIXED] How to combine count from two different mstats in where clause Splunk?

Issue

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 to combine them to show results only if

count1>0 and count2=0

How can I do that?

Solution

Per the docs.Splunk entry for mstats, you can append another mstats call. So something like this should work:

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

You should then be able to post-process the appended search results as desired

Answered By - warren

Answer Checked By - Senaida (PHPFixing Volunteer)