Splunk Search

stats with where clause not filtering

thailam
Engager

Good day everyone,

Ran into following problem,

The query
index=source | eval time=strftime(_time, "%+)

|stats

max(time)

values(from) as Sender,

values(rcpt) as Recipients,

value(subject) as Subject

values(hops_ip) as SenderIP

values (ref) as Reference

by ref |where like(senderIP, "10.%)

 

Not sure where went wrong, senderIP which is not 10.% is still showing. I did noticed that the ref value appears multiple times for different transaction, that could be the cause? Happy new year in advance!

Labels (2)
0 Karma
1 Solution

to4kawa
Ultra Champion

your SenderIP is multivalue. it can't work with where like()

If you only know the logs, you should make single value from SenderIP. 

View solution in original post

0 Karma

thailam
Engager

Due to the log "ref" value may sometime appears multiple times, is there a way i am able to filter by "ref" together with "hdr_mid"?

0 Karma

scelikok
SplunkTrust
SplunkTrust

Hi @thailam,

You should better filter hops_ip before stats like below;

index=source hops_ip="10.0.0.0/8"
| stats max(_time) as _time values(from) as Sender values(rcpt) as Recipients values(subject) as Subject values(hops_ip) as SenderIP values(ref) as Reference by ref 
If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

thailam
Engager

Hi Scelikok,

 

Just tried that, it works however the sender and recipients is now empty 😕

 

thailam_0-1609313651881.png

 

0 Karma

scelikok
SplunkTrust
SplunkTrust

Only reason maybe "from" and "rcpt" field names are wrong. Can you please check is there is something wrong about case or typo. Do you see these fields on "Interesting Fields" list? 

If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

thailam
Engager

Hi Scelikok

 

Yes that's correct, its not showing right after i've moved the where clause to the top.

thailam_0-1609318381887.png

 

0 Karma

thailam
Engager

The log has for example "ref" in the log sometime may show different IP. Is anyway i can achieve something similar and to filter away unwanted IP? Thanks!

0 Karma

thailam
Engager

I have tried the following, however the IP which is not "10%" still showing. Thanks sincerely!

thailam_4-1609298479012.png

thailam_3-1609298303390.png

 

0 Karma

to4kawa
Ultra Champion

your SenderIP is multivalue. it can't work with where like()

If you only know the logs, you should make single value from SenderIP. 

0 Karma

thailam
Engager

I think this is correct, where can only filter a single value. Have tried, whenever there is only single value it correctly removes it.

0 Karma

to4kawa
Ultra Champion

field name is case-sensitive, so senderIP is not same with SenderIP.

 

 

index=source 
|stats max(_time) as _time, values(from) as Sender, values(rcpt) as Recipients, values(subject) as Subject, values(hops_ip) as SenderIP by ref |where like(SenderIP, "10.%") | rename ref as Reference

 

 

Also, there are too many minor mistakes. 

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