Hi,
Anyone out there have tried to use Splunk DB Connect to connect to DBs with multiple hosts?
Below is sample connection string with multiple hosts. How do I configure the DB Connection in DB Connect? I can only specify one hosts in the DB Connect configuration.
DB.NAME=
(DESCRIPTION=
(SOURCE_ROUTE=yes)
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)
(HOST={hostname1})
(PORT=1630)
)
(ADDRESS=
(PROTOCOL=TCP)
(HOST={hostname2})
(PORT=1630)
)
(ADDRESS=
(PROTOCOL=TCP)
(HOST={hostname3})
(PORT=1521)
)
)
(CONNECT_DATA=
(SERVICE_NAME={service_name})
)
)
Thanks!
I have just configured this on DB Connect v1.2.2 using a thin connection string with an address list containing two hosts and it works for me.
First I had to create a new database type in $SPLUNK_HOME/etc/apps/dbx/local/database_types.conf and hard code the hostnames of the multiple hosts. The only dynamic parts in the connection string are the service name (SID) and port:
#Bits in bold can/need to be changed to reflect your environment
[oracle-service-x]
displayName = OracleServiceX
defaultCatalogName = ORCLX
jdbcDriverClass = oracle.jdbc.OracleDriver
connectionUrlFormat = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=off)(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT={1}))(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})))
testQuery = SELECT 1 FROM DUAL
defaultPort = 1521
Then I added a new connection by editing the configuration file $SPLUNK_HOME/etc/apps/dbx/local/database.conf:
[ConnectionXYZ]
database = SIDNAME
database.sid = true
host = ANYTHING
isolation_level = DATABASE_SETTING
password = ANYTHING
port = 1521
readonly = 1
type = oracle-service-x
username = ORACLEUSERNAME
Then restart splunk.
Using the web interface login, navigate to the newly created connection and set the password.
Then all should be working.
The trick to this that kept catching me out was the default configuration in $SPLUNK_HOME/etc/apps/dbx/local/database_types.conf for oracle had the line typeClass = com.splunk.dbx.sql.type.impl.Oracle and if this line exists the connectionUrlFormat line is ignored. So by simply removing this typeClass configuration the connection string is used and presumably you can use any normal oracle connection parameters.
I hope this helps anyone trying to set this up.
Because DBX builds the JDBC uri dynamically from parameters in the database.conf file (host, port, and service name), I do not believe this kind of connect string is currently possible, especially if you must use the source_route setting through multiple connection managers.
Perhaps someone at Splunk would consider support for an OCI / JDBC-Thick connect string, or a the ability to define the complete URI in the configuration file as an enhancement request?
Perhaps someone at Splunk would consider support for an OCI / JDBC-Thick connect string, or a the ability to define the complete URI in the configuration file as an enhancement request?
yes, i was thinking about submitting a feature request for Splunk DB Connect to support thick connection string and/or TNS connection. but i was hoping that there is some kind of a work around to connect to multiple hosts with the current version of DB Connect. thank you for confirming my suspicion though.
Feature requests can be sent to support@splunk.com
Are you using Oracle RAC, and if so, which version?
hi,
here's the version.
"Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64-bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing Options"
thanks!