topic Re: stats with where clause not filtering in Splunk Search

stats with where clause not filtering

thailam — Wed, 30 Dec 2020 00:55:16 GMT

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!

Re: stats with where clause not filtering

to4kawa — Wed, 30 Dec 2020 01:08:54 GMT

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.

Re: stats with where clause not filtering

thailam — Wed, 30 Dec 2020 03:22:34 GMT

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

Re: stats with where clause not filtering

to4kawa — Wed, 30 Dec 2020 05:04:58 GMT

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

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

Re: stats with where clause not filtering

thailam — Wed, 30 Dec 2020 05:17:49 GMT

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!

Re: stats with where clause not filtering

scelikok — Wed, 30 Dec 2020 07:23:55 GMT

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

Re: stats with where clause not filtering

thailam — Wed, 30 Dec 2020 07:35:00 GMT

Hi Scelikok,

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

Re: stats with where clause not filtering

scelikok — Wed, 30 Dec 2020 08:21:10 GMT

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?

Re: stats with where clause not filtering

thailam — Wed, 30 Dec 2020 08:55:15 GMT

Hi Scelikok

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

Re: stats with where clause not filtering

thailam — Mon, 04 Jan 2021 05:36:07 GMT

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

Re: stats with where clause not filtering

thailam — Mon, 04 Jan 2021 05:38:05 GMT

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"?