Splunk Search

filter stats on two different "where" clauses

mbond81
Engager

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

0 Karma
1 Solution

niketn
Legend

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

cmerriman
Super Champion

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.

0 Karma

mbond81
Engager

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.

0 Karma

niketn
Legend

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

mbond81
Engager

Answered (solved) by Niketnilay. Thanks so much!

0 Karma

mbond81
Engager

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

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

mbond81
Engager

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

0 Karma

horsefez
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...