topic Re: Using Splunk DB Connect 1, how can I enrich my search results by inserting matching values from a MySQL database? in Splunk Search

Using Splunk DB Connect 1, how can I enrich my search results by inserting matching values from a MySQL database?

joea9 — Thu, 22 Oct 2015 13:31:47 GMT

I want to be able to enrich my Splunk search results using data in a MySQL database.
Where the 'hostname' field in my Splunk search results matches the hostname field in my database, I want to insert the IP address from the database.

So far I have set up the database connection within Splunk using DB Connect 1, and have returned the contents of my database using dbquery in the search bar.

But how do I now create the database lookup that inserts the IP address into my search results when the hostname is found?

(I've read all of the documentation but it isn't helping me to understand, I'd appreciate it if someone could provide an explanation with an example).

Re: Using Splunk DB Connect 1, how can I enrich my search results by inserting matching values from a MySQL database?

woodcock — Thu, 22 Oct 2015 14:09:09 GMT

It is all right here:

http://docs.splunk.com/Documentation/DBX/1.2.2/DeployDBX/Setupadatabaselookuptable

Re: Using Splunk DB Connect 1, how can I enrich my search results by inserting matching values from a MySQL database?

joea9 — Tue, 29 Sep 2020 07:41:53 GMT

I've probably looked at that page 300 times over the last 2 days!

I've created a 'database lookup' although I'm not sure sure I've done it correctly without an example, the documentation seems really light on information.

The example query on the page you linked looks like this:

index=test | lookup local=1 mysql_table ip_address as clientip OUTPUT host | table clientip, host

My best guess at interpreting that for my set up is:

source="/var/log/audit/audit.log" | lookup myiplookup myhost as host OUTPUT myip | table myip, host

(Where myiplookup is my 'database lookup', myhost is the hostname field in my database, myip is the ip field in my database, and host is the host field in my search results).

A table is returned, but it's not inserting anything from the database (the search also returning 'error code 1').

Re: Using Splunk DB Connect 1, how can I enrich my search results by inserting matching values from a MySQL database?

joea9 — Sun, 25 Oct 2015 14:22:12 GMT

I got this working...because I had a hard time finding any decent info regarding this, I'll explain what I had to do.

Install DB connect
Go to the Splunk DB Connect app in Splunk
Go to "database connections in Splunk manager" --> New
Database type - choose the type of database you are using, you will need to find and install the relevant driver for your database if you don't have it already, but Splunk will give you all of the info you need to do this.
Transaction Isolation Level - I had this set to 'DATABASE_SETTING'
The rest of the options here are self explanatory, once you have saved that you should have an external database connection listed.
You should now be able to run a dbquery command to pull in data from your database:

| dbquery list "select * from myTable"

In the above query, 'list' is the name of the database connection you've just set up

Next you need to set up the Lookup so that data can be inserted from your database
Go to Manager menu --> Database Lookups --> New
Database - The name of the database connection you just created will appear here
Database Table - The name of the table that you will be pulling data from
Lookup Fields - Choose all of the fields that you want to pull into Splunk, including the field you are matching on
You should now be able to run a lookup:

host="coffee" | lookup iplookup myhost as host OUTPUT myip, metadata | table host, myip, metadata

In the above query, 'lookup' just means that you're running a lookup query. iplookup is the name of my lookup in Manager-->Database lookup. myhost is the field in my database that I want to match with a field in my Splunk search results. I then want to OUTPUT the related fields from my database, in this case I want to enrich my results with the myip field and the metadata field. I then pipe the results into a table within Splunk, showing me the host, myip and metadata fields.

Re: Using Splunk DB Connect 1, how can I enrich my search results by inserting matching values from a MySQL database?

Richfez — Mon, 26 Oct 2015 01:35:34 GMT

joea9,

Nice write up, thanks for posting back what you found! It really helps the community and people searching for answers a lot. Why don't you accept your own answer to your question? You answered it and deserve some karma!

Thanks again.