All Apps and Add-ons

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

gajananh999
Contributor

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

0 Karma
1 Solution

theouhuios
Motivator

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$

View solution in original post

theouhuios
Motivator

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
Contributor

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

0 Karma

theouhuios
Motivator

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
Contributor

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

0 Karma

theouhuios
Motivator

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

0 Karma

theouhuios
Motivator

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
0 Karma

gajananh999
Contributor

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

0 Karma

theouhuios
Motivator

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

0 Karma

gajananh999
Contributor

Hello,

I am able to see the data into splunk now thanks

Thanks
Gajanan Hiroji

0 Karma

gajananh999
Contributor

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

0 Karma

theouhuios
Motivator

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

0 Karma

gajananh999
Contributor

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

0 Karma

gajananh999
Contributor

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

[dbmon:kv]
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]+)
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 ...