O'Reilly logo

SQL Server 2014 with PowerShell v5 Cookbook by Donabel Santos

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Setting up WMI server event alerts

In this recipe, we will set up a simple WMI server event alert for a DDL event.

Getting ready

We will set up an alert that creates a text file with timestamp every time there is a DDL login event (any of CREATE, ALTER, or DROP). We will utilize the WMI provider for server events in this exercise.

These are the values you will need to know:

Item

Value

Namespace (if using default instance)

root\Microsoft\SqlServer\ServerEvents\MSSQLServer

Namespace (if using named instance)

root\Microsoft\SqlServer\ServerEvents\InstanceName

WMI query

SELECT * FROM DDL_LOGIN_EVENTS

DDL_LOGIN_EVENTS Properties (partial list)

SQLInstance
LoginName
PostTime
SPID
ComputerName
LoginType

For WMI events hitting SQL Server, you ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required