All Apps and Add-ons

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

cpatacsil
Explorer

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

SELECT
(SELECT COUNT()
FROM Request_Table
WHERE Closed = 'Yes'
AND System = 'PlainOld_Source'
**AND TRUNC(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(60*60*24))) = TRUNC(sysdate-1)
*
) AS PlainOld_Alias,
(SELECT COUNT()
FROM Request_Table
WHERE Closed = 'Yes'
**AND TRUNC(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(60*60*24))) = 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

jcoates_splunk
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

Dmitri_P
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
$SPLUNK_HOME/etc/apps/splunk_app_db_connect/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.

cpatacsil
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 Request_Table
WHERE Closed = 'Yes'
AND System = 'PlainOld_Source'
AND TRUNC(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(60*60*24))) = TRUNC(sysdate-1)
) AS PlainOld_Alias,
(SELECT COUNT()
FROM Request_Table
WHERE Closed = 'Yes'
AND TRUNC(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(60*60*24))) = TRUNC(sysdate-1)
AND Status ='Closed'
AND System = 'DSL'
) AS Connection_Source,
trunc(sysdate-1) Automatic_ClosedDate
FROM dual

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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

cpatacsil
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...