Is there a row limit to lookup tables?

[ilhwan]

My boss asked me to generate a report of people connecting to our network from public VPN providers.  I'm using this file  from github as a lookup table.  I added a column to make it a valid .csv.  The first couple of rows look like this:

NetworkAddress,isvpn
1.12.32.0/23,1
1.14.0.0/15,1

I added my own IP address to confirm that the lookup was working.  It works if I add as the first row but not as the last row.

Is there a row limit?  The file is only 425K, so I don't think I'm running into a file size limit, but it has 22682 rows.

[scelikok]

Hi @ilhwan,

You hit 10000 rows limit that @gcusello mentioned if you are using lookups as a subsearch with inputlookup command. This is subsearch results limit. Please use lookup command for searching inside lookup, lookup command has no limit.

[isoutamo]

Probably biggest issue with huge lookups will come as SH try to send it with query metadata to indexer and it will bigger that 2GB. Then you must do some config changes or prevent SH to send it to IDX nodes. I have seen some big lookups which splunk has converted some kind of mini indexes on SH side and which have more than 1 million rows.

[ilhwan]

Thanks for your reply.  This is the search I'm running:

index=cisco sourcetype=cisco:asa eventtype=cisco_vpn_start host!=shr-vpn-cell-fw01
| lookup vpnlist.csv NetworkAddress AS src_ip OUTPUTNEW isvpn
| stats count by src_ip, isvpn, Username

I'm definitely not using inputlookup, but I seem to be running into a limit on the number of rows.  Now I've converted the lookup to a KV lookup as @gcusello suggested, but I'm having trouble getting that working.

[gcusello]

Hi @ilhwan,

csv lookups have 10,000 rows limit.

using kv store there aren't limits, only that having more than 10.000 rows, searches are very slow.

having these numbers, use Data Models, that are DB tables, instead lookup.

Ciao.

Giuseppe

[ilhwan]

Thank you.  I'll need to figure out how to do that.

[gcusello]

Hi @ilhwan,

you can create a kv store lookup as a csv lookup only choosing kv store option.

for a Data Model, you have to follow steps at https://docs.splunk.com/Documentation/Splunk/9.0.3/Knowledge/Designdatamodelobjects or https://docs.splunk.com/Documentation/AddonBuilder/4.1.1/UserGuide/DataModel.

Ciao.

Giuseppe

[ilhwan]

Thank you for the links.  I converted my lookup to a KV lookup, and it keeps telling me that my lookup table is invalid.  I thought I could just keep the same .csv file, I'll have to figure out how to fix that.