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 install a Magic Suite Agent in your own Windows, Linux or macOS 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.
Agents securely connect to the Magic Suite API using HTTPS and API credentials, and listen for instructions to execute, such as SQL queries. They only make outbound connections, so no inbound firewall rules are needed.
Installing and configuring an Agent
Downloading, installing, upgrading, configuring and uninstalling the Agent are all described on the Magic Suite Agent page, which is the one place that covers them. This page covers the Agent Connection and using the Agent from reports.
Creating the Agent Connection
Each Agent has its own Connection, though several Agents can share one for resilience.
- In Admin, open Connections, then click Create
- Choose Agent as the type, enter a name, and save
- In the Connections table, the Agent's Connection ID shows in brackets after its name. For a Connection called "Finance Database" it may show "Finance Database (34)". Use that number as the ConnectionId setting in the Agent's configuration
Optional Per-Request Configuration
There are two optional settings in the Configuration of an Agent Connection. They apply to each request, for example when the Agent queries an SQL database:
- sqlMaxTryCount: the number of times a command or macro tries again if it fails or times out
- sqlRetryIntervalSeconds: the time in seconds between those tries
Set them as JSON in the Configuration of the Agent Connection, for example:
{
"sqlMaxTryCount": 12,
"sqlRetryIntervalSeconds": 60
}
Using Agent Macros
Use the Agent macros to tabulate and graph one-off reports. To do this:
- In Admin, open Connections and make sure you have an Agent Connection. Note its name
- In Admin, open Macro Parameter Defaults and add a parameter of type Agent Connection, entering the Connection's name as the default value
- Use the Agent macros in Report Studio or in a report
For example:
[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]
For more details, see the help for the individual Agent macros.