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.
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.
and you have {{ AND $rising_column$ > ?}} in your input query?
In DB-Query I'm able to get proper results
What is coming back from your query as far as column names, are they
I'm getting this Error in the Recent DB Errors
I'm getting an error as
Configuration Error: Invalid query "THE QUERY IS WRITTEN"
without proper {{ ... $rising_column$ > ?}} pattern!
Yes, I have order by that field too, so then it will not import anymore data since the rising_column will be the last date in your selection.
No, it will automatically be replaced with the last value of DATE_FIELD returned from the result set.
what about the Question_Mark ? should we replace it with any field..?
the rising_column=DATE_FIELD attribute right
Should be ok, splunk simply sends the query at the database and takes in the result set and may format it as epoch when storing it in it's index so that it will work with _time
no in DB its an Date data-type
Is it stored in the database as epoch format? If splunk is converting it, i see no issue with that.
will it cause a problem as the date returned is Epoch format when i do a select * from Table_name
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
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.
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")
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
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')
i tried I'm getting an Error as Invalid Month.
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'
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.
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.