Generic SQL

The generic SQL import agent for Elimity Insights queries your on-premise SQL instances (Microsoft SQL Server or Oracle Database) and uploads the results to your Elimity Insights server. You configure the database connections, the queries to execute, the expected result schema and the connection with your Elimity Insights server. The import agent will then connect to your databases, execute the configured queries, transform the results to a format compatible with Elimity Insights and send the transformed data to your Elimity Insights server.

Installation

The import agent is available as a Docker image for Linux and Windows. Refer to the dedicated knowledge base article for all details about working with agents and gateways. Visit https://console.cloud.google.com/artifacts/docker/elimity-general/europe-west1/docker/sql-import-agent to get a list of available image tags for this specific agent.

Generic usage

Running the import agent with the `-h` option prints a generic usage message:

$ docker run --rm europe-west1-docker.pkg.dev/elimity-general/docker/sql-import-agent:<tag> -help
Usage:
  -print-config-schema
    print a JSON schema definition for the configuration file and exit
  -print-data-model-export
    print a data model export file for creating a custom source in Elimity Insights and exit
  -print-example-config
    print an example HJSON configuration file and exit

Step-by-step deployment guide

The following sections will explain the different steps you'll need to take to deploy the generic SQL import agent for Elimity Insights.

1. Configuring the agent

You can get an example configuration file by running the import agent with the `-print-example-config` flag and writing its output to a `config.hjson` file:

$ docker run --rm europe-west1-docker.pkg.dev/elimity-general/docker/sql-import-agent:<tag> -print-example-config > config.hjson

This example configuration will simply import all principals and permissions from a Microsoft SQL Server database. Alternatively, you can find example configuration files to import from MySQL and Oracle databases in the attachments at the bottom of this page. Leave the `entityTypes` and `relationshipTypes` configuration properties untouched if you want to try any of these example imports. Edit the following properties in your configuration file to adjust the import agent as needed:

• `cronPattern`: optional CRON pattern describing when the import agent should run (refer to https://crontab.guru for example patterns); omit if you just want to run the agent once
• `entityTypes`: a list of configuration objects describing how to import entities of a specific type into Elimity Insights
  • `attributes`: a list of configuration objects describing how to import attributes of this entity type's entities
    • `id`: unique identifier of the attribute type for which the agent should import assignments
    • `name`: human-readable name of the attribute type (*)
    • `type`: data type of the attribute type, one of `boolean`, `date`, `dateTime`, `number`, `string` or `time`
  • `icon`: icon of the entity type (*) 
  • `id`: unique identifier of the entity type for which the agent should import entities
  • `plural`: plural form of the entity type's name (*)
  • `query`: query that the import agent should send to the configured SQL instances, each resulting row corresponds to an entity of this type
  • `singular`: singular form of the entity type's name (*)
• `insightsSourceId`, `insightsSourceToken`: you can leave these properties untouched for now, we'll come back to them later
• `insightsUrl`: URL of your Elimity Insights server
• `db2Dsns`: list of connection strings that the import agent should use to connect with your IBM Db2 instances; we recommend connecting with a dedicated read-only user
• `mssqlDsns`: list of connection strings that the import agent should use to connect with your Microsoft SQL Server instances (we use Microsoft's official Go MSSQL driver behind the scenes, refer to its documentation on supported connection string formats); we recommend connecting with a dedicated read-only user (e.g. by only assigning the `db_datareader` role)
• `mySqlDsns`: list of connection strings that the import agent should use to connect with your MySQL instances (we use the de facto standard MySQL driver for Go behind the scenes, refer to its documentation on supported connection string formats); we recommend connecting with a dedicated read-only user 
• `oracleDsns`: list of connection strings that the import agent should use to connect with your Oracle Database instances (we use the de facto standard Oracle Database driver for Go behind the scenes, refer to the implementation of their connection string parser); we recommend connecting with a dedicated read-only user
• `relationshipTypes`: a list of configuration objects describing how to import relationships between entities of two specific types into Elimity Insights
  • `attributes`: a list of configuration objects describing how to import attributes of this relationship type's relationships
    • `id`: unique identifier of the attribute type for which the agent should import assignments
    • `name`: human-readable name of the attribute type (*)
    • `type`: data type of the attribute type, one of `boolean`, `date`, `dateTime`, `number`, `string` or `time`
  • `from`: unique identifier of the entity type from which the relationships start
  • `query`: query that the import agent should send to the configured SQL instances, each resulting row corresponds to a relationship between two entities of the configured types
  • `to`: unique identifier of the entity type where the relationships end
• `transactionIsolationLevel`: a number representing which isolation level the agent should use for the transaction in which it combines all queries; should be one of the following:
  • 0: default determined by SQL instance (this value is always used for DB2 and Oracle Database instances)
  • 1: read uncommitted
  • 2: read committed
  • 4: repeatable read
  • 5: snapshot (for Microsoft SQL Server this is used when you omit the `transactionIsolationLevel` field)
  • 6: serializable (for MySQL this is used when you omit the `transactionIsolationLevel` field)

Properties marked with a (*) do not affect the importing functionality, they are only necessary to generate a data model export file when running the import agent with the `-print-data-model-export` flag.

2. Creating a custom source in Elimity Insights

The import agent also includes some functionality to help you with creating a source in Elimity Insights. Assuming you correctly configured the properties marked with a (*) in the previous section, the following command will write a data model export file to `data-model-export.json`:

$ docker run -v ./config:/app/config --rm europe-west1-docker.pkg.dev/elimity-general/docker/sql-import-agent:<tag> -print-data-model-export > data-model-export.json

You can now upload this file to Elimity Insights when creating a new custom source. Copy the resulting source's identifier and token to the `insightsSourceId` and `insightsSourceToken` properties in the agent's configuration file.

3. Deploying the agent

Having configured the agent and having created a source in Elimity Insights, you can now deploy the agent to regularly import data from your SQL databases and upload it to Elimity Insights. Since we distribute the agent as a Docker image, our recommendation for deployment is to use your cloud provider’s dedicated job execution platform (e.g. Google Cloud Run, Azure Container Apps, …). If that's not an option, you can also manually deploy the image on e.g. Windows Server. Refer to the dedicated knowledge base article about installing import agents for additional details.

4. Following up on the import

The import agent should start outputting some logs to indicate its progress, for a manual Windows Server deployment you can check these with `docker-compose logs sql-import-agent`.

SQL query format

When writing queries to configure the import agent, you should make sure they adhere to the following rules:

• for entity queries:
  • the number of output columns should equal the number of configured attributes plus two
  • the first output column should have a textual type and represents the entity's id
  • the second output column should have a textual type and represents the entity's name
  • the following output columns should type-match with the entity type's configured attributes (so order of configured attributes is important)
• for relationship queries:
  • the number of output columns should equal the number of configured attributes plus two
  • the first output column should have a textual type and represents the source entity's id
  • the second output column should have a textual type and represents the target entity's id
  • the following output columns should type-match with the relationship type's configured attributes (so order of configured attributes is important)
• the import agent will not generate attribute assignments for output columns that have a `NULL` value