Setup logstash jdbc-input, and wanted to use the default Oracle TNS Names lookup instead of storing the JDBC connect string directly in the logstash configuration.
Basically the JDBC connect string can be very long, and it is changing outside the scope of logstash, but the DBA and AO keep a tnsnames.ora in sync under $ORACLE_HOME, so it is the way better option to use this.
Configuration, with a thin jdbc connect string
input {
jdbc {
id => "logstash_jdbc_sessions_hour"
# JDBC Settings
jdbc_connection_string => "jdbc:oracle:thin:@db_name"
jdbc_user => "<username>"
jdbc_password => "<password"
jdbc_validate_connection => true
jdbc_driver_library => "/custom/logstash/lib/ojdbc10.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_fetch_size => 1000
use_column_value => true
tracking_column => "created"
tracking_column_type => "timestamp"
last_run_metadata_path => "/custom/logstash/tmp/nb01-sessions_hour.stat"
statement => "SELECT * FROM sessions_hour WHERE created > :sql_last_value ORDER BY created"
#
# How often to schedule / run the query. Cron syntax.
# Leave unset to run only once
#
schedule => "14 10 * * *"
add_field => {
"@custom_indextype" => "custom-statistics-jdbc"
"@custom_doc_type" => "jdbc"
"@db_database" => "nb01"
"@db_table" => "sessions_hour"
}
}
}
Problem is that I'm getting this error message.
[2021-10-29T09:45:00,594][ERROR][logstash.inputs.jdbc ][main][logstash_jdbc_sessions_hour] Unable to connect to database. Tried 1 times {:error_message=>"Java::JavaSql::SQLRecoverableException: IO Error: Unknown host specified "}
Logstash is setup with saltstack (automated), and creates a systemd service file:
/etc/systemd/system/logstash-jdbc.service
[Unit]
Description="Logstash configuration for logstash-jdbc-custom-input-p1"
[Service]
Type=simple
User=custom
Group=custom
# Load env vars from /etc/default/ and /etc/sysconfig/ if they exist.
# Prefixing the path with '-' makes it try to load, but if the file doesn't
# exist, it continues onward.
EnvironmentFile=-/etc/default/logstash-jdbc-custom-input-p1
ExecStart=/usr/share/logstash/bin/logstash "--path.settings" "/custom/logstash/settings.d"
Restart=always
WorkingDirectory=/
Nice=19
LimitNOFILE=16384
[Install]
WantedBy=multi-user.target
The environment file consists of:
/etc/default/logstash-jdbc-custom-input-p1
LS_HOME="/custom/logstash"
LS_SETTINGS_DIR="/custom/logstash/settings.d"
LS_PIDFILE="/var/run/logstash-jdbc-custom-input-p1.pid"
LS_USER="custom"
LS_GROUP="custom"
LS_GC_LOG_FILE="/custom/logstash/log/gc.log"
LS_OPEN_FILES="16384"
LS_NICE="19"
SERVICE_NAME="logstash-jdbc-custom-input-p1"
SERVICE_DESCRIPTION="logstash-jdbc-custom-input-p1"