Splunk® Supported Add-ons

Splunk Add-on for Oracle Database

Download manual as PDF

Download topic as PDF

Configure Splunk DB Connect v2.x inputs for the Splunk Add-on for Oracle Database

To gather inventory and performance data from your Oracle Database Server, the Splunk Add-on for Oracle Database leverages Splunk DB Connect. Follow the instructions that correspond to the version of DB Connect that you have installed. This topic presents the instructions for DB Connect version 2.X.

Set up the database connection

1. Download the JDBC driver for Oracle database from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html.

2. Place the driver file called ojdbc*.jar in $SPLUNK_HOME/etc/apps/splunk_app_db_connect/bin/lib/ on the part of your Splunk Enterprise architecture performing the data collection.

3. Still on the part of your Splunk Enterprise architecture performing data collection, go to Splunk DB Connect in Splunk Web.

4. Create an identity for establishing a connection to a database. Make sure the user for this identity has the system role. The screenshot below shows an example identity configuration.

Dbx2identity.png

Use the Splunk DB Connect GUI to create a database connection

To create a database connection to the Oracle Database Server using the Splunk DB Connect GUI:

Refer to the "Create and manage database connections" in the Splunk DB Connect manual for step-by-step instructions for using the GUI to set up a new database connection.

Enter the following parameters.

Parameter Value
Connection Name Use Oracle_Connection for the connection name. If you prefer to choose other connection name, you need to manually edit your local db_inputs.conf file later to specify a non-default name.
Identity Use the identity you created above.
App Use the default app, Splunk DB Connect V2
Port The default port for Oracle database is 1521.
Host Enter the host IP address where the Oracle database is running.
Database Types Choose Oracle.
Default Database: Enter orcl.

The screenshot below shows an example connection configuration.

Dbx2dbconnection.png

Configure the inputs

To configure the inputs, you can copy the input template provided in the add-on to your local db_inputs.conf file and enable the inputs that you want to collect there. Alternatively, you can configure the DB Connect inputs manually using the DB Connect GUI.

Use db_inputs.conf

1. Copy the contents of the dbx2.inputs.conf.template file from $SPLUNK_HOME/etc/apps/Splunk_TA_oracle/default to $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/db_inputs.conf.

2. Enable the inputs through the configuration file by toggling the disabled attributes in the input stanzas you want to use.

  disabled = 0

3. If you selected a custom connection name other than Oracle_Connection, change that parameter here in each stanza to match the connection name that you configured in database.conf or using the GUI.

4. If necessary, adjust the index to another index of your preference.

5. If you are using Oracle Database Server 10g, uncomment the 10g version of the [mi_input://oracle:osPerf] stanza and comment out the 11g/12/c version.

6. Restart Splunk Enterprise services for your changes to take effect.

Use the Splunk DB Connect GUI

Refer to "Create and manage database inputs" in the Splunk DB Connect manual for step by step instructions for using the GUI to configure your database inputs.

The following examples detail the configuration instructions for each source type, with example screenshots for the first set of input screens for reference.

Oracle database information

Parameter Setting Value
Status Enabled
Name oracle:database
Description Oracle database information
App Splunk DB Connect v2
Connection Enter the database connection name you created when you set up the database connection. Default is Oracle_Connection.
Query Mode Advanced Query Mode
Query Statement
SELECT * FROM V$DATABASE
Type Batch Input
Max Rows to Retrieve 10000
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 7200
Source dbx
Sourcetype oracle:database
Index main
Select Resource Pool local

Dbx2example1.png

Dbx2example2.png

Dbx2example3.png

Dbx2example4.png

Oracle instance information

Parameter Setting Value
Status Enabled
Name oracle:instance
Description Oracle instance information
App Splunk DB Connect v2
Connection Enter the database connection name you created when you set up the database connection. Default is Oracle_Connection.
Query Mode Advanced Query Mode
Query Statement
SELECT* from v$instance cross join (select VALUE as NLS_CHARACTERSET from nls_database_parameters where parameter='NLS_CHARACTERSET') cross join (select VALUE as NLS_LANGUAGE from nls_database_parameters where parameter='NLS_LANGUAGE')  cross join (select VALUE as NLS_TERRITORY from nls_database_parameters where parameter='NLS_TERRITORY') cross join (select VALUE as NLS_SORT from nls_database_parameters where parameter='NLS_SORT')
Type Batch Input
Max Rows to Retrieve 10000
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 7200
Source dbx
Sourcetype oracle:instance
Index main
Select Resource Pool local

Oracle session information

Parameter Setting Value
Status Enabled
Name oracle:session
Description Oracle session information
App Splunk DB Connect v2
Connection Enter the database connection name you created when you set up the database connection. Default is Oracle_Connection.
Query Mode Advanced Query Mode
Query Statement
SELECT * FROM V$SESSION cross join (select instance_name, host_name from v$instance)
Type Batch Input
Max Rows to Retrieve 10000
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 900
Source dbx
Sourcetype oracle:session
Index main
Select Resource Pool local

Oracle tablespace information

Parameter Setting Value
Status Enabled
Name oracle:tablespace
Description Oracle tablespace information
App Splunk DB Connect v2
Connection Enter the database connection name you created when you set up the database connection. Default is Oracle_Connection.
Query Mode Advanced Query Mode
Query Statement
SELECT * FROM V$SESSION cross join (select instance_name, host_name from v$instance)
Type Batch Input
Max Rows to Retrieve 10000
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 3600
Source dbx
Sourcetype oracle:tablespace
Index main
Select Resource Pool local

Oracle tablespace capacity information

Parameter Setting Value
Status Enabled
Name oracle:tablespaceMetrics
Description Oracle tablespace capacity information
App Splunk DB Connect v2
Connection Enter the database connection name you created when you set up the database connection. Default is Oracle_Connection.
Query Mode Advanced Query Mode
Query Statement
SELECT TABLESPACE_NAME, TOTAL_BYTES, TOTAL_BLOCKS, FREE_BYTES, USED_BYTESFROM 
(SELECT D.TABLESPACE_NAME, TOTAL_BYTES, TOTAL_BLOCKS,FREE_BYTES, 
TOTAL_BYTES - NVL(FREE_BYTES, 0) "USED_BYTES" FROM (SELECT TABLESPACE_NAME, 
SUM (BYTES) TOTAL_BYTES, SUM (BLOCKS) TOTAL_BLOCKS FROM DBA_DATA_FILES 
GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME, SUM (BYTES) FREE_BYTES 
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME =
F.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME, TOTAL_BYTES,
TOTAL_BLOCKS,NVL(FREE_BYTES, 0) "FREE_BYTES", USED_BYTES FROM 
(SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_BYTES, SUM(BLOCKS) 
TOTAL_BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D,
 (SELECT TABLESPACE_NAME, SUM(BYTES_USED) USED_BYTES, 
SUM(BYTES_FREE) FREE_BYTES FROM V$TEMP_SPACE_HEADER GROUP BY 
TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+))
Type Batch Input
Max Rows to Retrieve 10000
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 900
Source dbx
Sourcetype oracle:tablespaceMetrics
Index main
Select Resource Pool local

Oracle SGA information

Parameter Setting Value
Status Enabled
Name oracle:sga
Description Oracle SGA information
App Splunk DB Connect v2
Connection Enter the database connection name you created when you set up the database connection. Default is Oracle_Connection.
Query Mode Advanced Query Mode
Query Statement
SELECT NAME, TOTAL, FREE  FROM (  SELECT 'SGA' NAME,  
(SELECT SUM(VALUE) FROM V$SGA) TOTAL,  (SELECT SUM(BYTES) 
FROM V$SGASTAT WHERE NAME = 'free memory') FREE  FROM DUAL  )  
UNION  SELECT NAME, TOTAL, TOTAL - USED FREE  FROM (  
SELECT 'PGA' NAME,  (SELECT VALUE FROM V$PGASTAT WHERE 
NAME = 'aggregate PGA target parameter') TOTAL,  (SELECT VALUE 
FROM V$PGASTAT WHERE NAME = 'total PGA allocated') USED  
FROM DUAL  )  UNION  SELECT NAME, TOTAL, FREE  FROM (  
SELECT 'Shared pool' NAME,  (SELECT SUM(BYTES) FROM 
V$SGASTAT WHERE POOL = 'shared pool' GROUP BY POOL) 
TOTAL,  (SELECT BYTES FROM V$SGASTAT WHERE NAME = 
'free memory' and POOL = 'shared pool') FREE  FROM DUAL  )  
UNION  SELECT NAME, TOTAL, FREE  FROM (  SELECT 'Large pool' 
NAME,  (SELECT SUM(BYTES) FROM V$SGASTAT WHERE POOL = 
'large pool' GROUP BY POOL) TOTAL,  (SELECT BYTES FROM 
V$SGASTAT WHERE POOL = 'large pool' AND NAME = 'free memory') 
FREE  FROM DUAL  )
Type Batch Input
Max Rows to Retrieve 10000
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 300
Source dbx
Sourcetype oracle:sga
Index main
Select Resource Pool local

Oracle library cache information

Parameter Setting Value
Status Enabled
Name oracle:libraryCachePerf
Description Oracle cache information
App Splunk DB Connect v2
Connection Enter the database connection name you created when you set up the database connection. Default is Oracle_Connection.
Query Mode Advanced Query Mode
Query Statement
SELECT NAMESPACE, GETS, GETHITS, GETHITRATIO, PINS, 
PINHITS, PINHITRATIO, RELOADS, INVALIDATIONS, 
DLM_LOCK_REQUESTS, DLM_PIN_REQUESTS, DLM_PIN_RELEASES, 
DLM_INVALIDATION_REQUESTS, DLM_INVALIDATIONS FROM V$LIBRARYCACHE
Type Batch Input
Max Rows to Retrieve 10000
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 60
Source dbx
Sourcetype oracle:libraryCachePerf
Index main
Select Resource Pool local

Oracle database file I/O performance metrics

Parameter Setting Value
Status Enabled
Name oracle:dbFileIoPerf
Description Oracle database file I/O performance metrics
App Splunk DB Connect v2
Connection Enter the database connection name you created when you set up the database connection. Default is Oracle_Connection.
Query Mode Advanced Query Mode
Query Statement
SELECT 'DB_FILE' TYPE, D.NAME FILE_NAME, F.PHYRDS, F.PHYWRTS, 
F.AVGIOTIM,  F.MINIOTIM, F.MAXIOWTM, F.MAXIORTM  FROM V$FILESTAT F, 
V$DATAFILE D WHERE F.FILE# = D.FILE#  UNION  SELECT 
'TEMP_DB_FILE' TYPE, T.NAME FILE_NAME,  F.PHYRDS, F.PHYWRTS, 
F.AVGIOTIM, F.MINIOTIM, F.MAXIOWTM, F.MAXIORTM  FROM 
V$FILESTAT F, V$TEMPFILE T  WHERE F.FILE# = T.FILE#  ORDER BY 1
Type Batch Input
Max Rows to Retrieve 10000
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 120
Source dbx
Sourcetype oracle:dbFileIoPerf
Index main
Select Resource Pool local

Oracle database server host performance metrics

Parameter Setting Value
Status Enabled
Name oracle:osPerf
Description Oracle database server host performance metrics
App Splunk DB Connect v2
Connection Enter the database connection name you created when you set up the database connection. Default is Oracle_Connection.
Query Mode Advanced Query Mode
Query Statement
SELECT STAT_NAME, VALUE, CUMULATIVE FROM V$OSSTAT
Type Batch Input
Max Rows to Retrieve 10000
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 300
Source dbx
Sourcetype oracle:osPerf
Index main
Select Resource Pool local

Oracle database system performance metrics

Parameter Setting Value
Status Enabled
Name oracle:sysPerf
Description Oracle database system performance metrics
App Splunk DB Connect v2
Connection Oracle_Connection (connection name created in step 2)
Query Mode Advanced Query Mode
Query Statement DB Connect v2:
SELECT * FROM V$SYSMETRIC_HISTORY cross join (select instance_name, HOST_NAME from v$instance)

DB Connect v3:
SELECT * FROM (SELECT * FROM V$SYSMETRIC_HISTORY cross join (select instance_name, HOST_NAME from v$instance)) t WHERE END_TIME > ? ORDER BY END_TIME ASC
Type Batch Input
Max Rows to Retrieve 1000000
Specify Rising Column END_TIME
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 60
Source dbx
Sourcetype oracle:sysPerf
Index main
Select Resource Pool local

Oracle connections performance metrics

Parameter Setting Value
Status Enabled
Name oracle:connections
Description Oracle connections performance metrics
App Splunk DB Connect v2
Connection Oracle_Connection (connection name created in step 2)
Query Mode Advanced Query Mode
Query Statement
SELECT instance_name, HOST_NAME, connections from v$instance cross join (select count(*) as connections from v$session where username is not null)
Type Batch Input
Max Rows to Retrieve 10000
Specify Rising Column END_TIME
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 300
Source dbx
Sourcetype oracle:connections
Index main
Select Resource Pool local


Oracle pool connections performance metrics

Parameter Setting Value
Status Enabled
Name oracle:pool:connections
Description Oracle pool connections performance metrics
App Splunk DB Connect v2
Connection Oracle_Connection (connection name created in step 2)
Query Mode Advanced Query Mode
Query Statement
SELECT host_name, instance_name, active_pooled_connections, total_pooled_connections from v$instance cross join  (select count(case when status='active' then 1 end) active_pooled_connections, count(*) total_pooled_connections from DBA_CPOOL_INFO)
Type Batch Input
Max Rows to Retrieve 10000
Specify Rising Column END_TIME
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 300
Source dbx
Sourcetype oracle:pool:connections
Index main
Select Resource Pool local


Oracle database size metrics

Parameter Setting Value
Status Enabled
Name oracle:database:size
Description Oracle database size metrics
App Splunk DB Connect v2
Connection Oracle_Connection (connection name created in step 2)
Query Mode Advanced Query Mode
Query Statement
SELECT HOST_NAME, instance_name,current_size from v$instance cross join (select round((sum(bytes)/1024/1024),2) as current_size from v$datafile)
Type Batch Input
Max Rows to Retrieve 10000
Specify Rising Column END_TIME
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 300
Source dbx
Sourcetype oracle:database:size
Index main
Select Resource Pool local


Oracle table metrics

Parameter Setting Value
Status Enabled
Name oracle:table
Description Oracle table metrics
App Splunk DB Connect v2
Connection Oracle_Connection (connection name created in step 2)
Query Mode Advanced Query Mode
Query Statement
SELECT host_name, instance_name,all_tables.*, dba_segments.bytes, All_TAB_MODIFICATIONS.timestamp as last_update_time from all_tables left join All_TAB_MODIFICATIONS on all_tables.table_name=All_TAB_MODIFICATIONS.table_name left join dba_segments on dba_segments.segment_name=all_tables.table_name  cross join (select host_name, instance_name from v$instance) where dba_segments.segment_type='TABLE' 
Type Batch Input
Max Rows to Retrieve 10000
Specify Rising Column END_TIME
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 300
Source dbx
Sourcetype oracle:table
Index main
Select Resource Pool local


Oracle database user metrics

Parameter Setting Value
Status Enabled
Name oracle:alluser
Description Oracle database user metrics
App Splunk DB Connect v2
Connection Oracle_Connection (connection name created in step 2)
Query Mode Advanced Query Mode
Query Statement
SELECT * FROM ALL_USERS cross join (select host_name, instance_name from v$instance)
Type Batch Input
Max Rows to Retrieve 10000
Specify Rising Column END_TIME
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 900
Source dbx
Sourcetype oracle:alluser
Index main
Select Resource Pool local


Oracle query performance metrics

Parameter Setting Value
Status Enabled
Name oracle:query
Description Oracle query performance metrics
App Splunk DB Connect v2
Connection Oracle_Connection (connection name created in step 2)
Query Mode Advanced Query Mode
Query Statement
SELECT\
instance_name, host_name, all_users.username, all_users.user_id,\
sql_id, sql_fulltext, plan_hash_value, DECODE(command_type,11,'ALTERINDEX',15,'ALTERTABLE',170,'CALLMETHOD',9,'CREATEINDEX',1,'CREATETABLE',7,'DELETE' ,50,'EXPLAIN',2,'INSERT',26,'LOCKTABLE',47,'PL/SQLEXECUTE',3,'SELECT',6,'UPDATE',189,'UPSERT') command_name,\
CASE\
WHEN executions > 0\
THEN ROUND(elapsed_time/executions,3)\
ELSE NULL\
END elap_per_exec,\
parsing_schema_name, module, elapsed_time,\
executions,\
PHYSICAL_READ_BYTES/1024 read_kb,\
buffer_gets,\
rows_processed\
FROM v$sqlarea,all_users cross join (select instance_name,host_name from v$instance) where v$sqlarea.PARSING_USER_ID = all_users.user_id
Type Batch Input
Max Rows to Retrieve 1000000
Specify Rising Column END_TIME
Timestamp Current Index Time
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 600
Source dbx
Sourcetype oracle:query
Index main
Select Resource Pool local


Unified Auditing logs (Oracle 12c only)

Parameter Setting Value
Status Enabled
Name oracle:audit:unified
Description Oracle 12c unified auditing information
App Splunk DB Connect v2
Connection Oracle_Connection (connection name created in step 2)
Query Mode Advanced Query Mode
Query Statement DB Connect v2:
SELECT CAST((event_timestamp at TIME zone 'UTC') AS TIMESTAMP) event_timestamp_utc,u.* FROM UNIFIED_AUDIT_TRAIL u

DB Connect v3:
SELECT * FROM (SELECT CAST((event_timestamp at TIME zone 'UTC') AS TIMESTAMP) event_timestamp_utc,u.* FROM UNIFIED_AUDIT_TRAIL u) t WHERE SCN > ? ORDER BY SCN ASC
Type Rising Column
Max Rows to Retrieve 10000
Specify Rising Column EVENT_TIMESTAMP_UTC
Timestamp EVENT_TIMESTAMP_UTC
Output Timestamp Format YYYY-MM-dd HH:mm:ss
Execution Frequency 300
Source dbx
Sourcetype oracle:audit:unified
Index main
Select Resource Pool local
Last modified on 11 July, 2018
PREVIOUS
Configure monitor inputs for the Splunk Add-on for Oracle Database
  NEXT
Configure Splunk DB Connect v3.1 inputs for the Splunk Add-on for Oracle Database

This documentation applies to the following versions of Splunk® Supported Add-ons: released


Was this documentation topic helpful?

Enter your email address, and someone from the documentation team will respond to you:

Please provide your comments here. Ask a question or make a suggestion.

You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters