filter stats on two different "where" clauses

[mbond81]

Bonus points to the folks who can help me.
I'm trying to first filter (stats count) results above a threshold of 100 -AND- of those results, I need there to be more than 3 in order to be considered a problem. So far my search is showing me 1 result, when I want it to show me "no results" if there aren't more than 3.
There are 4 or more "gateways" per "core node". I want to know which "core node" has 4 or more of it's "gateways" over the "retries" message threshold.

index= host= sourcetype=

|stats count as retries by gateway corenode
| join [search index= host= sourcetype=
| stats dc(gateway) as gateways by corenode]
| where gateways>3 AND retries>100
| table gateway gateways corenode retries

[cmerriman]

Without seeing any sample data and limited information on the query, my suggestion is to simply try using search instead of where.

|search gateways>3 retries>100

But if you could provide sample data, that would be more helpful, as well as if the data is in separate source types. I think you might be able to get by without the join and just use stats command to bring the information together.

[mbond81]

The gateways are named, not numbered - so I need to dc(count) them by core node first in order to get a count I can filter on.

[niketn · Accepted solution]

You have not mentioned the reason for JOIN. Is the index, source and sourcetype same in both your query?

<Your Base Search>
| stats count as retries by gateway corenode 
| search retries>100
| eval gateway_retries= gateway."-".retries
| stats dc(gateway) as gateways values(gateway_retries) as gateway_retries by corenode
| search gateways>3
| table corenode gateways gateway_retries

[mbond81]

Answered (solved) by Niketnilay. Thanks so much!

[mbond81]

I think that works! I ran it against 2 cases, 1 that should've returned my >3 gateways and one that should not have and it worked as expected 🙂 Thank you so much!!

[niketn]

@mbond81, I have converted my comment to answer. Please accept to mark this as answered!

[mbond81]

Well, I used join because I get staggered table results when I use append search. If I use neither, and simply combine my stats clauses [|stats count as retries, dc(gateway) as gateways by CN | where gateways>3 AND retries>100], the search only picks up the retries limit and not the gateway count.
index=x, host=y sourcetype=z for both searches, no different.

As for sample data, here are the results when I try to run this...
Not what I want (triggered for number of retries, but not for number of gateways)
gateway gateways corenode retries
hys 4 DEN 1878

This is what I want: more than 3 gateways crossed the 100 retries limit.
gateway gateways corenode retries
den 4 SLC 108
rks 4 SLC 303
rno 4 SLC 1335
sgu 4 SLC 6180

[horsefez]

Hi mbond81,
to get help at splunk answers more quickly you should always provide some sample data when possible.