Example: Configuring a SQL Statement as an Alert Action - Teradata Viewpoint

Teradata Viewpoint User Guide

Product
Teradata Viewpoint
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
hkd1478709580913.ditamap
dita:ditavalPath
2206_User_vp_1610.ditaval.ditaval
dita:id
B035-2206
lifecycle
previous
Product Category
Analytical Ecosystem
Suppose you want to run the following SQL insert statement as an alert action for the Teradata Database 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 Database system, perform these steps.

  1. In the Alert Setup portlet, configure the login credentials for TEST1.
    1. From the Setup Options list, click Delivery Settings.
    2. From the Authentication list, click Teradata Login.
    3. Click 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] Click Test to verify that 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, click Delivery Settings.
    2. From the Delivery Types list, click SQL Queries.
    3. Click 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, click Alert Presets.
    2. From the Preset Options list, click Action Sets.
    3. Click next to Action Sets.
    4. In Action Set Name, type runSessionCount.
    5. From the Times list, check the Core check box, and clear both the Evening and Weekend check boxes.
    6. Under Actions, ensure 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, click 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. Click next to Systems and 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, click TEST1.
    2. From the Setup list, click Data Collectors.
    3. From the Data Collectors list, click 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, click TEST1.
    2. From the Setup list, click Alerts.
    3. From the Alert Types list, click 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.