Why Splunk DB Connect is not properly importing data in Splunk after configuring an Oracle database input?

[gajananh999]

Dear All,

I'm working on splunk db connect app I have configured database input to one oracle database. The table is very big with more than 10 fields and there is more data in each field. When I searched this data, I can see events are properly indexed into splunk. One row of the database has become multiple events is splunk.

What might be the problem?

I have tried applying
[yoursourcetypehere]
TRUNCATE = 0
MAX_EVENTS = 10000
But no luck could anyone me out here as soon as possible.

Thanks
Gajanan Hiroji

[theouhuios · Accepted solution]

I faced this issue too with rows which have very big values. Instead of trying with props.conf I actually created a template for them which will get the data in a different format Eg: the format which I used . You can place the field which is the very big in the middle of the template to avoid them breaking. Ofcourse, if your event has more than 10k characters, you might want to increase the TRUNCATE value.

$STARTTIME$| $OwnerName$ | $RoleName$ | $TargetUserName$ | $DBUserName$ | $TargetLoginName$ | $textdata$

[gajananh999]

Could you please help me out here where i have to do this?
This is my table shema.

column_name         type    nullable    size    decimal_digits  radix   

1 ID NUMBER NO 10 0 10

2 TYPE VARCHAR2 NO 255 0 10

3 PRECIS VARCHAR2 YES 4000 0 10

4 ORIGIN_ENV VARCHAR2 NO 255 0 10

5 ORIGIN_DT TIMESTAMP(6) NO 11 6 10

6 LAST_DT TIMESTAMP(6) NO 11 6 10

7 LAST_ACTION VARCHAR2 YES 255 0 10

8 DETAIL CLOB YES 4000 0 10

9 COUNT NUMBER NO 10 0 10

10 COMMENTS CLOB YES 4000 0 10

[theouhuios]

When you define an input there is an option of selecting what type of Output Format you want. You must have selected kv . Instead of that select Template and for all the columns in your outputs create a template.

For a STARTTIME field , you can have it as $STARTTIME$ in the template. You can use | or a space or any other way you want to define the separation of fields. I used | as its easy to write rex later on it. If you get stucks, post your input here. But should be pretty straight forward. Use the output fields as $$ in the template.

[gajananh999]

Hello.

Here is the inputs.conf details.

[dbmon-tail://qsync_svt/testingqsync]
host = usadac
index = test
output.format = template
output.timestamp = 1
sourcetype = testingqsync
table = QSYNC_APP.Q_MESSAGE_DETAIL_EXC
tail.rising.column = ID
output.template = $STARTTIME$| $ID$ | $TYPE$ | $PRECIS$ | $ORIGIN_ENV$ | $ORIGIN_DT$ | $LAST_DT$ | $LAST_ACTION$ | $DETAIL$ | $COUNT$ | $COMMENTS$
still am not able to get the result properly.

Thanks
Gajanan Hiroji

[theouhuios]

Whats the query? Can you post the output you expect it to be as

[theouhuios]

Also I see the rising.column as ID. You sure you dont want it to be the time? Also add this to the input

output.timestamp.column = STARTTIME

[gajananh999]

I trying to pull all the data from the table so i am mentioned table name there.

[theouhuios]

Is there no time stamp? Starttime was actually an example. If no timestamp and you want to use ID as rising.column then remove the $STARTTIME$ from your output. If you have timestamp then use that field

[gajananh999]

Hello,

I am able to see the data into splunk now thanks

Thanks
Gajanan Hiroji

[gajananh999]

Hello.
here is my new inputs.conf file

[dbmon-tail://qsync_svt/testingqsync]
host = usadac
index = test
output.format = template
output.timestamp = 1
sourcetype = testingqsync1
table = QSYNC_APP.Q_MESSAGE_DETAIL_EXC
tail.rising.column = ID
output.template = $ID$ | $TYPE$ | $PRECIS$ | $ORIGIN_ENV$ | $ORIGIN_DT$ | $LAST_DT$ | $LAST_ACTION$ | $DETAIL$ | $COUNT$ | $COMMENTS$
interval = auto

using this not able to index data into splunk

[theouhuios]

Format which works for me

[dbmon-tail:///*]
host = abc
output.format = template
output.timestamp = 1
output.timestamp.column = STARTTIME
query = abcd
sourcetype = mssqlserver
tail.rising.column = STARTTIME
interval = 15
table =xyz
disabled = 0
output.template = $STARTTIME$| $START_TIME$ | $ENDTIME$ | $EventClass$ | $NTUserName$ | $LoginName$ | $SPID$ | $ObjectName$ | $HostName$ | $ClientProcessID$ | $ApplicationName$ | $EventSubClass$ | $ObjectID$ | $Success$ | $ServerName$ | $ObjectType$ | $NestLevel$ | $State$ | $Error$ | $Mode$ | $DatabaseName$ | $FileName$ | $OwnerName$ | $RoleName$ | $TargetUserName$ | $DBUserName$ | $TargetLoginName$ | $textdata$
index = 123

[gajananh999]

Hello.
But there is no column in table called STRATTIME.

[gajananh999]

I am getting key value pair values in splunk here are the details of regex.

[dbmon:kv]
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]+)