Splunk Search

DB input configuration setup for oracle database

gudli618
New Member

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

Tags (1)
0 Karma

pmdba
Builder

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.

0 Karma

gudli618
New Member

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

0 Karma

gudli618
New Member

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

0 Karma

gudli618
New Member

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.

0 Karma

araitz
Splunk Employee
Splunk Employee

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.

0 Karma

lukejadamec
Super Champion

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

0 Karma

gudli618
New Member

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

0 Karma

gudli618
New Member

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

0 Karma

lukejadamec
Super Champion

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?

0 Karma

gudli618
New Member

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

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 ...