Connectors

Connectors used as interfaces allow for connecting external data sources, services, and databases to the Event Management system.

The following connectors are currently available:

  • Service Broker Connector: If MS SQL Structured Query Language is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system. 2005 or later is used as the database server, this connector can be used. The advantage of this connection type is that the Service Broker is forwarding new events to the connector automatically without the need for repeated queries to the database. Thus, the latency for polling queries can be significantly reduced. The configuration of this connector is simple and does not require a manual setup of the Service Broker queues in the database.
  • Service Broker Queue Connector: If MS SQL 2005 or later is used as the database server, this connector can be used. This is a lower-level interface to the Service Broker infrastructure and it provides the maximum performance and lowest latency of all connectors. It connects directly to a queue of the Service Broker and retrieves notifications directly from it. The setup requires the administrator to configure the queues and notifications manually on the database.
  • SQL Connector: If a database server older than MS SQL 2005 is used, or another database server, e.g. Oracle, or an ODBC In computing, Open Database Connectivity is a standard application programming interface (API) for accessing database management systems.-compliant database, this connector can be used. Unlike the Service Broker Connector, this connector only forwards new events whenever a poll is performed by the connector.

 

1. Service Broker Connector

The primary advantage of this connector compared to the SQL connector is its use of a "Subscription Query" to detect new events, which minimizes the data acquisition and the computation costs. A soon as a notification has been received, the connector performs another query to collect all of the new events’ data. Thus, the lags that may occur by polling can be avoided. The notification of the Service Broker technology is instantaneous. Additionally, this connector makes the Service Broker infrastructure available for use without the need for a manual configuration of the notifications in the database.

The Service Broker requires the following information:

Name: This is the name of the connector. It is used for identification purposes.

Activated: If this checkbox is activated, the connector is in use.

Provider: In this field, the name of the .NET Framework Data Provider (ADO.NET) is to be specified. The provider is used to access the database. Depending on the system used, different values have to be entered here.

Examples:

  • "System.Data.SqlClient" for SQL servers,
  • "System.Data.OracleClient" for Oracle,
  • "System.Data.Odbc" for generic ODBC databases,
  • or individual provider for different database systems.

Connection string: This connection string specifies the connection details. In most cases, this connection string and the "Subscription connection string" are identical.

Example:

Data Source=192.168.0.1;Initial Catalog=myDB;UserId=myLogin;

Password=myPassword

Query: Defines the SQL database query to be used for retrieving the full event data. This query includes all fields of the event. Ideally, this query should retrieve only a small subset of the data of the new events for performance reasons. To achieve this partial selection, expressions can be used in the query additionally. The following example shows a query to retrieve all entries added since the last query.

Example:

Copy 
SELECT
{$ ServiceBrokerConnector.IdentityColumn $} AS IdColumn,
{$ ServiceBrokerConnector.Provider $} AS Provider,
{$ ServiceBrokerConnector.LastPoll.Time[yyyy/MM/dd hh:mm:ss] $} AS LastPollTime,
{$ ServiceBrokerConnector.LastPoll.Identities.Count $} AS LastPollIDCount,
{$ ServiceBrokerConnector.LastPoll.Identities.Contains[41] $} AS LastPollContains,
FROM myEvents WHERE
column1 IS NOT NULL
AND eventDate > COALESCE({$ ServiceBrokerConnector.LastDetectedRow[eventDate]$}, 
CONVERT(nvarchar(50), GETUTCDATE()-10, 126))
ORDER BY eventDate ASC

Identity column: This column is used for recognizing possible data duplicates. It does not have to be the primary key column. If the table does not contain a column with distinct values, a respective column has to be added (e.g. SELECT col1 + col2 + col3 as IdCol [...]), which can be used to detect duplicates.

Source system: A name for the source can be entered here. This value will be attached to the respective event, in order to retrace the system used to generate an event. This field is only used for identification purposes after the event has been processed. For example, if NAGIOS is entered as a Source system, the ticket mapping In computing and data management, data mapping is the process of creating data element mappings between two distinct data models. Data mapping is used as a first step for a wide variety of data integration tasks including. will be configured to assign this value to a ticket field, and then tickets created after a trigger by a NAGIOS event will be handled differently.

Subscription connection string: Defines connection details for the subscription query. The subscription query is used for receiving notifications on new datasets. In most cases, the value of this field and the Connection string field will be identical.

Example:

Data Source=192.168.0.1;Initial Catalog=myDB;UserId=myLogin; Password=myPassword.

Subscription query: This is the query used to subscribe to notifications. The connector will be informed as soon as there are changes that modify the query result.

Example:

SELECT id FROM myEvents

 

2. Service Broker Queue Connector

This is a lower-level connector compared to the Service Broker Connector. It provides the best performance of all available connectors and has the lowest latency as well. This connector is also based on the Service Broker infrastructure, but it operates on a lower-level compared to the Service Broker Connector, giving the administrator maximum flexibility, but also requiring a more detailed knowledge for its use.

The primary advantage of this connector over the Service Broker Connector is that it does not require a repeated query in order to retrieve the new data. Instead, it receives a notification already containing all details and content for the event, resulting in an optimal query performance. This is the recommended connector for systems, in which a single database contains an extremely large amount of incoming events per second and performance is a critical factor.

This connector requires a SQL Server 2005 or newer to operate. The Service Broker Queue requires the following information:

Name: This is the name of the connector used for its identification.

Activated: If this checkbox is activated, the connector is in use.

Connection String: This connection string defines the connection details. A SQL Server connection string is to be used here.

Example:

Data Source=192.168.0.1;Initial Catalog=myDB;

User Id=myLogin;Password=myPassword

Queue name: Defines the name of the Service Broker queue to contain the events. This queue has to be setup manually in the database.

Example:

Isonet_EventManagement_TargetMessageQueue

The following scripts show an exemplary configuration of a queue named Isonet_EventManagement_TargetMessageQueue in the database:

Copy 
IF EXISTS (SELECT * FROM sys.services
WHERE name = ’Isonet_EventManagement_InitiatorService’)
BEGIN
DROP SERVICE Isonet_EventManagement_InitiatorService
END
GO
IF EXISTS (SELECT * FROM sys.services
WHERE name = ’Isonet_EventManagement_TargetService’)
BEGIN
DROP SERVICE Isonet_EventManagement_TargetService
END
GO
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = ’Isonet_EventManagement_Contract’)
BEGIN
DROP CONTRACT Isonet_EventManagement_Contract
END
GO
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = ’Isonet_EventManagement_MessageType’)
BEGIN
DROP MESSAGE TYPE Isonet_EventManagement_MessageType 
END
GO
IF OBJECT_ID(’[dbo].[Isonet_EventManagement_MessageQueue]’)
IS NOT NULL AND EXISTS(SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(’[dbo].[Isonet_EventManagement_MessageQueue]’) AND type = ’SQ’)
BEGIN
DROP QUEUE [dbo].[Isonet_EventManagement_MessageQueue]
END
GO
IF OBJECT_ID(’[dbo].[Isonet_EventManagement_TargetMessageQueue]’) 
IS NOT NULL AND EXISTS(SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID (’[dbo].[Isonet_EventManagement_TargetMessageQueue]’) AND type = ’SQ’)
BEGIN
DROP QUEUE [dbo].[Isonet_EventManagement_TargetMessageQueue]
END
GO
CREATE MESSAGE TYPE Isonet_EventManagement_MessageType VALIDATION = WELL_FORMED_XML
GO
CREATE CONTRACT Isonet_EventManagement_Contract (Isonet_EventManagement_MessageType SENT BY INITIATOR)
GO
CREATE QUEUE [dbo].[Isonet_EventManagement_TargetMessageQueue] GO
CREATE QUEUE [dbo].[Isonet_EventManagement_MessageQueue] GO
CREATE SERVICE Isonet_EventManagement_InitiatorService 
ON QUEUE [dbo].[Isonet_EventManagement_MessageQueue] GO
CREATE SERVICE Isonet_EventManagement_TargetService ON 
QUEUE [dbo].[Isonet_EventManagement_TargetMessageQueue] ([Isonet_EventManagement_Contract])
GO

 

And once a queue is created, the notifications to that queue need to be setup. The following example creates a notification on insertion on a table called "IncomingEvents":

Copy 
CREATE TRIGGER [dbo].[IncomingEvents_TriggerNotification] ON [dbo]. [IncomingEvents]
AFTER INSERT AS
DECLARE @conversationHandle UNIQUEIDENTIFIER DECLARE @message XML
SET @message = (SELECT TOP 1 * FROM INSERTED FOR XML AUTO, ELEMENTS)
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE Isonet_EventManagement_InitiatorService 
TO SERVICE ’Isonet_EventManagement_TargetService’
ON CONTRACT Isonet_EventManagement_Contract WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @conversationHandle MESSAGE 
TYPE Isonet_EventManagement_MessageType (@message)
GO

Source system: Specifies the name of the source. This value is attached to the respective event in order to retrace, which system has generated an event. It is only used for identification purposes, after the event has been processed.

Example:

If Source system is set to NAGIOS, then the event-to-ticket mapping is configured to assign this value to a ticket field; thus, tickets created by a NAGIOS event will be handled differently.

Threads: Defines the number of threads used to collect events. In most cases, a value of 1 is sufficient, due to the very light processing cost of collecting an event.

 

3. SQL Connector

The configuration of the SQL connector is analogous to the configuration of the Service Broker. The only difference is that this connection will not send instant notifications on data changes in the database automatically. In this case, the connector has to query the database at assigned intervals (polling).

Name: This is the name of the connector used for its identification.

Activated: If this checkbox is activated, the connector is in use.

Provider: The name of the .NET Framework Data Provider (ADO.NET) is to be specified here. The provider is used for accessing the database. Depending to the system used, different values have to be entered here.

Examples:

  • For SQL Server use "System.Data.SqlClient",
  • for Oracle use "System.Data.OracleClient",
  • for generic ODBC databases use "System.Data.Odbc",
  • or custom providers for other databases.

Connection String: This connection string specifies the connection details.

Example:

Data Source=192.168.0.1;Initial Catalog=myDB;UserId=myLogin;Password=myPassword

Query: Defines the SQL database query to be used to retrieve the full event data. This query covers all fields of the event. Ideally, this query should retrieve only a small subset of data on the new events for performance reasons. In order to achieve this partial selection, expressions can be used within the query additionally. The following example shows the query for retrieving all the new entries added since the last query.

Example:

Copy 
SELECT
{$ ServiceBrokerConnector.IdentityColumn $} AS IdColumn,
{$ ServiceBrokerConnector.Provider $} AS Provider,
{$ ServiceBrokerConnector.LastPoll.Time[yyyy/MM/dd hh:mm:ss] $} AS LastPollTime,
{$ ServiceBrokerConnector.LastPoll.Identities.Count $} AS LastPollIDCount,
{$ ServiceBrokerConnector.LastPoll.Identities.Contains[41] $} AS LastPollContains,
FROM myEvents
WHERE
column1 IS NOT NULL
AND eventDate > COALESCE({$ ServiceBrokerConnector.LastDetectedRow[eventDate]
$}, CONVERT(nvarchar(50), GETUTCDATE()-10, 126))
ORDER BY eventDate ASC

Identity column: This column is used for recognizing possible data duplicates. It does not necessarily have to be the primary key column. If the table does not contain a column with unique values, a respective column has to be added (e.g. SELECT col1 + col2 + col3 as IdCol [...]), which can be used to detect duplicates.

Source system: A name for the source can be entered here. This value will be attached to the respective event, in order to retrace the system used to generate an event. This field is only used for identification purposes after the event has been processed. For example, if NAGIOS is entered as a Source system, the ticket mapping will be configured to assign this value to a ticket field, and then tickets created after a trigger by a NAGIOS event will be handled differently.

Poll interval: This is the time between two database queries. The interval is specified in seconds.