Include DB Data in an Alert
Feature
How to automatically include Data that is fetched from a Database into an Alert.
Configure
In this example we will be using a table called “WorkMon Backlog” (table available in an external application called Siebel).
Steps:
“Deploying the Monitor” and all requirements
“Creating the Action (SQL Report / Alert Email)” and all requirements
“Adding the Action to the SLA”
Deploy Database Query Monitor Deployment
Germain Workspace > Left Menu > Wizards > “Database Query Monitor Deployment”
Enter the Database details
“Database”: The database you wish to check. This this case a Siebel Database
“Credentials”: The credentials for the appropriate user on the above database.
“Application Name”: Siebel Database
Select the Component
To create the Component: See the “Creating the Component” section for details
Enter the Monitor details
“Monitor Name”: “WorkMon” Backlog Monitor
“Monitoring Node”: Choose node with network access to the database
“Engine”: Choose appropriate engine on the Node
Configure the schedule to business needs
Click “Close”
Create Database Query Monitor Component
Enter the “Database Query Monitor Component” Wizard in one of two ways
From inside the “Database Query Monitor Deployment” Wizard
From the “Component Types” List
“System” → “Component Types” →
→ “Database Query Monitor Component”
Enter the “Component Name”
“WorkMon Backlog”
Enter the Database Credentials for Validation
“Database”: The database you wish to check. This this case a Siebel Database
“Credentials”: The credentials for the appropriate user on the above database
Enter the Query details
“Query Name”: WorkMon Backlog Count
“SQL”:
SELECT COUNT(*) FROM SIEBEL.S_ESCL_REQ
Enter the Mapping details
“KPI”: WorkMon Backlog
If you have not created it yet, see section “Creating the KPI“ for details
“KPI Field Mapping”
Hit the plus to add a line
Enter: Left side: 0, Right Side: value
Enter the SLA details
“SLA Threshold Value”: 2500
“SLA Color”: Red
Do not add and SLA actions yet. See section “Adding the Action to the SLA” after all other steps
Click “Close”
Creating the KPI
Enter the “KPI” Wizard in one of two ways:
From within the “Database Query Monitor Component” Wizard:
Through the “KPI” list screen
Enter the details:
Name: “WorkMon Backlog”
Fact Class: “Generic Metric”
Fact Category: “WorkMon Backlog Data”
(Optional)Description
Creating an SLA
Enter the SLA Wizard in one of two ways (There are more ways as well):
Continuing in the KPI Wizard (It is easy to create SLAs, you do not need to create them all here now)
From within the KPI:
Enter the SLA details:
“SLA Name”: WorkMon Backlog SLA
“Threshold Value”: 2500
“Color if not met”: Red
Click Finish (We will add the Action later)
Creating the Action (SQL Report / Alert Email)
Enter the “SQL Action” Wizard:
Through the “SQL Report” List
Enter the Action details:
“Name”: WorkMon Backlog Report
“Distribution List”: Distribution list for the appropriate Admins
“Report Template”: WorkMon Backlog SLA
“Attach Result?”: Checked
Enter the Connect details
“Database”: The database you wish to check. This this case a Siebel Database
“Credentials”: The credentials for the appropriate user on the above database
Enter the Query details
“Name For SQL Query”: WorkMon Backlog Count
“SQL Query”:
SELECT * FROM SIEBEL.S_ESCL_REQ
“Attachment Name Template”: Not requires, but you could modify to embed the date in the file name
WorkMon_Backlog_Report-${.now?date}
Click “Next”
Click “Finish”
Creating the Alert Template
Enter the “Alert Template” Wizard
From inside the “KPI” Wizard:
Through the “Alert Templates” List:
Enter The Templates “Name”:
Hit “Next” (We do not need a text template for this action)
Enter the HTML details:
SMS Title:
Germain Alert - WorkMon Backlog is at ${context.results.rows?size} (of 2500)
SMS Content:
- CODE
<!DOCTYPE html> <body> <h3 style="display: inline-block;font:Verdana; padding: 3px !important; font-weight: bold; color: #ffffff; background-color: #3a87ad; white-space: nowrap;">Daily User Login Report</h3> <div>The WorkMon Backlog has exceeded the SLA threshold. See attached results </div> <div> <p>Created: ${context.createdOn.format('yyyy-MM-dd HH:mm:ss')}</p> </div> <#if context.results.rows?size == 0> <div> <strong> No results for this period. </strong> </div> <#else> <div> <table border="1" cellpadding="6"> <thead> <tr> <th>REQ_ID</th> <th>CREATED</th> <th>GROUP_ID</th> <th>RULE_ID</th> <th>TBL_NAME</th> </tr> </thead> <tbody> <#list context.results.rows as row> <tr> <td>${row.getColumnValue("REQ_ID")}</td> <td>${row.getColumnValue("CREATED")}</td> <td>${row.getColumnValue("GROUP_ID")}</td> <td>${row.getColumnValue("RULE_ID")}</td> <td>${(row.getColumnValue("TBL_NAME"))!}</td> </tr> </#list> </tbody> </table> </div> </#if> <#include "DISCLAIMER"> </body> </html>
Use the “Validate” button for both
Hit “Finish” (We do not need an SMS template for this action)
Hit “Close”
Add the Action to the SLA
Germain Workspace > Left Menu > Analytics > KPIs > WorkMon Backlog > WorkMon Backlog SLA
Edit the SLA by going to the KPI, then clicking on the SLA
Click on the threshold
Click the Plus
to add an action
Enter the Action “Name”
Service: Automation
Feature Availability: 8.6.0 or later