topic Splunk DB Connect: Is it possible to specify a SQL Query by running an inner join query to use as the database input? in Splunk Search

Splunk DB Connect: Is it possible to specify a SQL Query by running an inner join query to use as the database input?

KindaWorking — Mon, 12 Jan 2015 22:18:37 GMT

In the DB Connect app, when I try to add a Database Input, instead of selecting a Table Name I would like to Specify the SQL Query.
I want to run a very simple join query to use as the input. Is this possible? If so, what am I doing wrong:

select Documents.tmpname, DocumentStatistics.*
From DocumentStatistics
INNER JOIN Documents
On DocumentStatistics.image_id=Documents.image_id;

I am getting the following error when I try to do this (it works fine if I just select the table):

01-13-2015 11:53:46.206 +1100 ERROR AdminManagerExternal - Received malformed XML from external handler:\nFailed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://Database/New Data: [New Data] Invalid query " select Documents.tmpname, DocumentStatistics.*\r\r\n From DocumentStatistics\r\r\n INNER JOIN Documents\r\r\n On DocumentStatistics.image_id=Documents.image_id;" without proper {{ ... $rising_column$ > ?}} pattern! with query = ...

Re: Splunk DB Connect: Is it possible to specify a SQL Query by running an inner join query to use as the database input?

musskopf — Mon, 12 Jan 2015 23:33:31 GMT

You should be able to do that, the only thing might preventing you is a duplicated column name. Can you see any errors inside dbx.log file?

Re: Splunk DB Connect: Is it possible to specify a SQL Query by running an inner join query to use as the database input?

KindaWorking — Tue, 13 Jan 2015 01:04:52 GMT

Thanks musskopf, I have added the error logs to the question

Re: Splunk DB Connect: Is it possible to specify a SQL Query by running an inner join query to use as the database input?

KindaWorking — Tue, 13 Jan 2015 02:20:07 GMT

The answer is that I forgot to append the following to my query:
{{WHERE $rising_column$ > ?}}

Now all working. Thanks Musskopf for setting me in the right direction.

Re: Splunk DB Connect: Is it possible to specify a SQL Query by running an inner join query to use as the database input?

enriquemr — Thu, 09 Jul 2015 21:37:17 GMT

Thanks, have you had a problem with the date in your table? because i have a problen with that, the format is diferente when i import the data of my data bases within splunk.
regards.