Splunk Search
Highlighted

db connect lookup fails with table is invalid

Engager

To use a flat file lookup table is easy - simply create (say) a CSV file and use it with the search app syntax
| inputlookup test.csv

but if I go to
Apps -> Lookups -> database lookups and create a new DB lookup...
In this case a database table exists in an MS SQL database, local to the install of splunk (same machine)
HIP7_SignameXlate

I can get the GUI to query the DB (called ePO) to fill all the columns and save it. This suggests that the permissions etc are fine, and the DB connect app can read the table colums.

Additionally, I can read data using a db tail, proving that the system returns data via the DB CONNECT app.

However, the lookup table does not work. I get the following error:

| INPUTLOOKUP dbo.HIP7SigNameXlate
The lookup table 'dbo.HIP7
SigNameXlate' is invalid.

I know the spelling is correct as this is a cut and paste.

Any ideas how to troubleshoot this?

Thanks

Tags (4)
Highlighted

Re: db connect lookup fails with table is invalid

Communicator

check here: http://docs.splunk.com/Documentation/DBX/1.0.8/DeployDBX/dblookupspec
Also, using a lookup instead of a inputlookup lets you find something?

0 Karma
Highlighted

Re: db connect lookup fails with table is invalid

Engager

No - the lookup does not work... which is why I tried something easy. i.e. inputlookup

0 Karma
Highlighted

Re: db connect lookup fails with table is invalid

Influencer

Database lookups are actually scripted lookups in Splunk, so they cannot be used with the inputlookup command, which only supports file based lookups. You can use the dbquery command for a similar purpose, you just have to specify the full SQL query instead of the lookup name.

In order to find out the problem with using the database lookup with the lookup command, could you please provide some more information? (Lookup configuration, example searches you've tried, error messages, parts of dbx.log generated for the executed lookup)

View solution in original post

Highlighted

Re: db connect lookup fails with table is invalid

Explorer

Thanks. Once one realises that inputlookup won't return results, troubleshooting leads to results. | lookup from a db source works fine. Just have to be sure the data is there and the syntax is right.

0 Karma