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