All Apps and Add-ons

Splunk DB Connect 2: Why am I getting errors if I set up dblookup in editor mode with a WHERE in the SQL command?

Explorer

If I set up db lookup in editor mode with [where] command, I will get an error when I use the db lookup.
SQL Command

SELECT XXX FROM  WHERE XXX IS NOT NULL

Search

index=test |lookup db_connect_dblookup2 XXX AS XXX

Error

ERROR ScriptRunner - stderr from 'D:\Splunk\bin\PYTHON.EXE D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py dblookup2':  Traceback (most recent call last):
ERROR ScriptRunner - stderr from 'D:\Splunk\bin\PYTHON.EXE D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py dblookup2':    File "D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py", line 114, in 
ERROR ScriptRunner - stderr from 'D:\Splunk\bin\PYTHON.EXE D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py dblookup2':      raise ex
ERROR ScriptRunner - stderr from 'D:\Splunk\bin\PYTHON.EXE D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py dblookup2':  avro.ipc.AvroRemoteException: java.sql.SQLSyntaxErrorException: ora-00907 right parenthesis missing
11-01-2016 10:25:30.522 ERROR LookupOperator - Script for lookup table 'db_connect_dblookup2' returned error code 1.  Results may be incorrect. 

If there is not a where command, it works. Is it a bug?

0 Karma

Explorer

If it's a bug caused by the WHERE clause a possible workaround would be to use your query as a n SQL temporary table:

SELECT * FROM (SELECT XXX FROM  WHERE XXX IS NOT NULL) as tmp
0 Karma

Explorer

thanks for your answer. i tried it, but it got error.

Invalid Query
External search command 'dbxquery' returned error code 1. Script output = "RuntimeError: Failed to run query: "SELECT * FROM (SELECT * FROM (SELECT XXX FROM WHERE XXX IS NOT NULL) AS tmp) t", params: "None", caused by: Exception(' java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis

0 Karma