Collector settings

The collector settings are used by the collector to successfully connect to the OPC-UA server.

Basic settings

ConnectionString

Description: The connection string used to connect to the SQL database. Use $USER and $PASSWORD as placeholders for the necessary credentials
Required: yes
Examples:

• postgres : postgres://$USER:$PASSWORD@host:port/database
• oracle : oracle://$USER:$PASSWORD@server/service_name
• sqlserver : sqlserver://$USER:$PASSWORD@host/instance?param1=value&param2=value
• mysql : $USER:$PASSWORD@protocol(address)/dbname?param=value
• sqlite3 : file:test.db?cache=shared&mode=memory
• hdb : hdb://$USER:$ PASSWORD@something.hanacloud.ondemand.com :443?TLSServerName=something.hanacloud.ondemand.com
• ase : ase://$USER:$PASSWORD@host:port/?prop1=val1&prop2=val2
• odbc : DSN=my-odbc-datasource-name;UID=$USER;PWD=$PASSWORD

MariaDB

• MariaDB was created as a fork of MySQL. Therefore, it uses the same connection string format as MySQL.
• It is required to add parseTime=true in the connection string to parse the timestamp correctly for a MariaDB connection using the MySQL driver.
• The NULL values should be handled, e.g. by using COALESCE(column_name, '') in the SQL query to read values from a column having NULL values, as otherwise NULL values will cause parsing issues for a MariaDB connection using the MySQL driver.

Oracle

The oracle driver used in the SQL collector supports v9+ Oracle SQL databases.
Find the service name or SID name by performing the following command in powershell/cmd on the Windows Oracle Database server:

1

lsnrctl status

Alternatively, look for the content of the tnsnames.ora file typically found in a similar path as C:\oracle\ora90\network\ADMIN.
The hostname and the tcp port is mentioned on which the Oracle DB is listening (IP and/or DNS name). Each item lists the service name, which is needed to setup a SQL connection.

Make sure that the ODBC library path is added to $PATH on the machine running the SQL collector (typically is automatically performed on installation of the ODBC driver).

ODBC

The ODBC driver for the underlying SQL database and the ODBC connection must be configured on the same machine that runs the SQL collector. For Linux that is performed in unixODBC and for Windows that is done in the ODBC Data Source Administrator.
The name of the ODBC connection should be used after DSN= in the ODBC connection string.

User

Description: The user used to connect to the SQL database
Required: no

Password

Description: The password used to connect to the SQL database
Required: no

Driver

Description: The database driver that will be used to connect to the SQL database.
Required: yes
Options: postgres | oracle | sqlserver | mysql | sglite3 | hdb | ase | odbc

QueryInterval

Description: The interval in seconds between execution of the SQL query.
Required: yes
Default value: 5
Minimum: 1

QueryTimeout

Description: The timeout in seconds of the SQL query.
Required: yes
Default value: 5
Minimum: 1

QueryString

Description: The query string used to query the SQL database.
Required: yes
Examples:

• postgres: SELECT * FROM table_name WHERE updated > $1;
• oracle: SELECT * FROM table_name WHERE updated > :1;
• sqlserver: SELECT * FROM table_name WHERE updated > @p1;
• mysql/mariadb: SELECT * FROM table_name WHERE updated > ?;
• sqlite3: SELECT * FROM table_name WHERE updated > ?;
• hdb: SELECT * FROM table_name WHERE updated > ?;
• ase: SELECT * FROM table_name WHERE updated > ?;
• odbc: SELECT * FROM table_name WHERE updated > ?;

For the ODBC driver, the syntax of the underlying SQL database is used.

Advanced settings