topic Re: Splunk DB Connect: extremely long SQL query doesn't get run in Splunk Search

Splunk DB Connect: extremely long SQL query doesn't get run

Paolo_Prigione — Thu, 11 Jul 2013 17:17:33 GMT

Hi splunkers! I have a query which is just under 10k characters long that cannot be run through DB Connect's dbquery command. Has anybody had any similar issues? Do you have any workarounds?

-More info-

The DB is Oracle. When run from SQLDeveloper, the query completes in less than 1s with one or two results at most.
The dbquery command resides in a simpleXML form panel and it takes some parameters from the form itself. The splunk interface reports an error "PARSER: Applying intentions failed Splunkd daemon is not responding: ('The read operation timed out',)". The other panels (regular splunk searches and charts) load just fine.
If I run the same query in the DB Connect's dbquery dashboard, it doesn't work either.
DB Connect logs show no information about that particular query.
DB Connect version 1.09

Thanks, Paolo

Re: Splunk DB Connect: extremely long SQL query doesn't get run

davebrooking — Wed, 17 Jul 2013 14:13:47 GMT

Do you think it's an issue with the length of the query string? Or could it be the query just takes too long to run? How long does it take to run when executed directly against the Oracle DB, i.e. taking Splunk and DB Connect out of the equation?

If you think it's an issue with the length of the search string and your DBAs will allow you to create a view in the database, then I believe you could move most, if not all, of the static portion of your query into the view and reduce the length of the query your passing via DB Connect. However without any understanding of your actual query I may be totally wrong

For example if you're current query is

SELECT t1.col1,
       t1.col1...,
       t2.col1,
       ...,
       tn.coln
FROM   tab1 t1
       INNER JOIN 
       tab2 t2 ON t1.col1 = t2.col1
       LEFT OUTER JOIN
       ...
       tabn tn ON t1.coln = tn.col1
WHERE  t1.col1 = tn.col5
AND    t1.col4 = $dropdown1$
AND    tn.coln = $dropdown2$

You could create a view in Oracle using something like

CREATE VIEW paolo_view AS
SELECT t1.col1,
       t1.col1...,
       t2.col1,
       ...,
       tn.coln
FROM   tab1 t1
       INNER JOIN 
       tab2 t2 ON t1.col1 = t2.col1
       LEFT OUTER JOIN
       ...
       tabn tn ON t1.coln = tn.col1
WHERE  t1.col1 = tn.col5;

and your query string from DB Connect would become something like

SELECT *
FROM   paolo_view
WHERE  t1.col4 = $dropdown1$
AND    tn.coln = $dropdown2$

Re: Splunk DB Connect: extremely long SQL query doesn't get run

Paolo_Prigione — Wed, 17 Jul 2013 16:34:28 GMT

Hi Dave, I believe it is an issue of query length (in characters): when run on a regular SQL client it executes in less than 1 second. I have thought to create a view but the approach is not possible as the query is actually a lookup (I pass in parameters using Splunk's $field$ synstax). Other, shorter, queries run just fine on the same DB. Thanks

Re: Splunk DB Connect: extremely long SQL query doesn't get run

JovanMilosevic — Thu, 18 Jul 2013 08:11:46 GMT

Hi Paolo, In which of the SQL clauses do you pass in parameters using the Splunk $field$ syntax?

Re: Splunk DB Connect: extremely long SQL query doesn't get run

rroussev — Mon, 22 Jul 2013 21:39:21 GMT

The limit is highly dependent on the operating system type and version. As currently the query is passed as an argument, it is governed by the OS limitations of the particular platform, for example:
http://linux.die.net/man/2/execve
http://support.microsoft.com/kb/830473

Re: Splunk DB Connect: extremely long SQL query doesn't get run

ryantzj — Tue, 23 Jun 2015 02:41:03 GMT

HI Paolo, I am having a very similar situation over here, any work around for this issue ?