topic Re: DB input configuration setup for oracle database in Splunk Search

DB input configuration setup for oracle database

gudli618 — Mon, 28 Sep 2020 16:00:33 GMT

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

Re: DB input configuration setup for oracle database

gudli618 — Thu, 27 Feb 2014 23:28:11 GMT

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

Re: DB input configuration setup for oracle database

lukejadamec — Fri, 28 Feb 2014 01:15:07 GMT

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?

Re: DB input configuration setup for oracle database

gudli618 — Mon, 28 Sep 2020 16:00:39 GMT

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

Re: DB input configuration setup for oracle database

gudli618 — Mon, 28 Sep 2020 16:00:42 GMT

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

Re: DB input configuration setup for oracle database

lukejadamec — Fri, 28 Feb 2014 02:08:19 GMT

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

Re: DB input configuration setup for oracle database

araitz — Fri, 28 Feb 2014 18:42:59 GMT

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.

Re: DB input configuration setup for oracle database

gudli618 — Mon, 28 Sep 2020 16:01:36 GMT

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.

Re: DB input configuration setup for oracle database

gudli618 — Mon, 28 Sep 2020 16:01:39 GMT

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

Re: DB input configuration setup for oracle database

gudli618 — Mon, 28 Sep 2020 16:01:42 GMT

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

Re: DB input configuration setup for oracle database

pmdba — Fri, 13 Jun 2014 02:31:31 GMT

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.