16.20, 16.50 - Example: Configuring an SQL Statement as an Alert Action - Teradata Workload Management

Teradata® Viewpoint User Guide

prodname
Teradata Viewpoint
Teradata Workload Management
vrm_release
16.20
16.50
created_date
June 2020
category
User Guide
featnum
B035-2206-107K
Suppose you want to run the following SQL insert statement as an alert action for Teradata system TEST1.

This example applies only to core hours.

INSERT INTO admin.sessionHist (TheTime, UserName, SessionCount)
VALUES (CURRENT_TIMESTAMP, ${userName}, ${sessionsForUser})

This insert is to be performed when the session count for any user on the TEST1 system exceeds ten.

Notice the alert properties ${userName} and ${sessionsForUser} in the insert statement above. When you configure an alert action to run SQL statements using the SQL Queries delivery type, the text can include alert properties that will be replaced with runtime values. See Teradata Database Alert Metrics and Properties for a description of the alert properties available for Teradata Viewpoint alert types.

For this example, assume the target table (admin.sessionHist) has already been created on TEST1. For example:
CREATE TABLE admin.sessionHist
 (TheTime TIMESTAMP NOT NULL,
 UserName VARCHAR(256) NOT NULL,
 SessionCount INTEGER NOT NULL)

To configure an SQL statement as an alert action for a Teradata system, perform these steps.

  1. In the Alert Setup portlet, configure the login credentials for TEST1.
    1. From the Setup Options list, select Delivery Settings.
    2. From the Authentication list, select Teradata Login.
    3. Select "" next to Teradata Login.
    4. In the TDPID box, type the TDPID of the TEST1 system.
    5. Select the Enable login check box.
    6. Enter a login name and password.
    7. From the Session Character Set list, select UTF8.
    8. [Optional] From the Test Teradata Logon list, select JDBC.
    9. [Optional] Select Test to verify the login settings are correct.
    10. Click Apply.
  2. In the Alert Setup portlet, configure the SQL query for TEST1.
    1. From the Setup Options list, select Delivery Settings.
    2. From the Delivery Types list, select SQL Queries.
    3. Select "" next to SQL Queries
    4. In NAME, type insertSessionHistory.
    5. Select the Enable check box.
    6. In SQL, type:
      INSERT INTO admin.sessionHist (TheTime, UserName, SessionCount)
      VALUES (CURRENT_TIMESTAMP, ${userName}, ${sessionsForUser})
    7. Click Apply.
  3. In the Alert Setup portlet, create an Action Set.
    1. From the Setup Options list, select Alert Presets.
    2. From the Preset Options list, select Action Sets.
    3. Select "" next to Action Sets.
    4. In Action Set Name, type runSessionCount.
    5. From the Times list, select the Core check box, and clear both the Evening and Weekend check boxes.
    6. Under Actions, make sure the Include in Alert Viewer check box is selected.
      This option is selected by default so you can view alerts for this action set in the Alert Viewer portlet.
    7. From the Actions list, select the SQL check box.
    8. From the Query list, select insertSessionHistory.
    9. From the TDPID list, select TEST1.
    10. Click Apply.
  4. In the Monitored Systems portlet for Teradata Viewpoint monitoring, configure TEST1.
    1. Next to Systems, select "", then select Add Teradata System.
    2. In System Nickname, type TEST1.
    3. Select the Enable system check box to activate the TEST1 system for monitoring.
    4. In the TDPID box, type the TDPID of the TEST1 system.
    5. Enter a login name and password.
    6. Click Apply.
  5. In the Monitored Systems portlet, configure the data collectors.
    1. From the Systems list, select TEST1.
    2. From the Setup list, select Data Collectors.
    3. From the Data Collectors list, select Sessions.
    4. Select the Enable Sessions Collector check box and keep the default settings.
    5. Click Apply.
  6. In the Monitored Systems portlet, define alert rules.
    1. From the Systems list, select TEST1.
    2. From the Setup list, select Alerts.
    3. From the Alert Types list, select Session.
    4. Click "" next to Alerts.
    5. In Alert Name, type userSessionCountAlert.
    6. Select the Enabled alert check box.
    7. From the Severity list, select High.
    8. From the Match list, select All.
    9. From the lists, select Sessions Per User and is greater than.
    10. In the empty box below the lists, type the threshold number 10.
    11. From the Action list, select runSessionCount.
    12. Click Apply.
  7. If no single user on TEST1 has more than ten sessions, then create the conditions for the alert to be raised by opening 11 sessions for a Teradata user.
    For example, using BTEQ:
    .set sessions 11
    .logon TEST1/myUser,myPassword
  8. On the TEST1 system, review the sessionHist table. Use a query tool such as BTEQ or SQL Scratchpad to query the table.
    For example:
    select * from admin.sessionHist order by TheTime
    You may need to wait a few minutes for the alert to be triggered, depending on the Teradata session monitoring rate on TEST1.
  9. In the Alert Viewer portlet, review triggered actions.