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 "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 ReportMagic Agents you can deploy and they come at no additional cost.

ReportMagic Agents securely connect to the ReportMagic 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 ReportMagic API is based. This will normally be https://api.docs.magicsuite.net/

API Token Name / Key

The Agent needs some credentials (an API Token) to connect to the API. Go to your Profile (icon top left in the UI) | My API Tokens and add a token. Note down the name and key.
Note that the API Token must be associated with a user permanently associated with your company (Tenant). Best practice is to create a dedicated "Agent" user for this purpose.

Connection ID

Each Agent should have its own Connection ID, though it is possible for multiple Agents to share a Connection ID for resilience purposes. In the ReportMagic UI, go to Admin | Connections and create a new Agent Connection. Note the connection ID will show in brackets after your provided name. For example if you called your connection "Finance Database", the UI may show: "Finance Database (34)". Use the number (in this case, 34) for the "ConnectionId" setting in your appsettings.json file.

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

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

You may set this as a JSON item in the Configuration section, 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.
A 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 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.