topic Re: DBConnect - Can we populate a lookup table from database data, on a period basis? in All Apps and Add-ons

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

jdunlea_splunk — Tue, 19 Mar 2013 13:30:38 GMT

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!

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

rgcurry — Wed, 08 May 2013 18:36:36 GMT

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.

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

0waste_splunk — Tue, 11 Jun 2013 14:03:19 GMT

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

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

jpass — Sun, 04 Aug 2013 14:21:06 GMT

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

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

rgcurry — Wed, 21 Aug 2013 13:06:35 GMT

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