Connecting to In-House Systems Using Agents

Although ReportMagic primarily reports on cloud systems, in some cases you will need to report on data held behind your firewall, or accessed from a specific IP address.

For example, you may wish to run queries directly on a Microsoft SQL Server or PostgreSQL database.

To achieve this, you can install "Magic Suite Agents" (sometimes referred to as "ReportMagic Agents") in your own Windows or Linux environment, for example behind your firewall, or in your AWS or Azure environment.

There is no limit to the number of Agents you can deploy and they come at no additional cost.

Magic Suite Agents securely connect to the Magic Suite API using HTTPS and API credentials and listen for macro instructions to execute SQL queries.

Running as a Windows Service, Agents:

Provide the highest levels of security
Install with an MSI installer
Do not require you to provide any credentials, other than when setting up the Windows Service

Installing Agents

To install Agents on Windows:

Prepare a host with appropriate access to your data
Download the MSI installer
There is no configuration required during installation, so just click through the installer
Follow the instructions in "Configuring Agents" below

To install Agents on Linux:

Prepare a host with appropriate access to your data
Download the Linux installer
Follow the instructions detailed in the Readme file included in the Zip
Follow the instructions in "Configuring Agents" below

Configuring Agents

After installation, you should copy the file (for example, on Windows):

C:\Program Files\ReportMagic Agent 3\appsettings.example.json

...to...

C:\Program Files\ReportMagic Agent 3\appsettings.json

Edit the new file. You will need to run your editor with elevated privileges if you want to edit it in place

API URL

The Agent requires the ReportMagic API URL to work properly. The setting in appsettings.json is called "ApiUrl", and should be set to the URL where the Magic Suite API is based.

This will normally be https://api.‍magicsuite‍.net/

API Token Name and Key

The Agent needs some credentials (an API Token and Key) to connect to the Magic Suite API.

In ReportMagic, and as a Tenant Admin, choose 'Admin' from the menu and then 'API Tokens', then click the Create button to add a token. Note down the name and key.

Alternatively, in the Admin app, choose API Tokens, and do the same.

Note that the API Token must be associated with a user permanently associated with your company (Tenant). The best practice is to create a dedicated "Agent" user for this purpose.

The API Token should go into the "ApiTokenName" section of appsettings.json, and the API Token Key should go into the "ApiTokenKey" section.

Connection ID

Each Agent should have its own Connection ID for Magic Suite, though it is possible for multiple Agents to share a Connection ID for resilience purposes.

In ReportMagic go to Admin > Connections and create a new Agent Connection. Alternatively, in the Admin app, choose Connections and do the same.

Observe that once created, in the Connections table, the Agent's Connection ID will show in brackets after its name. For example if you called your connection "Finance Database", the Agent's name may show: "Finance Database (34)". Use the number (in this case, 34) for the "ConnectionId" setting in your appsettings.json file.

Maximum Concurrent Requests

In Magic Suite / ReportMagic version 3.27 onwards, the Agent can more effectively respond to multiple concurrent (simultaneous) requests ensuring that, for example, a long-running SQL query does not impact its ability to do other work such as respond to status requests or perform other queries.

In its appsettings.json file, this JSON property is called "MaxConcurrentRequests" and takes a default value of 5 if omitted.

Even when the property is set, the number of concurrent requests can never exceed the core count of the CPU on which the Agent runs, but you may wish to set a specific value to limit or increase how much resources it can use.

Optional Per-Request Configuration

Note that there are 2 optional configuration items for Agent Connections.

These apply on a per-request basis (e.g. when an Agent queries an SQL database):

sqlMaxTryCount - the number of times any commands or macros will attempt to retry if they fail or timeout
sqlRetryIntervalSeconds - the time in seconds between retries relating to the above item

You may set these as a JSON item in the Configuration section of the Agent Connection, e.g.

{
  "sqlMaxTryCount" : 12,
  "sqlRetryIntervalSeconds" : 60
}

Logging

The Agent uses the Serilog logging library, and the following Serilog sinks are available as standard (click on each for relevant documentation):

Each can be configured in the Serilog section at the end of the appsettings.json file.

Installation Troubleshooting

Windows

When installing, if you get a retry / cancel error when the setup process is trying to start the service then it is possible the user is not permitted to run as a service.

Without cancelling the dialog box:

Open Services (for example, by going to the Windows Start menu and typing Services)
If the ReportMagic Agent 3is not listed as a service, you may not have used an appropriate Windows account, so cancel the installation and retry with a different service identity
If the ReportMagic Agent 3 is listed as a service, right-click it and select Properties
In the Properties dialog box, click the Log On tab. Check the username is correct, then retype your password and confirm it, then click Apply. message should appear confirming that the user has been granted permissions to log on as a service
Return to the installation dialog box and click Retry
The service should now start and the setup process complete

Linux

The Agent runs as a Linux service and will write to the system log. Please contact support if assistance is required in interpreting this log.

Using Agent Macros

Use the Agent macros to easily tabulate and graph one-off reports. To do this:

In ReportMagic, from the Admin menu, click Connections and ensure you have an Agent connection and note its name
From the Admin menu, click Macro Parameter Defaults and then add a new parameter of type Agent Connection, entering the connection name in the default value box
Use the Agent macros in Report Studio or in a report. For example, the [Agent.SqlTable] macro can be used to the same effect as the direct query shown above:

[Agent.Connection: name="Agent on TEST"]

Title only: [Agent.SqlTable: sql="SELECT Title FROM BloggerPosts", connectionString="Server=.;Database=MyDatabase;Trusted_Connection=True;", timeoutMs=30000]

All table data: [Agent.SqlTable: sql="SELECT * FROM BloggerPosts", connectionString="Server=.;Database=ReportMagic;Trusted_Connection=True;", timeoutMs=30000]

Currently, supported Agent features include:

SQL queries for values, tables, and graphs

For more details, see the help for individual Agent macros.

Uninstalling Agents

To uninstall the ReportMagic Agent software, using Add/Remove Programs, as you would with any program.