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.
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
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.