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

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

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

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

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...