Splunk Search

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

harshavrath
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

aelliott
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

aelliott
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

harshavrath
Contributor

Thanks a lot Aelliott.

0 Karma

harshavrath
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

aelliott
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

harshavrath
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

aelliott
Motivator

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

0 Karma

harshavrath
Contributor

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

0 Karma

aelliott
Motivator

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

0 Karma

harshavrath
Contributor

In the Error-log I'm getting

main:ERROR:Database - Database validation failed for database

0 Karma

aelliott
Motivator

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

0 Karma

harshavrath
Contributor

after clone option there are 2 Events under Database Inputs.

0 Karma

aelliott
Motivator

is it giving you any errors in splunkd.log?

0 Karma

harshavrath
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

aelliott
Motivator

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

0 Karma

harshavrath
Contributor

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

0 Karma

aelliott
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

harshavrath
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

aelliott
Motivator

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

0 Karma

harshavrath
Contributor

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

0 Karma

aelliott
Motivator

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

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!