topic Re: filter stats on two different "where" clauses in Splunk Search

filter stats on two different "where" clauses

mbond81 — Fri, 16 Jun 2017 23:47:48 GMT

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.

Re: filter stats on two different "where" clauses

horsefez — Sat, 17 Jun 2017 00:06:36 GMT

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

Re: filter stats on two different "where" clauses

niketn — Sat, 17 Jun 2017 05:58:54 GMT

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

Re: filter stats on two different "where" clauses

mbond81 — Sat, 17 Jun 2017 12:04:39 GMT

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

Re: filter stats on two different "where" clauses

cmerriman — Sat, 17 Jun 2017 15:42:37 GMT

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.

Re: filter stats on two different "where" clauses

mbond81 — Sat, 17 Jun 2017 15:51:46 GMT

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.

Re: filter stats on two different "where" clauses

mbond81 — Sat, 17 Jun 2017 15:56:54 GMT

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

Re: filter stats on two different "where" clauses

mbond81 — Sat, 17 Jun 2017 15:58:33 GMT

Answered (solved) by Niketnilay. Thanks so much!

Re: filter stats on two different "where" clauses

niketn — Sat, 17 Jun 2017 23:51:38 GMT

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