All Apps and Add-ons

Splunk DB Connect 2: Slash ("/") in SQL query always return 0 rows

Explorer

Splunk 6.2.6, DB Connect 2.1.1

When I run a SQL query with a / inside, Splunk will always return 0 rows. For example, when running against an Oracle DB:

SELECT * FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time >= (SYSDATE-__0.0833__) AND sample_time < (SYSDATE+1)

Returns 700+ rows

SELECT * FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time >= (SYSDATE-__2/24__) AND sample_time < (SYSDATE+1)

Returns 0 rows

Is there a way to escape the / character in the query?

1 Solution

Splunk Employee
Splunk Employee

This is a known issue in DB Connect version 2.1.1 and is fixed in version 2.2.0

http://docs.splunk.com/Documentation/DBX/2.2.0/ReleaseNotes/Releasenotes#Fixed_issues
DBX-2646 Slashes (/) in SQL silently break the query.

View solution in original post

0 Karma

Splunk Employee
Splunk Employee

This is a known issue in DB Connect version 2.1.1 and is fixed in version 2.2.0

http://docs.splunk.com/Documentation/DBX/2.2.0/ReleaseNotes/Releasenotes#Fixed_issues
DBX-2646 Slashes (/) in SQL silently break the query.

View solution in original post

0 Karma

Explorer

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.

0 Karma

Explorer

Running in enterprise environment - I have no access to the filesystem where Splunk is installed.

0 Karma

Explorer

If you have no access to the fily system, try to ask the DB admins to create a view/stored function on the server side and do select * from the view/stored function.

0 Karma

Path Finder

Can you verify that the query is actually being sent to the target database? I am having an similar unresolved issue with using the CONVERT_TZ function. That issue is here: https://answers.splunk.com/answers/386612/splunk-db-connect-2-blocking-mysql-function.html . My query was not even getting the target database. In my case, it was seemingly Splunk DB Connect 2 that was automatically doing this before even sending the query to the DB.

0 Karma

Engager

Try this option in the where clause
where sampletime between sysdate - interval '2' hour and .....
,Try this
where sample
time between sysdate - interval '2' hour and sysdate

Explorer

Updated title

0 Karma

SplunkTrust
SplunkTrust

BTW, that's a forward slash.

Forward slash /
Back slash \
0 Karma

SplunkTrust
SplunkTrust

I don't have an Oracle DB handy, but usually escaping is done with a backslash.

I don't know if this will help or hinder your problem, and for all I know it'll make Oracle choke and your C-level executives cry from the downtime, but you could try

SELECT * FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time >= (SYSDATE-2\/24) AND sample_time < (SYSDATE+1)

When in doubt, you can add two or even three backslashes. Sometimes you have to escape the escape character, and at other times you have to escape them with a double-backslash, making it an escape for the escape to escape. I think.

0 Karma

Explorer

I tried doing the following escape sequences, but none of them worked:

"\/", "//", "///"
0 Karma