All Apps and Add-ons

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

lohit
Path Finder

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 ?

Tags (2)
0 Karma
1 Solution

maciep
Champion

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.

View solution in original post

maciep
Champion

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
Path Finder

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

0 Karma

maciep
Champion

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

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...