Creating a Custom Alert

Create custom events-based alerts to track abnormalities and performance fluctuations for node health, queries, and resource pools using your own database schemas and tables. When the database triggers an active alert, you receive notifications according to the alert priority, and can take action in the Message Center.

About this Tutorial

The following tutorial creates a custom alert named Failed logins within a X time period to track the number of failed logins in the previous two hours, per user. This alert might indicate a possible distributed denial-of-service (DDoS) attack, or an application behaving inappropriately. The underlying SQL query uses a variable to create a dynamic threshold value that you can fine-tune after you create the alert.

Creating the Alert

You must have MC ADMIN role privileges to create a custom alert.

  1. Log in to the Management Console, then select Go to database > Settings > Alerts.
  2. In the Custom Alerts row at the top of the page, click the blue box with a plus (+) symbol on the far right of the row. When you hover over the button, the button expands and displays Create Custom Alert +.
    The Create custom alert window displays.
  3. In Alert Name, enter Failed logins within X time period.
  4. In SQL Query, enter the following SQL query:

    SELECT
      login_timestamp,
      user_name,
      node_name,
      client_hostname,
      reason
    FROM
      login_failures
    WHERE
      reason in ('INVALID USER', 'FAILED', 'INVALID DATABASE')
      AND login_timestamp > sysdate - INTERVAL '{{Time_Interval}}'

    The AND clause of the preceding query encloses the variable of type String named Time_Interval in double curly braces to represent a valid SQL syntax element.

    A variable is a dynamic value that is evaluated at runtime that you can configure after you save the alert. You can add up to 6 variables per custom alert. Variable names may consist of a maximum of 14 alpha-numeric characters, including underscores. Verify that the variable uses the correct data type and format. Variables of type String require single quotes around the curly braces.

    A SQL query triggers an alert if it returns one or more rows. Use the formatting or full screen buttons above and to the right of the SQL Query box as needed.

  5. A box displays below the SQL Query box containing placeholder text that corresponds to each variable name. To test the alert, enter 2 Hours in this box.
  6. Select Run Query. The Query Results section displays any rows returned from your query. Alternatively, you might encounter one of the following issues:
    • If you use invalid SQL syntax, you receive an error.
    • If the query returns more than 5 columns or 100 rows, you receive a warning. Because every query result set is saved to disk, it is important to be aware of the size of your result set. For more information, see Monitoring Database Messages and Alerts in MC.
    • The query timeouts if the runtime exceeds 240 seconds. To adjust the number of seconds before a timeout, edit the threshold_query.max_execution_time in /opt/vconsole/config/console.properties.
  7. When you are satisfied with the query results, select Create Alert.
    The Create custom alert window closes and the alert you just created is listed in the Custom Alerts section on the Alerts page. When you point the mouse on the query name, the query is displayed in a tooltip. Under the query name, there are editable boxes that correspond to the variables you added when creating the alert.
  8. In the Time_Interval variable box, enter 2 Hours.
  9. Select a value for Check Interval. The default setting is 10 minutes. This value determines how often the query is executed.
  10. Select a value for Alert Priority. By default, the alert is assigned the Critical value priority.
  11. Optionally, select the Manage Emails icon under Alert Email Recipients to send an email alert to specific users when the alert is triggered. To register a user to receive email alerts, complete the steps in Setting Up SMTP for Email Alerts.
  12. Complete the following steps in the Manage Email Recipient window:
    1. To add an existing user to an alert, click the checkbox beside the existing MC user, or select the box at the top to add all. For non-existing MC users, enter their email address at the bottom of the window.
    2. In Email Interval, select how often the email is sent:
      • Immediately
      • One hour: The hour starts when you make the alert.
      • One day: Users receive the alert 24 hours after you create the alert.

      For example, if you select One hour, an email is sent every hour, even if the alert is triggered multiple times within the hour.

    3. Click Save.

After you create the alert, toggle it on or off using switch at the far left of the alert row. To delete the alert, select the vertical ellipses at the right of the row, and select Delete. You must have MC ADMIN role privileges to delete an alert.