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
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!