Splunk DB Connect 2: After upgrading Splunk to 6.4.1, why is a "select * from" statement added to our query?

[jhoang]

Hi Support,

So currently we are trying to execute a DB query, however, you have added a select * from statement to our query. After the upgrade to version Splunk 6.4.1 Windows X64, we noticed this change to our DB Connect 2

Query we are trying to run:

declare @dt datetime
select @dt = dateadd(SECOND, -300, getdate())
SELECT OrderID, UserID, ConditionStateID, LogicalOperatorID, OC_ActivationTime, OC_StartTime, OC_EndTime, OC_ExpirationDate, OC_IsActive, OC_CreationDate, OC_DmlDate, OC_DmlUserID 
FROM UBER.dbo.OrderCondition
where OC_CreationDate > @dt

Invalid Query message:

External search command 'dbxquery' returned error code 1. Script output = "RuntimeError: Failed to run query: "SELECT * FROM (declare @dt datetime select @dt = dateadd(SECOND, -300, getdate()) SELECT OrderID, UserID, ConditionStateID, LogicalOperatorID, OC_ActivationTime, OC_StartTime, OC_EndTime, OC_ExpirationDate, OC_IsActive, OC_CreationDate, OC_DmlDate, OC_DmlUserID FROM UBER.dbo.OrderCondition where OC_CreationDate > @dt) t", caused by: AvroRemoteException(u"com.sybase.jdbc4.jdbc.SybSQLException: Incorrect syntax near the keyword 'declare'.\n",). "

This is happening for all of our DB input queries now - can you kindly advise?

Jacks

[jhoang · Accepted solution]

Hi all, this item can be marked as resolved.

There was an issue with the /splunk_app_db_connect/bin/dbx2/query_builder.py

We had to manually remove the 2 string, in order to allow the DB query to run.

[bgriffis]

So does anyone have an official word on WHY splunk made the query_builder.py have that goofy fmt?

def build_inline_view_query(query):
    #fmt = "SELECT * FROM (%s) t"
    fmt = "%s"
    return fmt % query

And what's the trouble in making it just "%s" ?

[mendesjo]

Didn't work for me..

[vu_le]

Hi jhoang,

Are you able to share which two strings you needed to remove in order to allow the DB query to run?

[joao_amorim]

You need to delete the SELECT * FROM and the t at the end.

I think that it's supposed to remain only the %s which is the query.

[jhoang]

my teammate made the changes, I am not too sure.