Event Handling with Windows PowerShell 2.0

Unlike Windows PowerShell 1.0, for which you need to use .NET classes, such as the System.Management.ManagementEventWatcher class to subscribe and monitor events, Windows PowerShell 2.0 CTP3 provides a full-blown eventing infrastructure for event queries. The following list describes someeventing cmdlets:
☞Register-WmiEvent: Create an event subscription of WMI events on the local or a remote computer
☞Get-Event: Receive subscribed WMI events in the event queue in the current Windows Power-Shell session.
☞Remove-Event: Remove subscribed WMI events in the event queue in the current Windows PowerShell session.
☞Unregister-Event: Cancel an event subscription.
☞Get-EventSubscriber: Get the event subscribers in the current Windows PowerShell session.

 

To register an event subscription associated with the WQL query and the namespace:

$eventQuery = "SELECT * FROM DDL_DATABASE_LEVEL_EVENTS WITHIN 10 WHERE DatabaseName='AdventureWorks2008'"

$namespace = "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"    #\\RemoteHostName\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER\InstanceName

Register-WmiEvent -Namespace $namespace -Query $eventQuery -SourceIdentifier "sqlevents"

 

To check whether new events have arrived in the event queue:

while ($true)
{
 # Get new events
 $objEvents=Get-Event –SourceIdentifier "sqlevents" -ErrorAction SilentlyContinue
 # If new events arrive, then retrieve the event information.
 if ($objEvents)
 {
  # Loop through the collection of new events
  for ($i=0; $i -lt $objEvents.Count; $i++)
  {
   $objEvents[$i].SourceEventArgs.NewEvent | Select-Object $properties
   # Remove the event after its information has been processed.
   Remove-Event -EventIdentifier $objEvents[$i].EventIdentifier -ErrorAction SilentlyContinue
  }
 }
}

 

In the preceding code, the events are monitored in an infinite loop. Some kind of stop mechanism should be introduced to break out of the loop without aborting brutally by using Ctrl+C. The following code enables the loop to be broken by pressing the Esc key:

$ESCkey = 27 # 27 is the key number for the Esc button.
# Check if the Esc key is pressed
if ($host.ui.RawUi.KeyAvailable) {
$key = $host.ui.RawUI.ReadKey("NoEcho,IncludeKeyUp")
# If the Esc key is pressed, unregister the event subscription, break the loop,and exit this function.
if ($key.VirtualKeyCode -eq $ESCkey) {
Unregister-Event "sqlevents"
break
}
}

 

To monitor different events,the complete function Get-WMIEvent.ps1 is shown here:

function Get-WMIEvent([string] $eventQuery, [string] $namespace, [string[]]$properties){$ESCkey = 27 # 27 is the key number for the Esc button.# If an event subscription called "sqlevents" already exists, unregister it first.if (Get-EventSubscriber 'sqlevents' -ErrorAction SilentlyContinue) {Unregister-Event "sqlevents"}# Create an event subscription called "sqlevents" that registers to the eventsspecified by the $eventQuery under the $namespace.Register-WmiEvent -Namespace $namespace -Query $eventQuery -SourceIdentifier"sqlevents"while ($true) {# Get new events$objEvents=Get-Event –SourceIdentifier "sqlevents" -ErrorActionSilentlyContinue# If new events arrive, then retrieve the event information.if ($objEvents) {# Loop through the collection of new eventsfor ($i=0; $i -lt $objEvents.Count; $i++) {$objEvents[$i].SourceEventArgs.NewEvent | Select-Object$properties# Remove the event after its information has been processed.Remove-Event -EventIdentifier $objEvents[$i].EventIdentifier -ErrorAction SilentlyContinue}}# Check if the Esc key is pressedif ($host.ui.RawUi.KeyAvailable) {$key = $host.ui.RawUI.ReadKey("NoEcho,IncludeKeyUp")# If the Esc key is pressed, unregister the event subscription, break theloop, and exit this function.if ($key.VirtualKeyCode -eq $ESCkey) {Unregister-Event "sqlevents"break}}}}

 

To reuse a function, you can put it in the user-specific, shell-specific profile: %UserProfile%\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1 or
%UserProfile%\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1 on Windows Vista. Alternately, you can put the function in a library file and source in the file each time prior to executing a script. In this case, the library file is called dbaLib.ps1:

############################################################################### Source this library by running ". C:\DBAScripts\dbaLib.ps1"##################################################################################################################################################################### Add function to capture SQL Server events and allow to stop monitoring with Esc key######################################################################################function Get-WMIEvent([string] $eventQuery, [string] $namespace, [string[]] $properties){$ESCkey = 27 # 27 is the key number for the Esc button. # If an event subscription called "sqlevents" already exists, unregister it first.if (Get-EventSubscriber 'sqlevents' -ErrorAction SilentlyContinue) {        Unregister-Event "sqlevents"}# Create an event subscription called "sqlevents" that registers to the events specified by the $eventQuery under the $namespace.Register-WmiEvent -Namespace $namespace -Query $eventQuery -SourceIdentifier "sqlevents"  while ($true) {        # Get new events        $objEvents=Get-Event -SourceIdentifier "sqlevents" -ErrorAction SilentlyContinue        # If new events arrive, then retrieve the event information.        if ($objEvents) {                # Loop through the collection of new events                for ($i=0; $i -lt $objEvents.Count; $i++) {                        $objEvents[$i].SourceEventArgs.NewEvent | Select-Object $properties                                                # Remove the event after its information has been processed.                        Remove-Event -EventIdentifier $objEvents[$i].EventIdentifier -ErrorAction SilentlyContinue}        }         # Check if the Esc key is pressed        if ($host.ui.RawUi.KeyAvailable) {                 $key = $host.ui.RawUI.ReadKey("NoEcho,IncludeKeyUp")                # If the Esc key is pressed, unregister the event subscription, break the loop, and exit this function.                if ($key.VirtualKeyCode -eq $ESCkey) {                         Unregister-Event "sqlevents"                        break                }        }}}############################################################################## Add function to capture SQL Server events and send out alert e-mails#############################################################################function Notify-WMIEvent([string] $eventQuery, [string] $namespace, [string[]] $properties) {# Initialize alert message variables[String] $alertSubject="SQL Server Error at " + (Get-Date).ToString('yyyy-MM-dd hh:mm')[String] $alertMessage=""# If an event subscription called "sqlevents" already exists, unregister it first.if (Get-EventSubscriber 'sqlevents' -ErrorAction SilentlyContinue) {        Unregister-Event "sqlevents"}# Create an event subscription called "sqlevents" that registers to the events specified by the $eventQuery under the $namespace.Register-WmiEvent -Namespace $namespace -Query $eventQuery -SourceIdentifier "sqlevents"while ($true) {        # Get new events        $objEvents=Get-Event -SourceIdentifier "sqlevents" -ErrorAction SilentlyContinue# If new events arrive, then retrieve the event information.        if ($objEvents) {                # Loop through the collection of new events                for ($i=0; $i -lt $objEvents.Count; $i++) {# Construct the alert message from the error event information.$alertMessage = $objEvents[$i].SourceEventArgs.NewEvent | Format-List $properties | Out-String                # Send an alert e-mail.                Send-Email $smtpServer $fromAddress $toAddress $alertSubject $alertMessage $smtpUserName $smtpPassword                                                # Remove the event after its information has been processed.                        Remove-Event -EventIdentifier $objEvents[$i].EventIdentifier -ErrorAction SilentlyContinue}        } }}############################################################################## Add function to send e-mail#############################################################################Function Send-Email([String] $smtpServer, [String] $from, [String] $to, [String] $subject, [String] $body, [String] $userName, [String] $password){if ($userName.Length > 0 ) {$credential=New-Object System.Net.NetworkCredential -argumentList $userName, $passwordSend-MailMessage -From $from -To $to -Subject $subject -Body $body -SmtpServer $smtpServer -Credential $credential}else { Send-MailMessage -From $from -To $to -Subject $subject -Body $body -SmtpServer $smtpServer}}############################################################################## Ping a host to see if it is reachable.#############################################################################Function Ping-Host  ([string] $hostname ){[String] $alertSubject=""[String] $alertMessage=""$status=Get-WmiObject Win32_PingStatus -Filter "Address='$hostname'" | Select-Object statuscodeif($status.statuscode -eq 0){Write-Host "$hostname is reachable." -background "GREEN" -foreground "BLACK"}else{Write-Host "$hostname is NOT reachable." -background "RED" -foreground "BLACK"# An alert e-mail is sent if the host cannot be pinged.Write-Host "Sending an e-mail regarding $hostname ..." $alertSubject="Ping Status"$alertMessage="$hostName is not reachable. Please check."Send-Email $smtpServer $fromAddress $toAddress $alertSubject $alertMessage $smtpUserName $smtpPassword}}############################################################################## Check SQL Server related services#############################################################################Function Check-Services([String] $hostName){[String] $alertSubject=""[String] $alertMessage=""# Get SQL Server related services on the host$services=Get-WmiObject -class Win32_Service -computername $hostName | Where-Object {$_.name -like '*SQL*'} foreach ( $service in $services){# If a service that is set to start automatically is not running, then write a red error message and send an alert e-mail.        if ($service.State -ne "Running" -and $service.StartMode -eq "Auto")        {$alertSubject="Service Exception"        $alertMessage="On " + $hostName + ", the service " + $service.Name + " is set to AutoStart, but it is " + $Service.State + ". Please check."         Write-Host $alertMessage -background "RED" -foreground "BLACK"Write-Host "Sending an e-mail regarding" $service.Name "on" $hostName "..." Send-Email $smtpServer $fromAddress $toAddress $alertSubject $alertMessage $smtpUserName $smtpPassword        }# If the status of a service is not OK, then write a red error message and send an alert e-mail.elseif ($service.Status -ne "OK") {$alertSubject="Service Exception"$alertMessage="On " + $hostName + ", the status of the service " + $service.Name + " is " + $Service.State + ". Please check."          Write-Host $alertMessage -background "RED" -foreground "BLACK"Write-Host "Sending an e-mail regarding" $service.Name "on" $hostName "..." Send-Email $smtpServer $fromAddress $toAddress $alertSubject $alertMessage $smtpUserName $smtpPassword}# You can add more exceptions here by adding more elseif cases.}}################################################################################################ Check SQL Server services made available by the WMI Provider for Configuration Management.# Please note that this function can only work with SQL Server 2008 hosts# as the namespace root\Microsoft\SqlServer\ComputerManagement10 only applies to SQL Server 2008.###############################################################################################Function Check-SqlServices ([string] $hostName ){[String] $alertSubject=""[String] $alertMessage=""# Get SQL Server related services on the host$services=Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService -computername $hostName  foreach ( $service in $services){# If a service that is set to start automatically is not running, then write a red error message and send an alert e-mail.        if ($service.State -ne 4 -and $service.StartMode -eq 2)        {$alertSubject="Service Exception"        $alertMessage="On " + $hostName + ", the service " + $service.ServiceName + " is set to AutoStart, but its state is " + $Service.State + ". Please check."         Write-Host $alertMessage -background "RED" -foreground "BLACK"Write-Host "Sending an e-mail regarding" $service.ServiceName "on" $hostName "..." Send-Email $smtpServer $fromAddress $toAddress $alertSubject $alertMessage $smtpUserName $smtpPassword        }# You can add more exceptions here by adding more elseif cases.}}################################################################################################ Find the databases that have not been backed up in a certain number of minutes. ###############################################################################################Function Check-Backups ([String] $instanceName, [String] $backuptype, [Int32] $minutes){[String] $strResult=""$results=Invoke-Sqlcmd -Query "Exec dbo.uspMonitorBackups '$backuptype', $minutes" -ServerInstance $instanceName -Database "admin" if ($results) {for ($i=0; $i -lt $results.Count; $i++) {if ($results[$i].Databasename) {$strResult = $strResult + $results[$i].Databasename + "`n"}}$strResult="The following databases on $instanceName have not been backed up in $minutes minutes:`n" + $strResultWrite-Output $strResult}}################################################################################################################## Define the default SMTP server and e-mail group used by DBA # Please change the SMTP server, the sending and receiving e-mail addresses before you start running this script!#################################################################################################################$smtpServer="smtp.powerdomain.com"$fromAddress="[e-mail]"$toAddress="[e-mail]"$smtpUserName=""$smtpPassword=""############################################################################## Define inventory server and database#############################################################################[String] $inventoryServer="POWERPC,1433"[String] $inventoryDatabase="SQL_Inventory"############################################################################## Add SQL Server Powershell snap-ins if they are not added yet############################################################################## Check if SQL Server Powershell snap-ins have not been added to the current Windows PowerShell sessionif (-not (Get-PSSnapin "SqlServer*" -ea SilentlyContinue)) {        # If SQL Server Powershell snap-ins have not been added, then check if they are registered on the system.        $PSSnapIn=Get-PSSnapin SqlServer* -Registered        if ($PSSnapIn) {                #Add SQL Server 2008 PowerShell snap-ins                $PSSnapIn | foreach { Add-PSSnapin $_.Name }                                # Load Type Data and Format Data used by SQLPSif(Test-Path -Path "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Types.ps1xml" ) { Update-TypeData "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Types.ps1xml" } else { Write-Host "SQLProvider.Types.ps1xml not found in C:\Program Files\Microsoft SQL Server\100\Tools\Binn."Write-Host "Please find the file on your machine and update the path in dbaLib.ps1." } if(Test-Path -Path "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Format.ps1xml" ) { Update-FormatData "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Format.ps1xml"} else { Write-Host "SQLProvider.Format.ps1xml not found in C:\Program Files\Microsoft SQL Server\100\Tools\Binn."Write-Host "Please find the file on your machine and update the path in dbaLib.ps1." }         }        else {                Write-Output "No SQL Server Powershell snap-ins are registered!"        }}

In real practice, it is more common to save the output into a log file, or notify support personnel through e-mail or page based on the event received, or send an alert to an event management system such as Netcool. The following function is a more flexible version of the Get-WMIEvent function that accepts a script block:

 

function Get-WMIEvent([string] $eventQuery, [string] $namespace, [ScriptBlock] $sblock){$ESCkey = 27 # 27 is the key number for the Esc button.# If an event subscription called "sqlevents" already exists, unregister it first.if (Get-EventSubscriber ‘sqlevents’ -ErrorAction SilentlyContinue) {Unregister-Event "sqlevents"}# Create an event subscription called "sqlevents" that registers to the events specified by the $eventQuery under the $namespace.Register-WmiEvent -Namespace $namespace -Query $eventQuery -SourceIdentifier "sqlevents"while ($true) {# Get new events$objEvents=Get-Event –SourceIdentifier "sqlevents" -ErrorAction SilentlyContinue# If new events arrive, then retrieve the event information.if ($objEvents) {# Loop through the collection of new eventsfor ($i=0; $i -lt $objEvents.Count; $i++) {$objEvents[$i].SourceEventArgs.NewEvent | &$sblock# Remove the event after its information has been processed.Remove-Event -EventIdentifier $objEvents[$i].EventIdentifier -ErrorAction SilentlyContinue}}# Check if the Esc key is pressedif ($host.ui.RawUi.KeyAvailable) {$key = $host.ui.RawUI.ReadKey("NoEcho,IncludeKeyUp")# If the Esc key is pressed, unregister the event subscription, break theloop, and exit this function.if ($key.VirtualKeyCode -eq $ESCkey) {Unregister-Event "sqlevents"break}}}}

Note:You can utilize the script block to save the event information into a log file C:\sqlevents.log. The script block is as follows:
$sblock = { $input | Select-Object ObjectType, SPID, SQLInstance, TSQLCommand | Out-File "c:\sqlevents.log" }
The $input variable enumerates the event objects in the incoming pipeline.