Splunk lookup file - lookup field question

[Pellecrino]

Hi all,

I've got a lookup file called devices.csv that contains 2 fields, hostname and ip_address.

The index I'm searching has 2 fields, src_ip and dest_ip.

I'd like to exclude results where both the src_ip and dest_ip fields match an IP address from my lookup file, it doesn't need to be the same IP, it just needs to be listed in that CSV. If either the src_ip field or the dest_ip field doesn't contain an IP address listed in the ip_address field I would expect to see it.

I'm just looking for advice on whether this is the best way of querying the data. Current query:

index=network_traffic AND NOT ([| inputlookup devices.csv | fields ip_address | rename ip_address AS src_ip] AND [| inputlookup devices.csv | fields ip_address | rename ip_address AS dest_ip])

[yuanliu]

I agree with @PickleRick that using lookup might be more performant if the lookup file is not very large and there are not many matches.  If the lookup is very large, you can eliminate one subsearch because there is only one lookup.

index=network_traffic NOT 
    [inputlookup devices.csv
    | stats values(ip_address) AS src_ip
    | eval dest_ip = src_ip]

[PickleRick]

While the search is technically more or lese correct, its performance will depend on the use case and with a big lookup you might hit search limits.

Another possible approach would be

<your_base_search>
| lookup my.csv src_ip OUTPUT matchsrc_ip
| lookup my.csv dest_ip OUTPUT matchdest_ip
| where isnull(matchsrc_ip) AND isnull(matchdest_ip)

[gcusello]

Hi @Pellecrino ,

your search seems to be correct, I'd change the order of the commands, even if it should not be relevant:

index=network_traffic NOT ( [ | inputlookup devices.csv | rename ip_address AS src_ip | fields src_ip ] [ | inputlookup devices.csv | rename ip_address AS dest_ip | fields dest_ip ] )

Debug the issue running one by one the two conditions.

Ciao.

Giuseppe