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?

kenkenou
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

michtek
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

kenkenou
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
Get Updates on the Splunk Community!

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...