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.
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.
Thanks a lot Aelliott.
its working now the reason that it did not work the previous time is, i used CREATED_DT field twice i mean in the query as to_char(CREATED_DT,''YYYY-MM-DD HH24:MI:SS') & used the same field for Time-Stamp(Increasing Value) so it was giving an Error.
if it says your database validation failed, it may have attempted to run the query the first time and failed. I would suggest trying again 🙂 Especially since that same query was able to get data previously.
Sorry nothing is indexed till now,i meant there are 2 Inputs under DB-Inputs.
1 dbmon-tail://DB_NM/Table_NM
2 dbmon-tail://DB_NM/Table_NM
can you see what events have gone into your new index? You mentioned there were 2?
I'm successfully able to run the query in DB-Query & its resulting 30 Records.
You may want to make sure your database connection is good then 🙂
In the Error-log I'm getting
main:ERROR:Database - Database validation failed for database
how many results does your query return when you run it?
after clone option there are 2 Events under Database Inputs.
is it giving you any errors in splunkd.log?
the query that i used is
SELECT (some 18 attributes) to_char(CREATED_DT, 'DD-MON-YYYY HH24:MI:SS AM')FROM table_NM WHERE ROWNUM<=30 AND TRUNC(CREATED_DT) BETWEEN to_date('04-08-2014','mm/dd/yyyy') AND to_date('04-08-2014','mm/dd/yyyy') {{AND $rising_column$ > ?}} ORDER BY CREATED_DT ASC
if you go into the indexes, does it show anything in there as far as # of events?
its not working the status is still the same "waiting for Inputs" its not indexing.
You need to create a new index.
You will have to re-create your input so that it re-indexes. Disable the input, hit the clone button, Change to dbmon:mkv (if you haven't already) and make sure it says " Multiline Key-Value based" instead of "Key-Value based"
i did not use the clone option,I deleted the index from the inputs page after that i even deleted the folder where my indexed data was placed.
you deleted the old index, created the new index, then cloned the input?
hey after deleting the old index when i tried to index its not indexing.
Did this fix your issue? If so please mark this as answer