JDBC Logstash with SystemD

Elastic Stack Logstash
1

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"
2

The problem is that the OJDBC does not know where to find the tnsnames.ora file, so I have to configure that.

The solution is to add the TNS_ADMIN environment variable and make it available to Logstash and the OJDBC driver.

By adding it to the EnvironmentFile referenced by the SystemD service file I can add it to my automation process (see above), and don't have to rely on any underlying server configuration.
Will of course have to coordinate with DBA / AO in case they move the file...

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"
TNS_ADMIN=/srv/app/oracle/network/admin

Restarting logstash with systemctl restart logstash-jdbc.service and now JDBC was able to connect:

[2021-10-29T10:33:07,270][INFO ][logstash.inputs.jdbc     ][main][logstash_jdbc_sessions_hour] (5.450559s) SELECT * FROM sessions_hour WHERE created > TIMESTAMP '1970-01-01 00:00:00.000000 +00:00' ORDER BY created
3

It can be mentioned that adding ORACLE_HOME alone to the EnvironmentFile does not resolve the issue. I had to add TNS_ADMIN.

Can also reference the official SystemD documentation: systemd.exec

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.