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

[jnraptor]

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?

[mjohnson_splunk · Accepted solution]

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.

[Dmitri_P]

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.

[jnraptor]

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

[Dmitri_P]

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.

[jsilverbears]

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.

[rajesh_rama]

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

[jnraptor]

Updated title

[Richfez]

BTW, that's a forward slash.

Forward slash /
Back slash \

[Richfez]

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.

[jnraptor]

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

"\/", "//", "///"