Connector Suite for MS SQL Server Tables
The connectors for SQL Server tables are implemented to link ASM Core and third-party SQL Server database tables.
There are four SQL Server Table Connectors, each performing a distinct function which is described in more detail below. Each connector can be configured to connect to a table in a MS SQL Server database.
This topic provides details of the SQL Connectors, including:
The types of functions each connector can perform
Use case examples
Connection methodology
The requirements involved in the handling of Event Management between ASM Core and the third-party database table
For compatibility and version support details, refer to the ASM Connector Matrix.
You should familiarize yourself with the information in Installing Connectors before installing any connectors, and read the Integration topics for more information on how to configure them.
Connector Description
The Connector Suite for MS SQL Server Tables is comprised of four distinct connectors, each performing a specific function:
| Connector | Function |
|---|---|
MS SQL Table Resources Connector | Import resources and their relationships from external data tables, and maintain them in ASM Core |
MS SQL Table Events Connector | Create and update Calls or Requests in ASM Core from records in an external SQL Server table |
MS SQL Table Lookup Connector | Query records in an external SQL Server table using Outbound Action Tasks |
MS SQL Table Outbound Action Connector | Insert records into an external SQL Server table using Outbound Actions on calls or Outbound Action Tasks |
Use Case Scenarios
Example use case scenarios for each connector are described below.
MS SQL Table Resources Connector
Purpose | An organization uses a third-party application to create and store Knowledge Articles from a specialized division within their organization. These knowledge articles and their links to each other need to be imported into ASM Core so the rest of the organization can access them. |
Role | The role of this connector is to expose the resources and their relationships in the third-party database in order to allow for population and ongoing consistency checks of those records in ASM Core. |
MS SQL Table Events Connector
Purpose | An organization uses a network security application to generate alerts if it detects unusual behavior. For each alert generated, the organization requires a call/request to be created in ASM Core so the appropriate action can be taken. |
Role | The role of this connector is to generate calls or requests in ASM Core from records it detects in the third-party database table. |
MS SQL Table Lookup Connector
Purpose | An organization uses a third-party Human Resources application to track employee annual leave accrual. Employees request leave by submitting a Leave Request form in ASM Core which triggers a request workflow. An outbound action task in this workflow performs a lookup in the HR application database table, based on information entered into the request, to validate the employee has sufficient leave available. |
Role | The role of this connector is to query an external database and retrieve a value which is then typically used to determine the progression of the request workflow, or to update a record in ASM Core. |
MS SQL Table Outbound Action Connector
Purpose | An organization uses a third-party Human Resources application to track employee annual leave usage. Employees request leave by entering leave dates into a Leave Request form in ASM Core, which managers approve or reject. If approved, the leave dates need to be transferred to the HR application. |
Role | The role of this connector is to write data to an external database. |
Connector Components
The table below lists the connector class for each SQL Table Connector. In all cases the connection methodology is SQL.
Unlike other connectors, these connectors do not use .ICNF files.
| Connector Name | Connector Class |
|---|---|
MS SQL Table Resources Connector | Alemba®.Connector.SQLTable.CMDB.Connector |
MS SQL Table Events Connector | Alemba®.Connector.SQLTable.Events.Connector |
MS SQL Table Lookup Connector | Alemba®.Connector.SQLTable.LookupAction.Connector |
MS SQL Table Outbound Action Connector | Alemba®.Connector.SQLTable.OutAction.Connector |
Connection Parameters
The table below provides a description of the connection parameters. These are configured on the Integration Source window.
The login account used to access the database must have permissions to read and write to the specified table(s).
If using Windows Authentication to log into the database
the Windows User configured for the ASM Connector Service, and the IIS User account for the Web UI, must both have read and write permissions to the database
append ;Integrated Security=SSPI to the connection string
leave blank the Login ID and Password fields in the connection parameters
Parameters | Description |
Connection String | Database server connection string. Formats: Server=<DatabaseServer>;Database=<DatabaseName> Server=<DatabaseServer>;Database=<DatabaseName>;Integrated Security=SSPI Server=localhost;Database=Core Server=localhost;Database=Core;Integrated Security=SSPI |
Login ID | Login ID for the third-party database |
Password | Password for the third-party database |
Table Parameters
This section provides a description of the database table information required for each of the MS SQL Table connectors, in order for them to read from and write to external tables. These are configured on the Integration Source window below the connection parameters.
These connectors write to the external table defined in the parameters. If this is a table in a third-party application, it may be preferable to create a new column in the external table for this purpose, or to create a temporary table.
Table and column names containing spaces cause the connector to fail and should not be used in the Integration Source configuration or the field mappings within the Resource settings.
A column name of ITEM REF will cause the connector to fail. A column name of ITEM_REF or ItemRef will not.
MS SQL Table Resources Connector
This connector imports resources from a data table. A secondary table allows the creation of links between the imported resources.
Parameters | Description |
Table Name | The name of the table containing resources to be imported into ASM Core. Required. |
Unique Ref Column | The column in the external table which contains the unique identifier of the items to be imported. Required. |
Display Name Column | The column in the external table which contains the name of the item to be imported. Required. |
Import Status Column | If populated, this is the column in the external table which:
Write access is required for this column if the import status is to be written to it. If this field is blank all items in the table are imported and the column in the external table is not updated with the import status. |
Import when text in Import Status Column is: | Change the default text as needed. Default is "ready for pickup". The connector looks in the column defined in Import Status Column for matching text and, if found, the record is imported. After the record is processed, the text in that column is changed to Processed+Date+Time (e.g.: Processed2017-03-27 01:59:59Z) If this field is blank "ready for pickup" is used as default. |
Resource Type Column | A column in the external table representing the resource type. The value of this column is used to generate the list of available resource types on the Integration Resources window for this connector source. Required. An Integration Source is configured to import from a Configuration Item table. In this table column CI_Type contains values 'PC' and 'Keyboard'. Enter CI_Type into the Resource Type Column field to expose PC and Keyboard on the Integration Resource window when this source is selected . |
Linking Table | A table in the external database linking items in the resource table (Table Name field). This function looks at the first two columns in the linking table, and links the item in the first column to the item in the second column. Items being linked must be of different resources types. Optional. |
MS SQL Table Events Connector
If the Event Management checkbox in the Integration Platform administration screen is selected, the Event Management functionality starts running as soon as a proper Event mapping is completed and saved.
When starting, the connector checks the configured database table and logs a call or request for every item that is present and fulfills the criteria that are implemented in the Event mapping. This could lead to a large number of calls/requests being created when activating the Event Management functionality.
One solution to avoid this behavior is to include in the Event criteria setting an item based on a date attribute. For example, you could plan to “go live” with Event management on a precise date and, as a consequence, specify that the value of date field in the external table has to be after this date before any action can be triggered in ASM Core.
Parameters | Description |
Table Name | The name of the table containing records to be imported into ASM Core as calls or requests. Required. |
Unique Ref Column | The column in the external table which contains the unique identifier of the records to be imported into ASM Core as calls or requests. Required. |
Import Status Column | A column in the external table to which the import status is written. Required. The column is updated with 'Process'+Date+Time after the item is imported (e.g.: Processed2017-03-27 01:59:59Z). Records are imported if the column in the external table contains text matching the text in the Import when text in Import Status Column is: field. If the text does not match, the record is not imported, and the cell in the external table is not updated. Write access is required for this column. |
Import when text in Import Status Column is: | Change the default text as needed. Default is "ready for pickup". The connector looks in the column defined in Import Status Column for matching text and, if found, the record is imported. If this field is blank "ready for pickup" is used as default. |
Internal Item No. Column | A column in the external table to which the corresponding ASM Core call or request number is written when it is created from that external event record. Required. |
Last Modified Date Column | A column in the external table to which the last modified date is written. The last modified date is updated when a call or request in ASM Core is created, updated, or closed as a result of the matching record in the external database. Required. |
MS SQL Table Lookup Connector
The MS SQL Server Lookup Connector requires only database connection details on the Integration Source window. All table lookup information is entered into Outbound and Inbound Actions in the Integration module, or Outbound Action Tasks.
This connector does not write to external tables.
MS SQL Table Outbound Action Connector
Parameters | Description |
Table Name | The name of the external table to be updated with data from ASM Core. Required. |
Internal Item No. Column | A column in the external table to which the ASM Core call or task number, which triggered the outbound action, is written. Required. |
Action Date Column | A column in the external table to which the date of the action is written. Required. |
User Diagnostics
The connector has the facility to trace information. The data can be obtained through Polling tracing or Application tracing.
Installation
No install is required for these connectors. Upon ASM Core install, these connectors are visible in the Integration module, ready to be configured.
Firewall Settings
In certain cases, it is possible that a firewall is present between the external database and the ASM System. In such cases, the firewall needs to be set up so that it allows bi-directional TCP transactions between the ASM System on one side, and the external database on the other side.
Last updated