Documentation forSQL Sentry

SQL Sentry Event Sources

Introduction

An event source represents a unique instance of an event provider operating over a single event store connection. In SQL Sentry's Navigator pane, every sub-node underneath a SQL Server node represents a unique event source.

The following is a listing of all event sources currently supported by SQL Sentry, along with their associated event objects, providers, and instances:

Item  Navigator Node Event Provider Instance Type
SQL Server Agent Jobs Jobs SQL Server Agent Job
  • SQL Server
  • AWS RDS
SQL Server Agent Alerts Alerts SQL Server Agent Alert SQL Server
SQL Server Agent Log SQL Server Agent Log SQL Server Agent SQL Server
Maintenance Plans Maintenance Plans Maintenance Plan SQL Server
Reporting Services Reports Reporting Services Reporting Services SQL Server
Windows Tasks  Tasks Window Task Scheduler Windows Instance
Windows Event Logs Windows Event Logs Windows Event Log Windows Instance

SQL Server Agent Jobs

SQL Server agent jobs are listed for each SQL Server instance under the Jobs node. Expand the node to view the list of jobs, or select the node to view a calendar for all jobs. The SQL Server agent job event provider is used behind the scenes to access job metadata, history, and active status information.

SQL Sentry Navigator Pane with the Jobs node selected. The Event Calendar is opened to the Jobs view for the selection.

Using the SQL Sentry client, perform all the same tasks related to jobs that you'd previously do with SSMS, plus more features such as visual scheduling, performance monitoring, reporting, queuing, and chaining.

SQL Server Agent Alerts

Navigator Pane with the SQL Server Agent Alerts folder highlighted.

SQL Server agent alerts are listed for each SQL Server instance under the Alerts node. Expand the node to view the list of alerts, or select the node to view a calendar for all alerts. The SQL Server Agent alert event provider is used to access alert metadata and history information.

SQL Sentry supports SQL Server Agent event alerts, but SQL Sentry can be configured to alert you on SQL Server agent WMI event alerts. Additionally, SQL Sentry can alert you about general performance conditions.

Note:  To enable the server alert collection and notification without the use of agents on each SQL Server, SQL Sentry must install the SQL Sentry alert trap and one small table msdb.dbo.SQL_Sentry_AlertLog_20 on each watched server, and store the procedure msdb.dbo.spTrapAlert_20.

SQL Sentry must also make a minor configuration change to each server alert to enable alert collection and notification; the Execute job setting updates to use the SQL Sentry Alert Trap job. If an alert is already set to execute another job, the Execute job settings for that alert doesn't update, and SQL Sentry is unable to generate notifications for the alert.

One of the most common reasons the Execute job setting may already be set to a job is for logging and/or notification purposes, in which case SQL Sentry may be able to safely assume control of this function.  Some of the advantages instead of using SQL Sentry are that its logging and notification processes are centralized versus distributed on each server. They aren't MAPI-dependent, and they can easily be made redundant by using more than one SQL Sentry monitoring service.

For a complete list of objects SQL Sentry places on a watched server, see the Watched Server Objects topic. 

Note:  To watch alerts on SQL Server 2005 and above instances, token replacement must be enabled for the SQL Server agent. This is disabled by default as a security precaution. Configure SQL Sentry to auto-enable SQL agent tokens at the global and instance level.
Additional Information: For more information about the token replacement setting for the SQL Server agent, see the SQL Server Books Online

Reporting Services Reports

If a reporting services database exists on a SQL Server, the Reporting Services node displays under the SQL Server agent node, under which all reports are listed by name. Expand the node to view the list of reports, or select the node to view a calendar for all reports. The reporting services event provider is used to access report metadata and history information.

For scheduled reports, Reporting Services jobs are listed under the Jobs node using a combination of the report name and schedule type. Not all reports have schedules, and some reports have multiple schedules, so the associated object nodes shown under Jobs and Reporting Services nodes may not match exactly. Shared report schedules are listed under the Jobs node.

Reports are displayed on the Calendar pane by a friendly name and offer many details through their pop-up window.

SQL Sentry Event Calendar displaying an event pop-up window for a SQL Server Agent Job.

SQL Server Agent Log

Navigator Pane with the SQL Server Agent folder expanded and the SQL Server Agent Log node highlighted.

The SQL Server Agent Log node is listed under each SQL Server Instance node. Select the node to view a calendar for all SQL Server agent events. The SQL Server agent event provider accesses historical information through the event calendar.

Windows Tasks

Navigator Pane with the Event Calendar node expanded, and the Tasks node highlighted.

SQL Sentry supports the task scheduler event source for Windows targets. Windows tasks are listed for each Windows instance under the Tasks node. Expand the node to view the list of tasks, or select the node to view a calendar for all tasks. The Windows Task Scheduler event provider is used behind the scenes to access task metadata, history, and active status information.

Important:  Windows Vista introduced Task Scheduler 2.0. Task Scheduler 2.0 is backwards compatible with Task Scheduler 1.0; however, Task Scheduler 1.0 is not forwards compatible with Task Scheduler 2.0. For this reason, to watch or synchronize Task Scheduler 2.0 connections, you need a SQL Sentry monitoring service and SQL Sentry client running Windows Vista or higher. Windows 8 and Windows 2012 also introduced changes to the Task Scheduler. To watch or synchronize Windows 8 and Windows Server 2012 instances, you need a SQL Sentry monitoring service and SQL Sentry client running Windows 8 or Windows 2012.

Some task scheduler tasks don't return zero for success. In these cases, SQL Sentry may misinterpret this non-zero exit code as a task failure. To prevent these false negatives, specify an exact text string that SQL Sentry recognizes as the success code for that particular task. Select on the task in the Navigator pane, and then enter the value next to Exit code if task was successful.

Specifying Non-zero Success Codes for Windows Tasks

Note:  Occasionally, Windows tasks may show as running even after they have completed. For this reason, while a task is running, the Close Running Event context menu item is available to manually close this event. This menu item should only be used when you're sure the task has completed, but shows as running in SQL Sentry.

Windows Event Logs

SQL Sentry includes the Windows event logs event source for Windows targets. The monitoring of the Application, Security, and System event logs is supported. When you monitor a Windows instance the Application and System logs are initially watched. If you'd like to monitor the Security log, select Watch Windows Event Log from the Security node's context menu.

The Windows event logs source has a default HistoryFilter in place. The default filter is tailored to capture information about both SQL Sentry and SQL Server. With the History Filter, define rules that control exactly what types of events the monitoring service writes into event history concerning the Windows event logs. If an event doesn't meet the criteria you've defined in the History Filter, information about that event isn't written to your SQL Sentry database. Change the filter to best fit your environment.

The Windows event logs History Filter can be accessed in the Settings pane as follows:

  1. Select the Windows Instance node in the Navigator pane.
  2. Open the Settings pane (View > Settings).
  3. Select Windows Event Logs Source from the drop-down menu.
    SQL Sentry Settings pane for a Windows Computer that's displaying Window Event Logs Source settings.
Note:  Monitoring the Windows event logs is supported for Windows Vista or higher.