SQL execution

Feature

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

Configure

Wizard

Germain Workspace > Left Menu > Wizard > SQL Action

SQL Report Reference

Workspace Field Label	Configuration Key	Description	Possible Values
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 config collection key eg. germain.apm.monitoringConfig.credentials itemName option 1 - Germain 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 Germain 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 Germain 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 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:

Component: Engine

Feature Availability: 8.6.0 or later