topic Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query in Splunk Search

Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Wed, 02 Apr 2014 09:56:40 GMT

Hi,

I'm trying to get the DB tables as input into Splunk by using Add DB Inputs in Splunk Manager,
I'm able to index the Data from my table into Splunk when i don't mention the query(the Splunk creates its own query)

But when i mention the query such as this
SELECT * FROM TABLE_NAME {{WHERE ROWNUM <= 30}} I'm unable to index the data into Splunk.

This is very important for me as my tables are very large in size so i can't index them completely i need a Where Condition for this

Any Help is Appreciated,

Thanks.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

richgalloway — Wed, 02 Apr 2014 11:54:29 GMT

What do you mean, exactly, when you say you're unable to index the data? Do you get an error message? Is there anything in dbx.log?

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Mon, 28 Sep 2020 16:17:37 GMT

2014-04-02 07:44:01.283 dbx2674:ERROR:TailDatabaseMonitor - Configuration Error: [DB_NAME] Invalid query "SELECT * FROM TABLE_NAME {{AND UPDATED_DT > to_date (2001-06-01,'YYYY-MM-DD"T"HH:MI:SS')}}" without proper {{ ... $rising_column$ > ?}} pattern!

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Mon, 28 Sep 2020 16:17:40 GMT

2014-04-02 05:21:09.047 dbx6822:ERROR:TailDatabaseMonitor - Configuration Error: [DB_NAME] Invalid query "SELECT * FROM TABLE_NAME {{WHERE ROWNUM <= 10}}" without proper {{ ... $rising_column$ > ?}} pattern!

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Wed, 02 Apr 2014 12:05:00 GMT

the errors are in the Query that we specify when we go indexing the DB data into Splunk,IF i don't provide the Query the complete table will be indexed into Splunk.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

aelliott — Mon, 28 Sep 2020 16:17:46 GMT

I think you want something like
SELECT * FROM TABLE_NAME WHERE ROWNUM <= 30 {{AND $rising_column$ > ?}}

Or if you are not doing "Tail"
SELECT * FROM TABLE_NAME WHERE ROWNUM <= 30

The stuff in the brackets will not be run the first time, so putting {{ where rownum <= 30 }} will exclude this from the first run.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Wed, 02 Apr 2014 13:50:47 GMT

what I'm i supposed to include in a query if i want to index 1000 rows in a specified time range say Jan 14 to Mar 14.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

aelliott — Wed, 02 Apr 2014 13:52:41 GMT

SELECT * FROM TABLE_NAME WHERE DateField <= '01/01/2014 00:00:00' and DateField >= '03/31/2014 23:59:59'

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Wed, 02 Apr 2014 13:52:50 GMT

If i use dbquery Epoch time is returned i was able to convert it using fieldformat-strftime.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Wed, 02 Apr 2014 14:08:45 GMT

I meant the query to be used for indexing shouldn't we use curly braces for where condition.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

aelliott — Wed, 02 Apr 2014 14:10:28 GMT

The stuff in the brackets will not be run the first time the query is run, so if you have curly braces around that, it will grab all your data. the curly braces are meant to hold the {{ $rising_column$ > ?}} pattern

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

aelliott — Wed, 02 Apr 2014 14:16:41 GMT

Also, You may have to re-create your data input if you are changing the query as it will not re-run the first run again, the clone feature works nicely for this.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Wed, 02 Apr 2014 14:25:33 GMT

I deleted the previous Entry that i had created.I need the Query that I'm supposed to use.As there are some millions of Records in DB i can't index them all in Splunk becoz of 500MB limit,So i want to index some 1000 rows which fall in a said time frame.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

aelliott — Wed, 02 Apr 2014 14:28:27 GMT

Right, have you tried any of the queries i have suggested? such as one like
SELECT TOP 1000 * FROM TABLE_NAME WHERE DateField <= '01/01/2014 00:00:00' and DateField >= '03/31/2014 23:59:59'

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Wed, 02 Apr 2014 14:31:33 GMT

i tried I'm getting an Error as Invalid Month.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

aelliott — Mon, 28 Sep 2020 16:17:51 GMT

try this:
SELECT TOP 1000 * FROM TABLE_NAME
where start_date between to_date('01-JAN-14 00:00:00')
and to_date('31-MAR-14 23:59:59')

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Thu, 03 Apr 2014 06:38:48 GMT

Hi,I'm getting this error when i tried the above query
command="dbquery", A database error occurred: ORA-01830: date format picture ends before converting entire input string

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Mon, 28 Sep 2020 16:18:24 GMT

Hi,I'm getting results when i tried the below query

| dbquery DB_NAME limit=100 "SELECT * FROM TABLE_NAME WHERE TRUNC(DATE_FIELD) BETWEEN to_date('04-03 2014','mm/dd/yyyy') AND to_date('04-03 -2014','mm/dd/yyyy') "|fieldformat DATE_FIELD=strftime(DATE_FIELD,"%d-%m-%Y %H: %M: %S")

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

harshavrath — Thu, 03 Apr 2014 10:25:36 GMT

But I'm still unable to figure out what is the Select Query that I am supposed to use while Indexing a table into Splunk.

Re: Splunk DBConnect Add Databse Inputs Unable to index when we specify the Query

aelliott — Mon, 28 Sep 2020 16:18:29 GMT

If you are definining a rising column..
SELECT * FROM TABLE_NAME WHERE TRUNC(DATE_FIELD) BETWEEN to_date('04-03 2014','mm/dd/yyyy') AND to_date('04-03 -2014','mm/dd/yyyy') {{AND $rising_column$ > ?}}
ORDER BY DATE_FIELD ASC