topic Re: Filtering queries by large lookup in Splunk Search

Filtering queries by large lookup

kevintelford — Mon, 21 Mar 2011 21:57:54 GMT

I have a large lookup full of bad domains. The lookup is simply a domain name per line. I would like to search through a section of logs and return events where the bad domain exists. My domain list looks like

baddomain
baddomain1.com
baddomain2.com
...

And the query I'm attempting to write looks like

index=myindex | append [ inputlookup domains.csv | fields baddomain ] | where domain=baddomain | ...

If I run just the inputlookup portion of the query I'm seeing my results, but they do not seem to be getting appended.

Thanks, Kevin

Re: Filtering queries by large lookup

araitz — Mon, 21 Mar 2011 22:39:41 GMT

How about:

index=myindex [ inputlookup domains.csv | rename baddomain as domain | fields baddomain]

This should be equivalent to:

index=myindex (domain=badguy.com OR domain=donkey.com ...)

Re: Filtering queries by large lookup

hazekamp — Mon, 21 Mar 2011 22:54:10 GMT

I typically use "lookup" instead of inputlookup along with a boolean column:

## baddomains.csv

domain,is_bad
bad.domain.com,true

index=myindex | lookup domains.csv domain OUTPUT is_bad | search is_bad=true

Re: Filtering queries by large lookup

kevintelford — Mon, 21 Mar 2011 23:57:00 GMT

Did I ever tell you you're my hero?
You're everything, everything I wish I could be.

Thanks dude, works swell!

Re: Filtering queries by large lookup

fox — Tue, 22 Mar 2011 00:12:26 GMT

you could also try:

[] LOOKUP-baddomain = ..... OUTPUTNEW ...

in your props.conf using the true and is_bad field.

with associated entry in transforms.conf:

[baddomain] default_match = no_match filename = lookup_baddomains.csv max_matches = 1 min_matches = 1

This way the search would be:

index=myindex is_bad=true

as the lookup will be appended automatically

Re: Filtering queries by large lookup

kevintelford — Tue, 22 Mar 2011 00:38:34 GMT

So I've become a liar. 2 issues.

In a distributed setup, it will not work unless the lookup is on the search head. Is there a way to tell it to look locally?
When I did put this file on the search head I got the error "Error in 'UnifiedSearch': Unable to parse 'The specified file is too large..."

Re: Filtering queries by large lookup

cfergus — Thu, 31 Mar 2011 01:37:06 GMT

So we worked the issue. The confusion was in the distributed search aspect.

We originally had the lookup file pushed out using the deployment server. So each indexer had the file in (app)/local/lookups. The search head did not have the lookup file.

It appears (please correct if this is wrong) that when using inputlookup, the lookup file need only be on the search head.

We were confusing this with regular lookups, where 'local=t' can be specified.

Re: Filtering queries by large lookup

kevintelford — Thu, 31 Mar 2011 01:40:17 GMT

GO TEAM NOVII!!