Archive

Most efficient way to exclude two fields from a lookup

Path Finder

Hi,

Whats the most efficient way to use a lookup table within a query to exclude results where 2 fields exist, i.e. a source IP address AND a destination port? so we only exclude results where BOTH fields are seen within the the same event. The source to a different port should still appear in the results.

I'm thinking maybe a join from the base search to the lookup with a type=outer might accomplish the same as a AND NOT?

Or would there be a better more efficient way to accomplish this?

Thanks in advance!

Tags (1)
0 Karma

SplunkTrust
SplunkTrust

index=aindex NOT [| inputlookup yourlookup.csv | fields src_ip dst_port | format ]

0 Karma

Revered Legend

Assuming your lookup table has fields src_ip and dest_port and fields with same names exist in your data, and number of rows in your lookups are less than 10K, this would be the best method to filter your logs:-

index=foo sourcetype=bar..your base search criteria..  NOT [| inputlookup yourLookupTable.csv | table src_ip dest_port | format ]

The subsearch would add filters in the format NOT ((dest_port="port1" AND src_ip="ip1") OR (dest_port="port2" AND src_ip="ip2").., so it'll only exclude events which has both the dest_port and src_ip combination value in them, all others will not be filtered.

0 Karma

Path Finder

Perfect. Thanks somesoni2.

0 Karma

SplunkTrust
SplunkTrust

Everybody now!

0 Karma

SplunkTrust
SplunkTrust

so, you can use lookups to exclude events based on those events having fields that match some value in the lookup, i.e.

basesearch-that-has-port | lookup exclusionList port OUTPUTNEW port as isFound | where isnull(isfound)

You could do additionally lookups for other fields if you are looking at IP as well

basesearch-that-has-port | lookup exclusionList port OUTPUTNEW port as portIsFound | lookup exclusionList ip OUTPUTNEW port as ipIsFound |  where isnull(portIsfound) AND isnull(ipIsFound)

Please let me know if this answers your question!

0 Karma

SplunkTrust
SplunkTrust

It's not clear what you're trying to do. You say you want to exclude events with certain fields, but you also want those fields in your results. Please share some sample events and an example of your desired results.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Path Finder

I cant share the sample data. But the idea is to bring back all source devices going to certain destinations. BUT exclude known traffic (taken from the lookup file).

i.e. a known exchange server on port 25. If it's not an exchange server going to port 25 then I'd want to know about it.

So basically use the lookup for known traffic that is expected, and exclude these from the results.

0 Karma