How to Configure Alerts via the SQL Command Line

Suresh Ramagiri -

Alerts Configuration

Before configuring alerts, we recommend setting the cluster_name and customer_name global variables. If you have multiple clusters, this will help to identify which cluster is sending alerts. This also helps Clustrix Support identify where alerts are coming from if you are sending alerts to our team at support.bot@clustrix.com (recommended). ${alerts_name} will parse out as customer_name - cluster_name and ${cluster_name} will show the value of cluster_name. You can set these globals with:

set global customer_name='<company name>';
set global cluster_name='<cluster identifier (usually similar to node hostnames)>';

There are two system tables in ClustrixDB used to configure alerts: 

alerts_parameters and alerts_subscriptions.

alerts_parameters:

SQL> select * from system.alerts_parameters;
+-------------------+-------------------------------------------------------+
| name | value |
+-------------------+-------------------------------------------------------+
| body_max_chars | 50000 |
| email_subject | ${alerts_name} [${severity}] ${summary} |
| smtp_password | |
| smtp_security | |
| smtp_username | |
| smtp_port | 25 |
| smtp_sender | ${alerts_name} CLX Log Alert <noreply@clustrix.com> |
| smtp_server | smtpserver.mycompany.com |
| subject_max_chars | 100 |
| email_body | Severity: ${severity} |
Date: ${date} ${tz}
Host: ${host}
HWID: ${hwid}
Cluster: ${cluster_name}
Version: ${version}
Image Version: ${image_version}
Message: ${message} |
| email_encoding | quoted-printable |
+-------------------+-------------------------------------------------------+
11 rows in set (0.00 sec)

The default server configuration is intended for testing purposes only and should be changed for production installations.

To set a new SMTP server, you can run the following:

SQL> update system.alerts_parameters set value='smtp.foo.com' where name='smtp_server';

To modify other alerter settings, simply update the row:

SQL> update system.alerts_parameters set value='foo' where name='bar';

alerts_subscriptions: This table contains the email addresses that alerts will be sent to.

SQL> select * from system.alerts_subscriptions;
+-----------------------------+ | address | +-----------------------------+ | support.bot@clustrix.com | +-----------------------------+ 1 rows in set (0.01 sec) 

You can insert, update or delete from this table with regular SQL commands.

For example:

SQL> insert into system.alerts_subscriptions values ('support@clustrix.com');

Note: When any settings are changed the alerter must be told to re-read the config. After modifying any parameters you must run the following for changes to take effect:

SQL> ALTER CLUSTER RESET ALERTER;

Testing Alerts

You can send a test alert via SQL with the following syntax.

SQL> select alert(<alert level>, '<alert text>');

The alert levels are:

0 - CRITICAL
1 - ERROR
2 - WARNING
3 - INFO

The alert text can be anything you desire.

Example:

SQL> select alert(3,'I am a test alert');
+------------------------------+
| alert(3,'I am a test alert') |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

This is the email generated from the above test:

From: foo-cl20dafefv4af047 CLX Log Alert <noreply@clustrix.com>
Subject: foo-cl20dafefb4a7bf047 [INFO] User Invoked From SQL
Date: Mon, Apr 18, 2016 at 12:38PM
To: support.bot@clustrix.com

Severity: INFO
Date: 2016-04-18 07:08:13.084133 UTC
Host: foo
HWID: 00:30:48:be:63:88
Cluster: Clustrix_cluster
Version: 5.0.45-clustrix-7.0.1
Image Version: CentOS release 6.5 (Final)
Message: [SQL] I am a test alert
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk