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

and you have {{ AND $rising_column$ > ?}} in your input query?

0 Karma

harshavrath
Contributor

In DB-Query I'm able to get proper results

0 Karma

aelliott
Motivator

What is coming back from your query as far as column names, are they . ? When running in db query

0 Karma

harshavrath
Contributor

I'm getting this Error in the Recent DB Errors

0 Karma

harshavrath
Contributor

I'm getting an error as
Configuration Error: Invalid query "THE QUERY IS WRITTEN"
without proper {{ ... $rising_column$ > ?}} pattern!

0 Karma

aelliott
Motivator

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.

0 Karma

harshavrath
Contributor

what about the Question_Mark ? should we replace it with any field..?

0 Karma

harshavrath
Contributor

the rising_column=DATE_FIELD attribute right

0 Karma

aelliott
Motivator

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

0 Karma

harshavrath
Contributor

no in DB its an Date data-type

0 Karma

aelliott
Motivator

Is it stored in the database as epoch format? If splunk is converting it, i see no issue with that.

0 Karma

harshavrath
Contributor

will it cause a problem as the date returned is Epoch format when i do a select * from Table_name

0 Karma

aelliott
Motivator

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

0 Karma

harshavrath
Contributor

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.

0 Karma

harshavrath
Contributor

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")

0 Karma

harshavrath
Contributor

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

0 Karma

aelliott
Motivator

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')

0 Karma

harshavrath
Contributor

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

0 Karma

aelliott
Motivator

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'

0 Karma

harshavrath
Contributor

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.

0 Karma

aelliott
Motivator

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.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...