All Apps and Add-ons

This query runs in Splunk DB Connect 1, but why does it not work in Splunk DB Connect 2?

Explorer

The query below is running in Splunk DB Connect version 1 but not in version 2.

SELECT
(SELECT COUNT()
FROM RequestTable
WHERE Closed = 'Yes'
AND System = 'PlainOld
Source'
*
AND TRUNC(TODATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(606024))) = TRUNC(sysdate-1)**
) AS PlainOld
Alias,
(SELECT COUNT()
FROM Request_Table
WHERE Closed = 'Yes'
*
AND TRUNC(TODATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(606024))) = TRUNC(sysdate-1)**
AND Status ='Closed'
AND System = 'DSL'
) AS Connection
Source,
trunc(sysdate-1) Automatic_ClosedDate
FROM dual

I tried to dissect the query and I found out that on the condition in bold caused the error. Please let me know your thoughts on converting this to be compatible in DB Connect version 2 Splunk app. Thank you!

1 Solution

Splunk Employee
Splunk Employee

this is probably due to changes in the JDBC driver required to run on Java 8?

View solution in original post

Explorer

DB Connect v2 doesn't accept a slash in a SQL query because this sign is defined as reserved in urllib.py file. For resolution of the issue see my comment in a similar topic (https://answers.splunk.com/answers/341611/splunk-db-connect-2-slash-i... please:

To resolve the issue, you should edit
$SPLUNKHOME/etc/apps/splunkappdbconnect/bin/dbxquery.py:
change the line (usually its number is 123)
query = urllib.quote(query)
to
query = urllib.quote(query, safe='')

Don't forget to make a backup copy before editing. Take care to keep changes after upgrades.

Explorer

Thank you for your answer and it worked. But I have another question, this Select * FROM DUAL query is not working but the inside SELECT statements are now working

SELECT
(SELECT COUNT()
FROM RequestTable
WHERE Closed = 'Yes'
AND System = 'PlainOld
Source'
AND TRUNC(TODATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(606024))) = TRUNC(sysdate-1)
) AS PlainOld
Alias,
(SELECT COUNT()
FROM RequestTable
WHERE Closed = 'Yes'
AND TRUNC(TO
DATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(606024))) = TRUNC(sysdate-1)
AND Status ='Closed'
AND System = 'DSL'
) AS ConnectionSource,
trunc(sysdate-1) Automatic
ClosedDate
FROM dual

0 Karma

Splunk Employee
Splunk Employee

this is probably due to changes in the JDBC driver required to run on Java 8?

View solution in original post

Explorer

Thank you for your answer. I am still validating if this really caused the issue.

0 Karma