DB input configuration setup for oracle database

[gudli618]

Hi ,
I recently installed DB Connect and I am setting up a new DB input to index db space used in Oracle. Please see the attached setup. The index has been enabled but I don't see it capturing any data. I am able to run the query in DB query successfully and also in dbx.log I can see successful execution without any errors. Can someone please advise if there are any issues with the config setup.
Query used:
select df.tablespace_name "Tablespace", totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB", round(100 * ( (tu.totalusedspace)/ df.totalspace)) "Pct. Util"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name

[pmdba]

From what you have posted, it looks like you may have line breaks in your query for readability. Make sure that your query is on a single, unbroken line in your inputs.conf file.

[gudli618]

query = select df.tablespace_name "Tablespace", totalusedspace "UsedMB",\r\n(df.totalspace - tu.totalusedspace) "FreeMB",\
r\ndf.totalspace "TotalMB", round(100 * ( (tu.totalusedspace)/ df.totalspace)) "Util"\r\nfrom\r\n(select tablespace_name,\
r\nround(sum(bytes) / 1048576) TotalSpace\r\nfrom dba_data_files \r\ngroup by tablespace_name) df,\r\n(select round(sum(by
tes)/(1024*1024)) totalusedspace, tablespace_name\r\nfrom dba_segments \r\ngroup by tablespace_name) tu\r\nwhere df.tables
pace_name = tu.tablespace_name\r\n\r\n

[gudli618]

Also I changed the configs a bit and moved to template based output. Here is the new config:

[script://./bin/jbridge_server.py]
disabled = 0

[batch:///apps/splunk/var/spool/dbmon/*.dbmonevt]
crcSalt =
disabled = 0
move_policy = sinkhole
sourcetype = dbmon:spool

[dbmon-dump://SPGNYT02/SPGNYT02_dbspace_input]
host = spgny-uat2.intranet.barcapint.com
index = gudlitest
output.format = template
output.timestamp = 1

interval = 1 * * * *
table = SPGNYT02_dbspace_input
disabled = 0
output.template = Tablespace UsedMB FreeMB Util% from $HOST$ at $timestamp$
sourcetype = dbmon:mkv

[gudli618]

Sorry for the late response. splunkd log has no errors neither does dbx.log. Infact on dbx.log I can see dbmonevt files being created and resultcount retrieved.
dbx179:INFO:SpoolOutputChannel - Moving temporary file /apps/splunk/var/run/tmp/dbx/mkv_39801.dbmonevt with size=1460 to destination /apps/splunk/var/spool/dbmon/mkv_1393880462148984821.dbmonevt
dbx179:INFO:DumpDatabaseMonitor - Database monitor=[dbmon-dump://SPGNYT02/SPGNYT02_dbspace_input] finished with status=true resultCount=10 in duration=2146 ms

Jus that when I search on the index no data is available. Could you please advise.

[araitz]

I agree, it is likely that splunkd.log will tell you that it is having problems assigning a timestamp, especially if the format is odd or some or all of the row-level timestamps are more than a few years in the past or a few months in the future.

[lukejadamec]

Have you checked the splunkd log for this input?
I'm thinking you'll probably see that splunkd has timestamp issues.

[gudli618]

query = select df.tablespace_name "Tablespace", totalusedspace "Used MB",\r\n(df.totalspace - tu.totalusedspace) "Free MB"
,\r\ndf.totalspace "Total MB", round(100 * ( (tu.totalusedspace)/ df.totalspace)) "Pct. Util"\r\nfrom\r\n(select tablespac
e_name,\r\nround(sum(bytes) / 1048576) TotalSpace\r\nfrom dba_data_files \r\ngroup by tablespace_name) df,\r\n(select roun
d(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name\r\nfrom dba_segments \r\ngroup by tablespace_name) tu\r\nwhere d
f.tablespace_name = tu.tablespace_name\r\n
interval = 1 * * * *
table = SPGNYT02_dbspace_input
disabled = 0

[gudli618]

Thanks for checking. Please see below
/apps/splunk/etc/apps/dbx/local > more inputs.conf
[script://./bin/jbridge_server.py]
disabled = 0

[batch:///apps/splunk/var/spool/dbmon/*.dbmonevt]
crcSalt =
disabled = 0
move_policy = sinkhole
sourcetype = dbmon:spool

[dbmon-dump://SPGNYT02/SPGNYT02_dbspace_input]
host = spgny-uat2.intranet.barcapint.com
index = SPGNYT02
output.format = mkv
output.timestamp = 1

[lukejadamec]

The settings you posted are from inputs.conf inside the dbx local folder?
If so then it is incomplete, can you post the full config for this input from splunk\etc\apps\dbx\local\inputs.conf?

[gudli618]

The page doesn't allow me to attach a screesnshot. Here are the settings:
Input Type: Dump
Sourcetype: Blank
Output Format: Multi-line Key-Value format
Timestamp column: Blank
Timestamp format: Blank