# JDBC Logstash with SystemD **URL:** \ **Category:** Logstash\ **Tags:** jdbc\ **Created:** [October 29, 2021, 8:43am UTC](https://discuss.elastic.co/t/jdbc-logstash-with-systemd/287979 "2021-10-29T08:43:18Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![sastorsl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sastorsl/32/44972_2.png) [@sastorsl](https://discuss.elastic.co/u/sastorsl)\ **Post date:** [October 29, 2021, 8:43am UTC](https://discuss.elastic.co/t/jdbc-logstash-with-systemd/287979/1 "2021-10-29T08:43:18Z")
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 ```auto input { jdbc { id => "logstash_jdbc_sessions_hour" # JDBC Settings jdbc_connection_string => "jdbc:oracle:thin:@db_name" jdbc_user => "" jdbc_password => " 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 ```auto [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 ```auto 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" ``` ---
**Author:** ![sastorsl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sastorsl/32/44972_2.png) [@sastorsl](https://discuss.elastic.co/u/sastorsl)\ **Post date:** [October 29, 2021, 8:49am UTC](https://discuss.elastic.co/t/jdbc-logstash-with-systemd/287979/2 "2021-10-29T08:49:00Z")
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... ```auto 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: ```auto [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 ``` ---
**Author:** ![sastorsl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sastorsl/32/44972_2.png) [@sastorsl](https://discuss.elastic.co/u/sastorsl)\ **Post date:** [October 29, 2021, 8:52am UTC](https://discuss.elastic.co/t/jdbc-logstash-with-systemd/287979/3 "2021-10-29T08:52:35Z")
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](https://www.freedesktop.org/software/systemd/man/systemd.exec.html#EnvironmentFile=) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 26, 2021, 8:52am UTC](https://discuss.elastic.co/t/jdbc-logstash-with-systemd/287979/4 "2021-11-26T08:52:50Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.