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” → (plus) → “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: 

      • <!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>
        CODE

    • 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 (plus) to add an action

  • Enter the Action “Name”