Skip to content

create-a-wmi-event-alert.md

Latest commit

 

History

History
101 lines (65 loc) · 4.89 KB

create-a-wmi-event-alert.md

File metadata and controls

101 lines (65 loc) · 4.89 KB
Raw
title Create a WMI Event Alert | Microsoft Docs
ms.custom
ms.date 03/06/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology ssms
ms.topic conceptual
helpviewer_keywords
WMI event alerts [SQL Server Management Studio]
ms.assetid b8c46db6-408b-484e-98f0-a8af3e7ec763
author stevestein
ms.author sstein
manager craigg

Create a WMI Event Alert

This topic describes how to a [!INCLUDEssNoVersion] Agent alert that is raised when a specific [!INCLUDEssNoVersion] event occurs that is monitored by the WMI Provider for Server Events in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

For information about the using the WMI Provider to monitor [!INCLUDEssNoVersion] events, see WMI Provider for Server Events Concepts. For information about the permissions necessary to receive WMI event alert notifications, see Select an Account for the SQL Server Agent Service. For more information about WQL, see Using WQL with the WMI Provider for Server Events.

In This Topic

Before You Begin

Limitations and Restrictions

  • [!INCLUDEssManStudioFull] provides an easy, graphical way to manage the entire alerting system and is the recommended way to configure an alert infrastructure.

  • Events generated with xp_logevent occur in the master database. Therefore, xp_logevent does not trigger an alert unless the @database_name for the alert is 'master' or NULL.

  • Only WMI namespaces on the computer that runs [!INCLUDEssNoVersion] Agent are supported.

Security

Permissions

By default, only members of the sysadmin fixed server role can execute sp_add_alert.

Using SQL Server Management Studio

To create a WMI event alert

  1. In Object Explorer, click the plus sign to expand the server where you want to create a WMI event alert.

  2. Click the plus sign to expand SQL Server Agent.

  3. Right-click Alerts and select New Alert.

  4. In the New Alert dialog box, in the Name box, enter a name for this alert.

  5. Check the Enable check box to enable the alert to run. By default, Enable is checked.

  6. In the Type list, select WMI event alert.

  7. Under WMI event alert definition, in the Namespace box, specify the WMI namespace for the WMI Query Language (WQL) statement that identifies which WMI event will trigger this alert.

  8. In the Query box, specify the WQL statement that identifies the event that this alert responds to.

  9. Click OK.

Using Transact-SQL

To create a WMI event alert

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    -- creates a WMI event alert that retrieves all event properties for any ALTER_TABLE event that occurs on table AdventureWorks2012.Sales.SalesOrderDetail  
-- This example assumes that the message 54001 already exists.  
USE msdb ;  
GO  

EXEC dbo.sp_add_alert  
    @name = N'Test Alert 2',  
    @message_id = 54001  
    @notification_message = N'Error 54001 has occurred on the Sales.SalesOrderDetail table on the AdventureWorks2012 database. Please see the following information...',  
    @wmi_namespace = '\\.\root\Microsoft\SqlServer\ServerEvents\,  
    @wmi_query = N'SELECT * FROM ALTER_TABLE   
WHERE DatabaseName = 'AdventureWorks2012' AND SchemaName = 'Sales'   
    AND ObjectType='Table' AND ObjectName = 'SalesOrderDetail'';  
GO

For more information, see sp_add_alert (Transact-SQL).