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
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
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.
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.
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
Answered (solved) by Niketnilay. Thanks so much!
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!!
@mbond81, I have converted my comment to answer. Please accept to mark this as answered!
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
Hi mbond81,
to get help at splunk answers more quickly you should always provide some sample data when possible.