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

harshavrath
Contributor

i can't do it now my manager wants to check it now,I will do it on Monday.

0 Karma

aelliott
Motivator

ah yes, then definitely dbmon:mkv, Is it working?

0 Karma

harshavrath
Contributor

I have attributes with VARCHAR2(4000 BYTE) & CLOB data-type.

0 Karma

aelliott
Motivator

delete index, re-create, disable db input, clone db input, change to dbmon:mkv

aelliott
Motivator

multiple lines in a field.

0 Karma

harshavrath
Contributor

If i edit the inputs.conf now will it make any difference or should i delete it & re-index it again.

0 Karma

harshavrath
Contributor

I have multiple lines in my tables,There are 18 attributes in my table.

0 Karma

aelliott
Motivator

do you have fields in your database with multiple lines? If so you will need to use dbmon:mkv

0 Karma

harshavrath
Contributor

If i use DB-Query the data is displayed in the form of tables very clean & crisp,but after indexing its pouring all the data at once without any space.

0 Karma

aelliott
Motivator

for example?

0 Karma

harshavrath
Contributor

the records are indexed into Splunk I'm able to Search them but they are not displayed in proper format as they are displayed when DB-Query is used.

0 Karma

harshavrath
Contributor

the sourcetype is dbmon:kv

0 Karma

aelliott
Motivator

Did you get this working?

0 Karma

aelliott
Motivator

http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring
7. Specify a data Sourcetype.

The following formats are associated with the sourcetypes:

Key-Value format with dbmon:kv sourcetype
Multi-line Key-Value format with dbmon:mkv sourcetype
Template with dbmon:tpl sourcetype
CSV format with CSV sourcetype
Note: If you leave the Sourcetype field blank, the pre-defined sourcetype associated with the format is used.

0 Karma

aelliott
Motivator

what is your sourcetype? dbmon:kv?

0 Karma

harshavrath
Contributor

Thank you its indexing now,but its displaying in the form of paragraph's the field i selected in the settings page for Output Format = Key-Value Format

0 Karma

aelliott
Motivator

to copy your current input, it will run the query initially once again without the {{ AND $rising_column$ > ?}}

0 Karma

harshavrath
Contributor

okay i will try that now,What is the clone button for.?

0 Karma

aelliott
Motivator

That's the issue, you literally need to put {{ AND $rising_column$ > ?}} instead of {{AND CREATED_DT > ?}}

0 Karma

harshavrath
Contributor

This is the Query By means of which I'm trying to index data into SPlunk
SELECT * FROM Table_NM WHERE TRUNC(CREATED_DT) BETWEEN to_date('04-03-2014','mm/dd/yyyy')
AND to_date('04-03-2014','mm/dd/yyyy')AND ROWNUM<=100
{{AND CREATED_DT > ?}}
ORDER BY CREATED_DT ASC

0 Karma

harshavrath
Contributor

When i tried this query in DB_Query I'm getting results
SELECT * FROM Table_NM WHERE TRUNC(CREATED_DT) BETWEEN to_date('04-03-2014','mm/dd/yyyy') AND to_date('04-03-2014','mm/dd/yyyy') AND ROWNUM <= 100

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...