SQL execution

Overview

GermainAPM offers a number of ways to automate an action. A SQL script can be automatically executed to automate real time 1)reporting or 2)data update (in any database) as a consequence of a SLA breach.

A SQL Report action will execute queries against a selected target database and allow query output to be sent in an email or as an email attachment to recipients.

SQL Report Configuration Reference

Workspace Field Label

Configuration Key

Description

Possible Values

Default Value

Workspace Field Label

Configuration Key

Description

Possible Values

Default Value

Name

name

Unique name for the action

String



Execute via Engine?

remote

If set, the system will log all occurrences of this action.

Boolean



Target Server

targetExpression

MVEL expression to identify target server for a remote action.

String



Logging Enabled

loggingEnabled

If set, the system will log all occurrences of this action.

Boolean



Notify On Success

notifyOnSuccess

If set, will create a notification whenever this action succeeds.

Boolean



Notify On Failure

notifyOnFailure

If set, will create a notification whenever this action fails.

Boolean



Execution Count

limitCount

Used in combination with limitInterval to define upper limit on how many times to execute this action.

Integer



Execution Interval

limitInterval

Used in combination with limitCount to defines interval during which upper limit applies.

String, one of the following values:
YEAR, MONTH, WEEK, DAY, HOUR, MINUTE



Distribution List

alertGroupName

Name of the alert distribution group for this alert.

String



Alert Template

templateName

Template to use for this report.

String



Suppress email if result is empty?

suppressIfEmptyResult

If set, will not send report if result set is empty.

Boolean



Monitored Database

monitoredDatabase

Must be one of the configured databases.

String



Credentials

credentials

Must be one of the configured credentials.

String



Attach Result?

attachResult

If set, will attach result set to report.

Boolean



Datamart Query?

datamartQuery

If set, query will be run against the APM datamart.

Boolean



Run on schedule?

schedule

*Calculated Field* if set to true, allows schedule cron expression to be defined

Boolean



 

references

*Advanced Field* References to configuration entries associated with this action.

Allows other configuration items such as credentials to be used within the Template as a key/item pair that can then be used within the Alert Template, eg. §{Credentials.username} .*option1*

Also allows for direct key/value references to be defined that do not reference other configuration items. *option2*

name - unique reference name
eg. Credentials
key *option 1*- germain apm config collection key
eg. germain.apm.monitoringConfig.credentials
itemName *option 1* - germain apm config item key
eg. SSHProdCredentials
Value *option 2* - hard value for reference
Password1



SQL Queries

sqlQueries

List of SQL queries to include in the report.

List of SqlReportQueryConfig:
Name, sqlText, intervalType, attachmentNameTemplate



Triggered By SLAs

 

*Calculated Field* Collection of SLAs this action is configured for

 

 

 

Example - Configure a SQL Query Action - Scheduled GermainAPM Login Report

Navigate to Automation > SQL

In this screen you'll be able to easily spot which SQL actions are available and configured to trigger on SLAs
Clicking on an existing action will bring you to the respective configuration page.
In order to create a new SQL action we'll click the + symbol up top which will bring up our SQL Action Wizard

In the example below we’ll schedule an email report to be sent out daily, which will have an attachment with all the logins against GermainAPM for the previous day.

Step 1 - SQL Action

Type in the SQL Action name.
Select or create which distribution we’ll send this report to.
Select or create the template used to send this email report.
Select Attach Result.

Step 2 - Connection

Select Run against Germain APM Datamart, otherwise this is where you can define which database and credentials will be used to run the query against.

Step 3 - SQL

In this screen we’ll define which queries are to be run, for this example we’re querying Germain’s own Datamart, but this can be any query against any database you would configure in the previous screen.

Step 4 - Schedule

Since we want a scheduled report, we’ll select the option to run on schedule, which then allows us to define a cron expression that will define the report schedule.

Step 5 - SLA

SqlReport actions can also be triggered by SLAs, alternatively to a defined schedule, here you can attach this action to an SLA that would make the SqlReport to run once the SLA is breached.

Since we’re defining a scheduled report, let’s click Finish without defining an SLA.

We will then receive the following alert: