Most efficient way to exclude two fields from a lookup

[jacqu3sy]

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!

[jkat54]

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

[somesoni2]

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.

[jacqu3sy]

Perfect. Thanks somesoni2.

[jkat54]

Everybody now!

[muebel]

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!

[richgalloway]

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.

[jacqu3sy]

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.