All Apps and Add-ons

DBConnect - Can we populate a lookup table from database data, on a period basis?

jdunlea_splunk
Splunk Employee
Splunk Employee

I want to lookup data from my database and bring it into Splunk to add more information to my log events. However I do not want my seaches querying the database every time we run a search as it may be large load on the database. Is there any way that we can build an internal lookup table in Splunk by looking up the data in the database on a periodic basis and then using this lookup table for my searches?

This eliminates the issue of querying the database for every search we run.

Thanks!

jpass
Contributor

Yes I do this using a saved search in conjunction with Splunk's DBConnect App which has a 'dbquery' command. The saved search

| dbquery malcodefam "SELECT myfield1,myfield2,myfield3 FROM mytable" | FIELDS myfield1,myfield2,myfield3 | outputlookup mylookupfile.csv

A saved search runs once every hour and replaces the lookup file for me. I'm on Splunk 4.3 and If my memory is correct, the OUTPUTLOOKUP command can only 'replace' the lookup file. In later versions I 'think' you can update the lookup file with new data as opposed to having to recreated the entire thing each time. It's not a big deal for me though because this is a small dbtable. The reason I did this is becacuse I don't want to provide access to the dbquery command to all users

-j

rgcurry
Contributor

Have you considered running a scheduled script 'owned' by the Splunk User ID that would collect the data you want and rebuild the lookup table CSB file dynamically as a temp file then replace the 'real' lookup file once it is built? This would also give you the ability to archive older versions to any level you wanted.

0 Karma

rgcurry
Contributor

It is kept private versus set to app level or global.

0 Karma

0waste_splunk
Communicator

Sorry for asking but i am noob.
can you provide more info on script 'owned' by splunk User ID?

0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...