How to return a list of hosts from a lookup table, but not in events?

[chrisfrigo]

Hi,

I'm using a lookup table with approx 107,000 lines and 6MB in size. Trying to display a list of hosts which are in a lookup table, but not in the Splunk events. The below search seems to work, but not effectively. The results display 96,648. I'm expecting more around 50,000. When I search a host which is in the results, it is contained in the Splunk events which validates it's not effective. Any idea whether there are limits on size of lookup tables?

| inputlookup workstations | rename fielda AS fieldb | dedup fieldb | search NOT [search index=index-name  | dedup fieldb | fields fieldb]

[vganjare]

You can try using multisearch command. e.g.

| multisearch [| inputlookup workstations | rename fielda AS fieldb | dedup fieldb | eval LOOKUP_CHECK="YES"] [search index=index-name  | dedup fieldb | fields fieldb | eval INDEX_CHECK="YES"] | stats values(LOOKUP_CHECK) as LOOKUP_CHECK, values(INDEX_CHECK) as INDEX_CHECK by fieldb | fillnull values="NULL" LOOKUP_CHECK | search LOOKUP_CHECK="NULL"

Thanks!!

[chrisfrigo · Accepted solution]

Ended up being subsearch limit to 10500 lines. updated the maxout and now working.

http://docs.splunk.com/Documentation/Splunk/6.2.1/Admin/Limitsconf

[HiroshiSatoh]

What with this?

index=index-name | dedup fieldb | fields fieldb | search NOT [ inputlookup workstations | rename fielda AS fieldb | dedup fieldb fields fieldb]

[chrisfrigo]

I see what your saying, the results should be minimal. but they are not matching. of the total 86,000 events, there are 76,000 display.

I will try reduce the CSV size