Splunk DB Connect: How to connect to a database and output data into a CSV file periodically for use in different searches?

[lohit]

Hi All ,

I want to connect to a database and output the data into a CSV file periodically. The output CSV file will then be used in different searches. I am not sure how to do it with Splunk DB Connect APP ? Any help ?

[maciep · Accepted solution]

We the same thing for a few lookups here. Here's a high-level approach (assuming dbconnect 1.x). I'm not sure at which points you may need further help because they're all going to require a certain understanding

1. Create the database connection in dbconnect
2. Using that connection, write a search using dbquery to get the results you want
3. Once you're search is returning the data you want, pipe the results to the outputlookup command
4. That command will create a lookup file for you that you can use in a search
5. Create a lookup definition for the new lookup file
6. Try using that lookup in a search using the lookup command
7. If that works, then take your search (with the output lookup) and save it.
8. Now schedule that saved search

So on that schedule, the search will run and update your lookup file.

Other things to consider:
1. App Context of where you want that lookup to live
2. Permissions for that lookup
3. Automatically run searches against the lookup?

One other note is that dbconnect will allows you to create database lookups as well. Meaning, instead of going through the process of writing to a csv on a schedule, you could just query the database directly every time you need to do a lookup. Obviously, that might put more stress on your database if those lookups would be run often.

[lohit]

Awesum explanation maciep !! I followed ur steps but i am stuck in setup. "Java Bridge Server nor running". I read docs and it says your JAVA_HOME path. For some reason it is not set in my environment. So i did which java and it redirects me to /usr/bin/java which indeed is a sym link to /usr/java/default/bin/java, so in the DBConnect app i set up home to be /usr/java/default but to no avail.

Could you let me know where i am wrong !!

[maciep]

I'm not sure how much I can help here. We have a team that manages our linux boxes for us, so I'm not sure how exactly java is installed. But if it does help, here is the path we're using in dbconnect, ultimately pointing to the jre directory:

/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/jre