Splunk Search

Failure to return values in a database lookup using Splunk DB Connect

wpreston
Motivator

I'm trying to perform a database lookup on the User_ID field in my events but the lookup fails. The User_ID field matches the key (of the same name) in my database table (called User_Information. The User_ID field in the database is a varchar2. The vast majority of its records only contain numeric characters so Splunk appears to think the field is a number, but occasionally some User_ID's contain letters. Typically, I use a query like this to preserve leading 0's when retrieving records and make sure the value passed in is treated as a string:

select * from user-information where User_ID = trim(to_char(*param1*,'000000'))

I've set up a database lookup in Splunk DB Connect, using the Advanced option so I can specify a query to return the results. Here is it's setup in dblookup.conf:

[User_Lookup]
advanced = 1
database = Test_DB_Connect
fields = User_ID,Last_Name
table = User_Information
input_fields = User_ID
query = select Last_Name from user_information where User_ID = trim(to_char($User_ID$,'000000'))

I've tried some variations on the query such as not using the trim and to_char functions or just encasing User_ID in quotes, but they all fail. If I look for a single record, the lookup returns nothing. If I look for multiple user records, I receive a 'Script for lookup table 'User_Lookup' returned error code 1. Results may be incorrect.' message. What am I missing? Is there a way to see where it is failing? I don't see anything in the normal Splunk logs... Any advice is appreciated!

1 Solution

wpreston
Motivator

I finally got this working, and it turned out to be something very, very simple. The tables and fields in my database are all in upper case, and our database (Oracle 10g) generlly doesn’t care about case when it is queried. The fields I’ve defined in Splunk have the same names as column names in the database but are in Camel Case.

In the “Lookup Fields” section of my lookup, I had defined the first field (input field/key) in Camel Case, and then down in the “Advanced lookup settings” section, I had also defined the “Input Field” in Camel Case. It appears that, for this lookup to work, I need to define the key in the “Lookup Fields” section in UPPER CASE to match my database and define the input field in the “Advanced lookup settings” section in Camel Case to match the Splunk field. Once I figured out the right combination to put into the Advanced Lookup, it worked!

I don't know if this is the same problem you're encountering or not, jdunlea_splunk, but you might take a second look at your advanced lookups and see it this has anything to do with it.

View solution in original post

0 Karma

wpreston
Motivator

I finally got this working, and it turned out to be something very, very simple. The tables and fields in my database are all in upper case, and our database (Oracle 10g) generlly doesn’t care about case when it is queried. The fields I’ve defined in Splunk have the same names as column names in the database but are in Camel Case.

In the “Lookup Fields” section of my lookup, I had defined the first field (input field/key) in Camel Case, and then down in the “Advanced lookup settings” section, I had also defined the “Input Field” in Camel Case. It appears that, for this lookup to work, I need to define the key in the “Lookup Fields” section in UPPER CASE to match my database and define the input field in the “Advanced lookup settings” section in Camel Case to match the Splunk field. Once I figured out the right combination to put into the Advanced Lookup, it worked!

I don't know if this is the same problem you're encountering or not, jdunlea_splunk, but you might take a second look at your advanced lookups and see it this has anything to do with it.

0 Karma

ziegfried
Influencer

There have been a few changes to the advanced database lookup functionality in DB Connect 1.0.7 and it should probably work now.

0 Karma

jdunlea_splunk
Splunk Employee
Splunk Employee

I have the same problem here... there are no errors in that "Recent DB Connect Errors" search and I still get an error code of 1 saying "results may be incomplete". I can successfully do a SQL query using dbquery from the table in question, however when I try run my lookup it does not work.

My database lookup seems to be set up correctly.

Is there another bug here with this?

wpreston
Motivator

Thanks for your reply Ziegfried. I ran that saved search and it returned no errors. I also ran the other two saved searches. The Java Bridge saved search returned nothing, and the dbx debug log returned a bunch of 'keep alive successful' messages. What else can I try?

0 Karma

ziegfried
Influencer

In DB Connect there is a saved search called "Recent DB Connect errors". Please take a look if you can spot any error messages that might be related.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...