Splunk Search

Is there a row limit to lookup tables?

ilhwan
Path Finder

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.

Labels (1)
0 Karma

scelikok
SplunkTrust
SplunkTrust

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.

If this reply helps you an upvote and "Accept as Solution" is appreciated.

isoutamo
SplunkTrust
SplunkTrust

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
Path Finder

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

ilhwan
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...