Skip to main content
Skip table of contents

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

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

  • Enter the Action “Name”

Service: Automation

Feature Availability: 8.6.0 or later

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.