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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...