stats with where clause not filtering

[thailam]

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!

[thailam]

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

[scelikok]

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 

[thailam]

Hi Scelikok,

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

[scelikok]

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? 

[thailam]

Hi Scelikok

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

[thailam]

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!

[thailam]

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

[to4kawa · Accepted solution]

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

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

[thailam]

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

[to4kawa]

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.