Archive

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

Contributor

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.

0 Karma
1 Solution

Motivator

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.

View solution in original post

Motivator

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.

View solution in original post

Contributor

Thanks a lot Aelliott.

0 Karma

Contributor

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.

0 Karma

Motivator

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.

0 Karma

Contributor

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

0 Karma

Motivator

can you see what events have gone into your new index? You mentioned there were 2?

0 Karma

Contributor

I'm successfully able to run the query in DB-Query & its resulting 30 Records.

0 Karma

Motivator

You may want to make sure your database connection is good then 🙂

0 Karma

Contributor

In the Error-log I'm getting

main:ERROR:Database - Database validation failed for database

0 Karma

Motivator

how many results does your query return when you run it?

0 Karma

Contributor

after clone option there are 2 Events under Database Inputs.

0 Karma

Motivator

is it giving you any errors in splunkd.log?

0 Karma

Contributor

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

0 Karma

Motivator

if you go into the indexes, does it show anything in there as far as # of events?

0 Karma

Contributor

its not working the status is still the same "waiting for Inputs" its not indexing.

0 Karma

Motivator

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"

0 Karma

Contributor

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.

0 Karma

Motivator

you deleted the old index, created the new index, then cloned the input?

0 Karma

Contributor

hey after deleting the old index when i tried to index its not indexing.

0 Karma

Motivator

Did this fix your issue? If so please mark this as answer

0 Karma