How TO Lookup With Database Using DB Connect ( DBX – Part 4 )

How TO Lookup With Database Using DB Connect ( DBX – Part 4 )

In this post we are covering how to a lookup  in the “Splunk DB Connect” application and get the most out of your database with Splunk.

Creating a “Lookup” in “Splunk DB Connect” application
Step-1: Navigate to the “Lookups” page, and click on the“New Lookup” button.

Step-2: Set Reference Search

Search/Saved Search : Select whether you want to write a new search or you want to use a saved search.
Run the search to check the output of your search/saved search, the output of this search will be used to lookup with the database table data.
Once you are satisfied with the output, click on the “Next” button.

Step-3: Set Lookup SQL

Connection :  Select the connection you want to use for
              this lookup.
Catalog : Select the Catalog (if available).
Schema : Select the Schema from your database.
Table : Search for the table/click on the table name,
        the data of which you want to lookup with the
        data in Splunk.
        Here, we are using the  “METHOD_INFO” table.
SQL Editor: You can use this to directly write a SQL 
             query instead.

Click on the “Next” button to proceed to the next page.

Step-4:  Field Mapping 

Search Field Mapping:

Here you have to chose a table’s  column name with which your search result field will lookup.
We have selected “METHOD” as a matching column name from the data base table  which will be matched with “method” field ( event search field )

Lookup Fields :

You have to specify the output field names which will be added as Splunk Fields. In our case we have mentioned  “COUNT” as “method_count”. So in the Splunk Fields we will be able to see as “method_count” instead of “COUNT” ( database table’s column name )

Step-5: Set Properties

Name : Provide a unique name for this lookup.
Description (Optional): Add a description about what
                        this lookup does.
Application: Select the app context.
Summary : Use the command shown here to enrich your 
          searches with the results of this lookup, 
          as required.

Click on the “Finish” button to finish the configuration.
You should get a “Done” screen as shown below.

Step 6: Now run below the command in the Search Head to see the lookup data which coming from the database.

index=_internal 
| dbxlookup lookup="method_lookup" 
| table method,method_count 
| dedup method,method_count

Note : Make sure you have Splunk DB Connect add-on installed 
       on the Search Head to use the dbxlookup command.

Hope, this post was worth a read. If you want to know more about the DB connect configuration and operations please visit the below links.

How To Configure Splunk DB Connect ( DBX – PART 1 )
Sending Data from Database To Splunk Using DB Connect ( DBX – Part 2)
Sending Data from Splunk To Database Using DB Connect ( DBX – Part 3)

Happy Splunking!!