SQL collector

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 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 5 minimum 1 querytimeout description the timeout in seconds of the sql query required yes default 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 heartbeatinterval description the sql heartbeat timeout in milliseconds, minimum 5s required no default 10000 minimum 5000 measurement settings the measurement settings reflect the configuration possibilities for mapping a measurement to an opc ua tag measurementkey description the key that will get mapped to this measurement from the query result required yes data collection method the sql collector allows you to collect data from a sql database this is done by periodically executing the sql collector /#example query the collector expects the following columns to be returned measurement key used to link the row to a measurement quality the quality of the data, will get added as the status tag an empty value is considered as a “good” status value the value of the data point, should be of the corresponding measurement datatype ts the timestamp of the data point updated this value used to make sure we don’t query data more than once, the most recent timestamp is kept and passed to the query as a parameter tag all columns starting with tag are considered as tags with everything after tag being used as the tag name example query select a id as measurement key, a val as value, b timestamp as ts, a updated as updated, 'good' as quality, b product nr as tag prodnr, from factry a a inner join factry b b on b nr = a b nr where updated > ?; measurement key value ts updated quality tag prodnr example key 12 0 2022 09 29 11 30 00+00 2022 09 29 11 50 00+00 good 42