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!
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.
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.
There have been a few changes to the advanced database lookup functionality in DB Connect 1.0.7 and it should probably work now.
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?
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?
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.