SQL Server 2014 with PowerShell v5 Cookbook

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 ...