Splunk Search

How to store a large search result set into a lookup table?

ddrillic
Ultra Champion

I have a large results set of a search which I would like to store as a lookup table. How can I do that?

Tags (2)
0 Karma
1 Solution

javiergn
Super Champion

Use the outputlookup command.
If large means millions of lines, you might be better off specifying .csv.gz in order to create a compressed file that you can later on read with inputlookup.

View solution in original post

javiergn
Super Champion

Use the outputlookup command.
If large means millions of lines, you might be better off specifying .csv.gz in order to create a compressed file that you can later on read with inputlookup.

ddrillic
Ultra Champion

Great.

We ran:

index=provider tin!=" *" tin!="000000000"  tin="*" | dedup tin | fields - _raw | table * | outputlookup provider_lookup.csv

It created a 2.2 GBs file on the SH's file system and I can see it under the 'Lookup table files' as -
/opt/splunk/splunk/etc/apps/search/lookups/provider_lookup.csv

What should I do now?

0 Karma

javiergn
Super Champion

You can use it with either inputlookup or just lookup.
Keep in mind you will need to create a lookup in order to do that.
Take a look at the very detailed documentation about this topic.

Hope that helps.

0 Karma

ddrillic
Ultra Champion

Thank you javiergn - worked nicely but slow with a lookup table of 2 GBs and physical memory of 16 MBs.

The lookup part of the command looks like -
| lookup provider_lookup tin as prov_tin OUTPUT adr_ln_1_txt

0 Karma

rusty009
Path Finder

also be careful about saving large lookup files on your search head. You may bump into issues with syncing your bundle across indexers if you hit your maximum bundle size. You may need to whitelist the lookup folder within that app > https://answers.splunk.com/answers/3436/how-could-i-optimize-distributed-replication-of-large-lookup...

ddrillic
Ultra Champion

Thanks a lot!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...