Monitoring database messages and alerts with MC

• 1: Message center
• 2: Alert configuration
• 3: Setting up SMTP for email alerts
• 4: Custom alerts
• 5: Exporting MC-managed database messages and logs

1 - Message center

The Message Center organizes system performance alerts to help you effectively monitor the state of your database. Pre-configured and custom alerts generate messages when the component they measure exceeds the specified threshold.

Access the Message Center in the following ways:

• On the MC Home page, select Message Center in the MC Tools section.

• Select the message envelope icon in the top-right of any database-specific page, then select Message Center in the quick view.

• On the database Overview tab, select the number (including 0) in the Unread Messages (This Week) widget.

The Message Center can retrieve up to 10,000 of the most recent messages generated for a database. By default, it lists up to 600 messages generated in the previous week. For details on how to retrieve messages that predate the previous week, see Date Filtering.

Filter the messages grid

The Management Console provides options that filter the messages that populate the messages grid by database, keyword, message type, and date. Use one or more of these options to view only the messages that you want to manage.

Database filtering

Select Showing to list the databases that are associated with the logged in user account. Choose a specific database to view only messages for that database, or select (All DBs) to view and manage messages across all of your databases.

Keyword filtering

Use the search bar at the top-right of the screen to list messages that contain the entered text. For example, if you enter 29, the grid lists any messages that contain those characters within the message title or message details available when you click the plus (+) icon to expand the message row.

Message type filtering

After you select a value for Showing, the number of messages for that value are totaled and grouped by type near the top-right corner of the screen, below the search bar. These message types use the syslog standard to define severity levels. The MC message types are defined as follows:

• All Messages: Messages that are not archived or deleted, and are within messageCenter.maxEntries for the previous week.

• High Priority: Messages that you assigned a High Priority alert priority on the Alerts tab. These messages correspond to syslog levels 0 and 1.

• Need Attention: Critical or error messages that indicate that correspond to syslog levels 2 and 3.

• Informational: Warning, notice, informational, or debugging messages that correspond to syslog levels 4, 5, 6, and 7.

To populate the grid with only a single message type, select the number or message type description.

Date filtering

Select Retrieve Older Messages to enter From and To dates to list messages that were generated before the previous week. Vertica stores 10,000 of the most recent database messages so that you can retrieve older messages when necessary.

Message groups

The Message Center groups messages and notifications as Recent Messages, Threshold Messages, and Archived Messages. All message groups use the following priority levels and color codes:

• Critical (Red)

• Warning (Orange)

• Notice (Green)

• Info (Blue)

Recent messages and threshold messages

Recent Messages include the most recent messages generated within the previous week within the messageCenter.maxEntries value. Threshold messages include messages that are generated when the database exceeds a pre-configured, custom, or default alert threshold.

Recent Messages and Threshold Messages are listed using the message type, database name, a description of the message, and the date and time that the message was generated. Additionally, there are collapsible grid headings that group the alerts by Today, Yesterday, and This Week.

Archived messages

Archived messages are messages that you manually saved for later. When you select the check icon for an individual message or select Archive All, MC marks the message as read and archives it. Archived messages do not have the same date or messageCenter.maxEntries restrictions as Recent Messages or Threshold Messages.

The Archived Messages tab contains the following tools to refine search result filters:

• Use the From and To boxes above the grid to define a time frame for the archived message search. To combine multiple time frames, select the Do not clear existing search results checkbox.

• Sort or filter messages even further using the Type, Database Name, Description, and Date columns.

Message actions

Perform actions on all, multiple, or individual messages. To execute actions on all messages at the same time, use the Select All or Select None buttons near the top-right of the screen by the search bar.

Each message row has a checkbox so that you can perform actions on one or more messages simultaneously. Select the plus (+) icon to expand the message row and display the following message details:

• Summary

• Host IP

• Time of occurrence

• Number occurrences of this message

• Description

For additional information about each message, query EVENT_CONFIGURATIONS.

When you select the X icon or select multiple messages and click the Delete Msgs or Delete all buttons, the message is permanently deleted.

2 - Alert configuration

Enable and customize settings for pre-configured or custom alerts for each database. For example, you can set the Threshold Value for Node Disk Usage to a 20% minimum or 80% maximum. If any node exceeds either of those thresholds, the MC generates a message and you receive a notification. Take action on alerts in the Message center.

To access the Alerts tab, log in to the Management Console, then select Go to database > Settings > Alerts.

Configurable settings

To configure any alert, you must toggle the switch on the left of the row to the on position. Node State Change is the only pre-configured alert turned on by default. By default, custom alerts are toggled off.

Pre-configued and custom alerts have the following settings:

• Query variables: Custom alerts only. Query variables are the variables that you added when you created the alert. You must provide a value for each variable. The query variable is not validated after you create the alert. If you update the variable to a value that results in an invalid query, the alert is silently disabled during the next execution.
  To view the original SQL query, hover the mouse over the alert name to display the alert in a tooltip.
  

• Threshold Value: Pre-configured alerts only. You can add a lower and upper limit on acceptable operating values for the component.

• Check Interval: This value determines how often Vertica executes the underlying SQL for the alert.

• Alert Priority: You can assign one of the following priority labels to determine how the alert is distributed:

  • Alert and Critical: Displays the message using the message alert notification mechanisms on the Overview page and creates a message in the Message Center.

  • Warning: Creates a message in the Message center.

• Alert Email Recipients: Configure email notifications for any alert priority when the alert is triggered. You must have SMPT configured. For details about adding email recipients to alerts, see Custom alerts.

• Vertical ellipses: For custom alerts and new resource pool alerts, click the vertical ellipses to delete the alert. You must have MC ADMIN role privileges to delete an alert.

Configuring resource pool alerts

Resource pool alerts use the same configurable settings as the other alerts, but you can set alerts per resource pool. By default, pre-configured alerts are set for the GENERAL pool only.

1. Log in to the Management Console and select Databases > Settings > Alerts.

2. In the top row labeled Resource Pool, select 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 Add Resource Pool Alert +.
   The New Resource Pool Alert window opens.

3. In Alert Name, choose the alert that you want to add to a resource pool.

4. In Resource Pool, choose the resource pool that you want to add this alert to.

5. When you are finished configuring the remaining settings, select Create Alert.

Edit Check Interval, Alert Priority, and Alert Email Recipients as you would other alerts. For guidance, see Configurable Settings or Custom alerts.

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.

3 - Setting up SMTP for email alerts

Management Console can generate email notifications when your database exceeds high-priority alert thresholds. To receive email alerts, you must configure your SMTP settings in MC.

You must be an administrator to provide SMTP settings. To set up MC to send email:

1. Select the Email Gateway tab on the MC Settings page.

2. Provide the following information about your SMTP server:

   • Email Server (Hostname): the hostname or IP of the SMTP server

   • Port: the port of the SMTP server

   • Session Type: the type of connection to use (e.g. SSL)

   • SMTP Username (optional): the username credential for connecting to the server

   • SMTP Password (optional): the password credential for connecting to the server

   • Sender Address: The sender address for your server when it sends email alerts

   • Trust SSL Certificate: Whether to automatically trust the SMTP server's certificate

3. Click Test at the top of the page. MC validates your SMTP settings and sends a test email to the inbox of the email alias you provided.

4. Verify that you successfully received the test email.

5. Click Apply at the top-right of the page to save the settings.

After you set up SMTP for email, you can configure MC to send high-priority threshold alerts through email. For details, seeAlert configuration or Custom alerts.

4 - Custom alerts

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.

Creating a custom alert

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

The following steps create 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.

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 with MC.

   A long-running query runs until it succeeds or returns a Query Error that indicates that there are insufficient resources to continue. To stop a long-running query, select Cancel Query.

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.

   Important

   To prevent the scheduled query from impacting system performance, each query has a default runtime threshold of 30 seconds. If the query runtime exceeds this threshold once, the alert is automatically disabled.

   To adjust the timeout threshold, edit the threshold_query.disable_time_out setting in the /opt/vconsole/config/console.properties file.

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.

Editing a custom alert

You must have MC ADMIN role privileges to edit 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, locate the custom alert that you want to edit.

3. Select the vertical ellipses, and select Edit.
   The Edit custom alert window opens and displays the previously saved values for the custom alert.

4. Edit the alert. You can edit any of the following alert properties:

   • Alert Name

   • SQL Query

   • Any variable values

5. 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 with MC.

   A long-running query runs until it succeeds or returns a Query Error that indicates that there are insufficient resources to continue. To stop a long-running query, select Cancel Query.

6. When you are satisfied with the query results, select Update Alert.

Deleting an alert

To delete a custom 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.

5 - Exporting MC-managed database messages and logs

You can export the contents of database messages, log details, query details, and MC user activity to a file.

Information comes directly from the MC interface. This means that if the last five minutes of vertica.log information displays on the interface, you can save that five minutes of data to a file, not the entire log. When you filter messages or logs, MC exports only the filtered results.

Depending on how you set your browser preferences, when you export messages you can view the output immediately or specify a location to save the file. System-generated file names include a timestamp for uniqueness.

The following table shows, by record type, the MC pages that contain content you can export, the name of the system-generated file, and what that file's output contains: