All Apps and Add-ons

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

jnraptor
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

mjohnson_splunk
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

mjohnson_splunk
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.

0 Karma

Dmitri_P
Explorer

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.

0 Karma

jnraptor
Explorer

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

0 Karma

Dmitri_P
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

jsilverbears
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

rajesh_rama
Engager

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
Explorer

Updated title

0 Karma

Richfez
SplunkTrust
SplunkTrust

BTW, that's a forward slash.

Forward slash /
Back slash \
0 Karma

Richfez
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

jnraptor
Explorer

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

"\/", "//", "///"
0 Karma
Get Updates on the Splunk Community!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...