Archive
Highlighted

DBConnect Lookup with joined tables

SplunkTrust
SplunkTrust

I have the DBConnect app successfully connected to an Oracle DB and I can successfully perform ad-hoc queries. However, I am unable to figure out how to use a DB lookup using the advanced options to use the following SQL query:

SELECT c.chan_id, c.inst_id, i.inst_name from TempTable.chan c, TempTable.inst i WHERE c.inst_id = i.inst_id ORDER BY c.inst_id

I defined the lookup in Manager >> Lookups >> Database Lookups, Advanced lookup settings. Whether I specify the chanid, instid and instname columns as Lookup Fields and chanid as an Input Field I get the following error when I try using it in a search or with '|inputlookup tablename':

The lookup table 'tablename' is invalid.

What am I missing?

0 Karma
Highlighted

Re: DBConnect Lookup with joined tables

Influencer

Database lookups (and actually all scripted lookups) cannot be used with the inputlookup command. You can still get similar results by using the dbquery command, though.

One thing I noticed with your query is that you didn't use the input field in your query, which is necessary, though. Something like this is necessary:

SELECT c.chan_id, c.inst_id, i.inst_name from TempTable.chan c, TempTable.inst i WHERE c.inst_id = i.inst_id AND c.chan_id = $chan_id$ ORDER BY c.inst_id

The repacement token $chan_id$ is replaced with the actual lookup value when the lookup is executed.

Alternatively you could create a view for your query and create a simple lookup for this view, which does not require input fields.

0 Karma