topic Re: Splunk DB Connect 2: Slash ("/") in SQL query always return 0 rows in All Apps and Add-ons

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

jnraptor — Mon, 18 Jan 2016 23:04:04 GMT

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?

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

Richfez — Tue, 19 Jan 2016 03:10:44 GMT

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.

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

Richfez — Tue, 19 Jan 2016 03:12:18 GMT

BTW, that's a forward slash.

Forward slash /
Back slash \

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

jnraptor — Tue, 19 Jan 2016 04:07:02 GMT

Updated title

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

jnraptor — Tue, 19 Jan 2016 04:10:29 GMT

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

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

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

rajesh_rama — Tue, 29 Sep 2020 08:50:52 GMT

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

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

jsilverbears — Thu, 31 Mar 2016 20:51:04 GMT

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.

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

Dmitri_P — Tue, 29 Sep 2020 09:26:40 GMT

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.

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

jnraptor — Thu, 05 May 2016 21:38:38 GMT

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

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

Dmitri_P — Fri, 06 May 2016 07:26:01 GMT

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.

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

mjohnson_splunk — Thu, 12 May 2016 01:06:19 GMT

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.