Compare lookup file to a field in an index help.

[zyz101z]

I have a list of malicious URL's that I have inputted into a lookup table called badurls.csv.  I created a field in the table called domains.  I want to compare that lookup table against an Index and specifically against a field called Domain to see if we have any traffic going to this list of malicious URL's.  

My .csv file has over 3 million entries.  I tried the search below but its not giving me all results and its complaining about a 10,000 line subsearch limit.  

index="dns" | eval d=substr(Domain, 1, len(Domain)-1) | search * [|inputlookup badurls.csv |
rename domains as d | fields + d ] | stats count by d

Any ideas on a better way to do this?

[inventsekar]

Hi @zyz101z .. if my understanding is correct, then, subsearch+inputlookup is not needed, just the "lookup" is enough. 

i assumed your badurls.csv got two columns(malicious_url and domain)

index="dns" | eval d=substr(Domain, 1, len(Domain)-1) 
| lookup badurls.csv domain as d OUTPUT url as malicious_url 
| stats count by d

 please try this.. some editing may be required. 

~ Happy Splunking ~ Karma points welcome!

[zyz101z]

My badurls.csv just has a single column of malicious domains.  

[inventsekar]

ohk, generally, a lookup file will have two or more columns(remember "dnslookup"...its converting the name www.google.com to its ip address). so i assumed that your lookup file got 2 columns, my bad 😉 

if you are having only one field, then, if i am in your position, i would simply "ingest" that file, then do the searching as "index="dns" OR index="malicious-domains" |...

or, if you like the inputlookup+subsearch, then increasing the limit was one idea. 

now, as you are having only one field, @bowesmana 's search is perfect!

ok, good that you have found out the solution. you may "like" my replies(add karma points), as your 2 cent for me ;)!

[bowesmana · Accepted solution]

Use the lookup as a lookup, rather than a search constraint

index="dns" 
| eval d=substr(Domain, 1, len(Domain)-1) 
| lookup badurls.csv Domain as d OUTPUT Domain as Found
| where !isnull(Found)
| stats count by d

[zyz101z]

This worked perfectly Thanks!!