How to use lookup table to make a filter?

[chuckfefer]

hello,

i would like to make a filter with an index field named "host", that means this field has to be different of a list of values like:
host!='a' AND host!='b' AND host!='c' etc...
i think i have to use a lookup table but i don't find the syntax
could you give me a clue?

[lorenzoalbanof]

Alternatively, you could use the lookup table as usual with | lookup (which seems to be more efficient),  creating a dummy variable host_b. Which will be not-null if the name is in your lookup table.

Say you have made a yourhostlookup.csv, which has field host and other fields too. 

Then you add this to your search.

| lookup yourhostlookup.csv host outputnew host as host_b
| where isnotnull(host_b) ``` true if host value is found in the lookup, for host values not in the lookup use |where isnull(host_b)``` 
| fields - host_b ``` continue without the dummy field...```

It will output the dummy field host_b, which will be not null (and equal to host naturally) if "host" from your original search is found in the lookup table. The filtering is done on the dummy field by |where .
In your particular case, you want to check for the host not being there, so you use |where isnull(host_b) . Finish by taking the dummy field out of your search.

[starcher]

EDIT (just realized someone answered this too) So consider this my thumbs up to that.

Though the embedded example others provided works. It should be used sparingly.

The TL/DR use a look AS a lookup. That scales even at large lookup sizes.

| mysearch
| lookup mylookup host OUTPUTNEW host as to_filterr
| where isnull(to_filter)

gives you results where it is not in the table. this has added option of using the lookup match types such as wildcard, CIDR etc etc. Calling a csv file directly in a subsearch does not.

It is best to use the repeatable pattern.

[lmonahan]

+ a million for this clever answer!

[chuckfefer]

hello,
since i asked my question, i found the answer while i was in the subway;)
in fact, i thought i had to write a pipe after the inputlookup command but in this case i don't need
it's a simple filter before a 'stats' command like this:
sourcetype=foo ... NOT [| inputlookup host-filter.csv] | stats sum(bytes_in) , sum(bytes_out), dc(user) by host | ...
and the name of the column in the lookup table is the same of the indexed field, means host
thank you for your help

[muebel]

Hi chuckfefer, as long as the lookup table isn't very large (over 10000 entries), you can use a subsearch to achieve what you want. For instance, if your filter is set as a lookup name host-filter.csv, you could do:

sourcetype=foo field1=bar NOT [|inputlookup host-filter.csv | table host]

Please let me know if this answers your question!

[somesoni2]

YOu can use the syntax like this

index=foo sourcetype=bar NOT [| inputlookup yourhostlookup.csv | fields host ]

When Splunk parses the query, it will be normalized to the expression that you've listed.