Database Listener

Description

The SQL Database listener runs SQL queries periodically and sends the output to 1Gateway as messages.

Prerequisites

The JDBC driver for the database has to be installed in the 1Gateway data/lib directory (File Upload into System Maintenance - 1Gateway restart is required).

Database	JDBC Driver Provider	JAR file name	Download
MySQL	Oracle Corporation	mysql-connector-java-VERSION.jar	Download JDBC Driver for MySQL
SQL Server	Microsoft Corporation	mssql-jdbc-8.2.2.jre8.jar	Download JDBC Driver for SQL Server
Oracle	Oracle Corporation	ojdbc6.jar, ojdbc7.jar, ojdbc8.jar	Download JDBC Driver for Oracle (login required)
PostgreSQL	The PostgreSQL Global Development Group	postgresql-VERSION.jar	Download JDBC Driver for PostgreSQL
SQLite	Xerial.org	sqlite-jdbc-VERSION.jar	Download JDBC Driver for SQLite

Installation

Make sure you have the 1gateway zip file that includes the plugin files and valid licences information ready. This file has been provided to you with the installation files.

Login to 1gateway, and make sure you are in "Advanced mode". If this is the first time you log in, the default user and password are both 'admin' (without the quotes).

2 - System Maintenance

Click on the menu icon and choose the option "System maintenance"

3 - Upload file

In the System maintenance view you can either drag and drop the downloaded zip file or select it by clicking the "choose file" button.

4 - Restart

After the upload installation will be confirmed. Restart 1Gateway to ensure any locked files are updated.

Configuration

Open the main menu and click on "New plugin"

Select the plugin you want to configure. Use the Filter field if needed.

There are two different profiles to configure:

Connection profile: includes database connection configuration
Query profile: configuration of the queries to run

Connection configuration

If a connectionString is defined, the rest of the parameters don't have to be set, and will be ignored. If no Connection String is set, the rest of the parameters have to be defined.

Field name	Supported values	Description	Example
Database host	Any string	Host where the database is	localhost
Database port	Any string	Port number	1433
Databse type	Any string	Database type	sqlserver, sqlite
Database user	Any string	Database user	sa
Database password	Any string	Database user's password	abc123
Database name	Any string	Database name	TestDB
Connection string	Valid connection string	Connection string to the database	jdbc:sqlite:C:\\Temp\metric.db jdbc:sqlserver://localhost:1433;user=sa;password=abc123;databaseName=TestDB; jdbc:oracle:thin:[<user>/<password>]@//HOSTNAME:PORT/SERVICENAME jdbc:oracle:thin:[<user>/<password>]@(description=(address=(host=HOSTNAME)(protocol=tcp)(port=PORT))(connect_data=(service_name=SERVICENAME)(server=SHARED)))

Query configuration

The SQL query output must contain a Type column that specifies the message type. This message type is then used for the mapping (see Mappers). In this example, SCOMAlert was defined.

Field name	Supported values	Description	Example
SQL query	Valid SQL query	Query to be executed	select 'SCOMAlert' as type, * FROM Alertview WITH (NOLOCK) WHERE TimeRaised is not NULL AND LastModified > ? ORDER BY LastModified
Field parameter	Any string	Parameter in the query to run as the where field	LastModified
Field default value	Any string	Starting parameter value (needs to be valid)	2019-01-01 00:00:00.000
Database connection	Any string	Name of the connection to use	SCOMDB
Polling interval	Any integer	Polling interval in seconds (default is 300)	60

The field parameter is defined in the query as '?'. It is recommended to define at least one parameter with its default value.