Inventory of system alerts for SQL auditing

Applies to

ApexSQL Audit


This article explains the usage of system-level alerts in SQL auditing environments.


In complex SQL audit environments, it is always welcomed to have mechanisms that can provide real-time notifications on critical events that may or have interrupted auditing processing. Those events include low storage space, network issues, remote SQL Server inaccessibility, etc. In general, detecting system-level issues in SQL auditing and prompt notification on them is a very important step in their quick resolution.

ApexSQL Audit application includes a batch of built-in or system type alerts that enable relaying critical information in a fast and comprehensive manner. In continuation, this article will explain what those system alerts are and how they can be configured and used.

Alert management

Both system and custom alerts are managed in the same section of the application – from the Manage tab of the Alerting section in the Home menu. When opened, the tab will display a list of system alerts that are built-in in the application. Which means they will be present (and active) there from the start.

Representation of system alerts

The following represents the list of available system alerts with their default severity level and ordered by severity:

  • Central repository database inactivity alert
  • Disk space alert for auditing instance
  • Disk space alert for central repository host
  • Auditing filter change monitor
  • Automatic database auditing monitor
  • Central repository database size alert
  • Central repository database tampering alert
  • Network connectivity monitor
  • SQL Server connectivity monitor

The severity and other default settings can be changed, which will be explained next.

Common settings

The settings for every system alert can be changed based on SQL audit requirements through a wizard type dialogue. To open the wizard, click on the item representing a system alert and click Edit:

System alert editing

There are several common wizard steps for every alert, and they will be explained separately in this chapter.

Alert message

This is where the message based on alert can be customized to provide the desired information. The message will be shown in the History tab of the Alerting section in the Home menu when the alert is triggered and can be displayed per audited SQL Server that alert refers to:

Alerts are already prepopulated with messages, but, based on requirements, more comprehensive information can be constructed using built-in, so-called, “environment variables” that will represent the value of a system element that is experiencing the issue (like the Instance name, the Unprocessed packages name…), or a system element providing general information about alert settings (like the Name of the triggered alert, Creation date…); basically, elements that can simplify the construction of the alert message and provide easy identification of the issue:

In this wizard step, the severity level can also be changed, which can influence how the issue should be perceived and filtered during a review:

Alert severity

The text will be used for the email body when the email notification option is used.

Email notification

Every alert type has the option to set email notifications. By default, email notifications are disabled, enabling the option will provide a list of email profiles that define the list of recipient email addresses that will receive notification:

Email notification list

Email profiles can be configured in the Email recipient list tab of the Options window:

Email recipient list profiles

By default, the Write this alert report to Windows event log option is turned on:

Write alerts to Windows log

This can be used to store alerts on alternate location and possibly create an automated response and issue resolution like the one explained in the article: Managing low storage space using SQL audit tool features and automation

The final common wizard step for system alerts would be the summary, which allows a review of the alert configuration before applying it. The alert name can be changed here:

Alert configuration summary

Along with these common wizard steps, every alert has some additional steps based on alert type to reconfigure the alert behavior, i.e., change the alert triggering threshold.

System alerts

Central repository database inactivity alert

This alert type will be triggered if no SQL audited events are being written in the repository for some period. This can be the indicator of several issues: something is wrong with the central repository database, SQL Server or machine that is hosting it are having network connectivity issues (audited SQL Servers are not connected anymore), or simply, SQL auditing filters are not applied properly, and the application is monitoring some rare, insignificant events. Since SQL activity may vary from one environment to another, customizing the period is available as the step in this alert’s wizard configuration. Options include the number of time units and the type of time units:

Central repository database inactivity period

The alert message will provide information on when the last event was recorded and how many unprocessed and processed packages are still in the queue.

Disk space alert for auditing instance

When a SQL Server is added for auditing, the auditing agent configuration will offer to designate a storage location for temporary files:

SQL auditing agent configuration

Those files include both unprocessed, raw data session files, and processed data package files. If the storage drive, where temporary files are located, was filled with data, it can interrupt further auditing as there will no longer be enough space to process audited data. First of the configuration steps specific for this alert type is the page with the audited SQL Server instance for which the monitoring should apply to:

SQL Server list with SQL audit applied

The alert has the option to set the disk size threshold limit before triggering and has the option to stop auditing at that moment:

Minimal remaining disk size before alert is triggered

The Disable auditing on this instance if this alert triggered option will prevent “suffocating” the drive with additional temporary files and filling up the audited SQL Server session buffer with event trails that cannot be audited until the storage space issue is resolved:

Disable SQL audits on instance with low disk space

Disk space alert for central repository host

Analog to previous, this alert type activates if a low storage space event occurs on the disk drive where the central repository database is located:

Minimal remaining disk size for central instance before alert is triggered

Besides setting the storage size threshold for the alert trigger, it is possible to use the Disable loading of packages into central repository database if this alert is triggered option to prevent the loading of processed data packages into the repository until the issue is resolved. This will conserve auditing data in the audited server temporary storage and prevent loss due to the inability to load data into the repository.

Both disk space type alerts will provide information about the drive letter and available space left on that drive.

Auditing filter change monitor

This is a simple alert that provides notification if the auditing configuration against a chosen SQL Server has been changed. The configuration wizard allows selection of SQL Server to be monitored from the list of audited ones:

List of SQL Servers to be monitored for SQL audit configuration change

This alert can be utilized to detect if an unauthorized configuration change is applied, which user applied the change, and what was the change:

Automatic database auditing monitor

Since ApexSQL Audit has the option to automatically add newly created databases for auditing, there is the system alert that provides notification on such an event. The configuration wizard step, specific for this alert type, has the option to select a SQL Server from the list of audited servers to be monitored for automatic database auditing events:

List of SQL Servers to be monitored for automatic SQL auditing

The alert can provide information on which SQL Server instance experienced the event and the name of the database added for SQL audit.

Central repository database size alert

Central repository database size is an important aspect of the SQL audit mechanism. An overinflated central repository database can be part of a bigger storage space issue, negatively influence reporting performance, or be the indicator of a large influx of audited data is being received. This is where this kind of alert can become helpful.

The alert’s configuration contains two steps. The first step allows setting the central repository database size limit before the alert gets triggered. It is possible to use the Disable loading of packages into central repository database option, which will keep data packages in queue until the database size limit is resolved. The option is disabled by default as there are no immediate negative consequences in data load continuation except for further database size increase:

Central repository database size

The second step is to set the start time and interval to check the repository database size. The interval should not be too short (e.g., 1 second) as it will overload the host SQL Server with frequent queries, and should not be too long (e.g., 10 days) as it might skip critical moment where reaction would be needed:

Time interval for central repository database size check

Useful information that can be relayed through the automated message can include current and maximum database size.

This alert can be resolved by manual archiving explained in the Archiving the Central repository database – ApexSQL Audit article, or scheduled archiving, explained in the following article: Schedule archiving in ApexSQL Audit

Central repository database tampering alert

The alert’s function is to warn if central repository database integrity has been compromised. This can be the consequence of tampering around the repository database, for example, a deletion attempt of stored events. The configuration step specific for this alert allows us to set the checkup interval. The interval should not be too short as the integrity checks can be performance-intensive:

Time interval for central repository database tampering check

To verify the alert notification and manually check the integrity of the central repository database, follow instructions from the article: Verifying SQL audited data integrity of central repository database

Network connectivity monitor

Since SQL audit mechanisms depend on the stable network connection between the central instance and audited SQL Servers, it would be recommended to utilize this alert. The central instance service will check for a response from remote audited instances and register if there has been silence in communication. The alert configuration allows us to set how much time without communication is needed before triggering the alert:

Time interval for network communication loss

And what is the interval between responsiveness checks? This interval should be lower than the communication breakdown interval:

Time interval for network communication check

The message with this alert can provide names of instances that have lost and/or regained connection.

SQL Server connectivity monitor

This system alert checks if the connection to an audited SQL Server can be established and is active. This can indicate a different scope of problems from the network connectivity. Such problems include situations like SQL Server is removed, or in some kind of maintenance state, or extremely high data traffic and machine resource consumed, so accepting communication from the central instance is impossible. There are three configuration steps for this alert. The first step is to select SQL Servers from the list of ones where SQL auditing is applied, central instance hosting repository is included:

SQL Server connectivity monitor

The following step sets the time limit before the alert is triggered when a SQL Server is unavailable:

Time interval for disconnected SQL Servers

The third step sets the start time and intervals between SQL Server availability checks:

Time interval for SQL Server connection check

The alert message will show which SQL Servers are unavailable and what was the period set for inactivity before notification.

A combination of these system alerts, if triggered simultaneously, can help better pinpoint the problem and resolve it to continue uninterrupted SQL audits.