Picture the scene: Your wife has gone to visit her mother, taking your car (let’s call it a Ferrari) and leaving you at home to watch the Lakers make a bid for yet another Championship victory. Midway through your wife’s journey, a tire blows out, so of course she’ll pick up her cell phone and notify you.
Now imagine if, when she called to you, you picked up the phone and immediately said: “Hi honey, I already got an email letting me know about the tire, and I’m on my way.” Not only would you impress her with your proactivity, speed and organization, you’d earn yourself a delicious home-made dinner and an all-round great weekend with your wonderful wife.
Of course, if you swap the Ferrari and wife for servers and users, then I believe that my little tale above describes, more or less, how I and most database administrators feel (or rather, would like to feel).
At the moment, the best system for notifying you that a problem has occurred is your users. But even with the sharpest, most eagle-eyed users, there may be a delay before they let you know about problems. How much better would it be for everyone if, when they got in touch, they immediately received this response:
“I’ve already been notified, and am solving the problem at this very moment.”
As for the blown-out tire, I don’t think there’s an API for that just yet (although, in a Ferrari, there should be). Thankfully, Windows does have a system we can use; one that will let you be notified if, for example, the SQL Server service stops: WMI Query Language.
In fact, you can use WMI Query Language to manage almost all the Windows features, including disk, AD, performance counters, cluster etc. using classes, properties and events. WMI Explorer is a very interesting tool that makes it much easier to find the properties and classes you need, but to know what they all do, I suggest you check out the WMI resources on MSDN.
A Quick Introduction to WMI Query Language
There is a set of systems management technologies, designed to unify the management of distributed computing environments. This set of technologies is called Web-Based Enterprise Management (WBEM), and Windows Management Instrumentation (WMI) is a Microsoft implementation of this. This means that we can use WMI to automate almost everything in Windows instances, both local and remote.
With the advent of this powerful tool, it was realized that administrators making use of it would need more efficient access to data, and so WQL was created to get the data faster.
“The WMI Query Language is a subset of the American National Standards Institute Structured Query Language (ANSI SQL)-with minor semantic changes. Similar to SQL, WQL has a set of keywords & operators and supports three types of queries.”
– Ravikanth Chaganti´s “WMI Query Language via PowerShell”
My good friend Ravi wrote a total of 57 pages explaining WQL in his eBook, so if you want to learn more about what you’re about to see, I recommend you download his work and take a look. As always, my goal is not to explain WMI and WQL, (and not just because Ravi did a splendid job of that already), but to show you a real-life implementation of this technology.
Some Examples
Less talk, more action! I’ve mentioned that WMI lets you access a wide range of functionality, but the best way to explain this is through a few simple demonstrations. To start with, let’s say you want to be notified if your local server’s processor’s Percentage Load exceeds 80%:
|
1 2 3 4 5 |
#Processor Load >= 80% $query = "Select * FROM __InstanceModificationEvent WITHIN 1 WHERE TargetInstance ISA 'Win32_Processor' and TargetInstance.LoadPercentage >= 80" #Register WMI event Register-WMIEvent -Query $query -Action { Write-Host "Processor utilization is more than 80%. Run Forrest Run"} |
To test, let’s run a DBCC CHECKDB to check if that notification really happens:
Figure 1 – Using WMI to keep track of processor load (click through for more detail).
Alternatively, for a more varied example of just how much control you have over the windows environment, say someone wants to write a word document – just run this code, and look at figure 2 to see the result:
|
1 2 3 4 |
#Word $query = "SELECT * FROM Win32_ProcessStartTrace WHERE ProcessName='WinWord.exe'" #Register WMI event Register-WMIEvent -Query $query -Action {Write-Host "Someone is writing a document"} |
Figure 2 – Generating a Word document using WMI (click through for more detail).
Finally, if you want to stop an events, you need to use Get-Job cmdlet in a pipeline:
|
1 |
get-job | Remove-Job -Force |
This command will remove all events that are registered, whether they’re running or not. However, if you want to be more fine-grained and just remove one event, type Get-Job, find the ID of the event in question, and then you can remove just that ID using get-job | remove-job -force.
What About SQL Server?
“This is all well and good,” I hear you say, “but does it really mean that if the SQL Server service stops, I can be notified?” A well-placed question, and to answer it, I say to you “Elementary, my dear Watson.” Let’s start with a simple example that you can use to show a message when sql server services stop :
|
1 2 3 4 |
#SQLServiceStop $query = "select * from __InstanceModificationEvent within 5 where TargetInstance ISA 'Win32_Service' and TargetInstance.Name='MSSQLSERVER' and TargetInstance.State='Stopped'" #Register WMI event Register-WMIEvent -Query $query -Action {Write-Host "Hey..hey..wakeup Man. Your SQL Server service stops"} |
Figure 3 – A very simple SQL Server service monitoring script in action (click through for more detail).
If you want the event action to be triggered more quickly after the conditions are met, just change the WITHIN component in the SELECT statement. The WITHIN keyword is a floating point number, used to specify the polling (or grouping) interval, specified as a number of seconds. So in this example, the event will be fired every 5 seconds. As I mentioned earlier, we also can use WMI Explorer (I like to call it Pandora’s Box) to track down these parameters. Again, rather than diving into detail here, I suggest you read Ravi´s eBook, where you will find plenty of information about “Pandora’s box”.
Time to Scale Out
Before we see how to notify ourselves (or anyone else) by email, lets scale out our code. After all, if I have X servers, with N instances, I want to be notified if any of them have a problem, and it would be a really painful process to treat each server and instance individually.
Instead, I’ve build a function that displays the status of the SQL Server service in each instance and server, including information on whether the service account is a member of Local Admin. Let’s take a look:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
function get-ServiceIsAdmin { [CmdletBinding()] Param ( [Parameter(position=0,Mandatory = $true,ValueFromPipeline = $true )][String] $ComputerName, [Parameter(position=1,Mandatory = $true)][String] $ServiceAccount ) process { $ServiceAccountSplit = $ServiceAccount -split '\\' if ($ServiceAccountSplit.count -eq 1) { $ServiceAccountToUse = $ServiceAccountSplit[0] } else { $ServiceAccountToUse = $ServiceAccountSplit[1] } #Original Script in #http://www.hanselman.com/blog/ #HowToDetermineIfAUserIsALocalAdministratorWithPowerShell.aspx $administratorsAccount = Get-WmiObject -ComputerName "$ComputerName" Win32_Group -filter "LocalAccount=True AND SID='S-1-5-32-544'" $administratorQuery = "GroupComponent = `"Win32_Group.Domain='" + $administratorsAccount.Domain + "',NAME='" + $administratorsAccount.Name + "'`"" $user = Get-WmiObject Win32_GroupUser -filter $administratorQuery | select PartComponent | where {$_ -match $ServiceAccountToUse} if ($user -eq $null ){ 'No' } else { 'Yes' } } } function Get-SQLServerServiceInfo { [CmdletBinding()] Param ( [Parameter(position=0,Mandatory = $true,ValueFromPipeline = $true )][String] $ComputerName, [Parameter(position=1,Mandatory = $true )][String] $SQLServerInstance = 'All', [ValidateSet("Engine", "Agent")] [Parameter(position=2,Mandatory = $true )][String] $ServiceType = 'Engine' ) begin { [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null Function Get-ServiceByInstance ($ServiceByInstance,$InstanceName,$ReturnName) { if ( -not $ReturnName) { [string] $ServiceAccount = ($ServiceByInstance | where {$_.Displayname -like "*$($InstanceName)*"} | select ServiceAccount) Write-Output ($ServiceAccount.substring(17,$ServiceAccount.length - 18)) } else { [string] $ServiceAccount = ($ServiceByInstance | where {$_.Displayname -like "*$($InstanceName)*"} | select Name) Write-Output ($ServiceAccount.substring(7,$ServiceAccount.length - 8)) } } } Process { try { $ManagedComputer = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') "$ComputerName" $ServiceTypeShow = if ($ServiceType -eq 'Agent') {'sqlagent'} else {'SqlServer'} if ($SQLServerInstance -eq 'All') { $ServiceByInstance =($ManagedComputer.Services | where { $_.type -eq "$($ServiceTypeShow)"} | select DisplayName,ServiceAccount,Name) $ManagedComputer.ServerInstances | select @{Expression={$($ComputerName) };Label = "ComputerName"} , @{Expression={$($_.NAME) };Label = "SQLEngineInstanceName"}, @{Expression={Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name) -ReturnName $False };Label = "ServiceAccount"}, @{Expression={get-ServiceIsAdmin -ComputerName $ComputerName -ServiceAccount (Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name)) };Label = "IsLocalAdmin"}, @{Expression={Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name) -ReturnName $True };Label = "ServiceName"} } else { $ServiceByInstance =($ManagedComputer.Services | where { $_.type -eq "$($ServiceTypeShow)" -and $_.displayname -like "*$SQLServerInstance*" } | select DisplayName,ServiceAccount,Name) $ManagedComputer.ServerInstances | where {$_.name -eq $SQLServerInstance } | select @{Expression={$($ComputerName) };Label = "ComputerName"}, @{Expression={$($_.NAME) };Label = "SQLEngineInstanceName"}, @{Expression={Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name) -ReturnName $false};Label = "ServiceAccount"}, @{Expression={get-ServiceIsAdmin -ComputerName $ComputerName -ServiceAccount (Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name)) };Label = "IsLocalAdmin"}, @{Expression={Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name) -ReturnName $True };Label = "ServiceName"} } } catch { Write-Output $Error[0] } } } |
Input Parameters:
- $ComputerName: Computer Name, string
- $SQLServerInstance: SQL Server Instance Name or ‘ALL’, string. Default: ‘ALL’
- $ServiceType: Which SQL Service to monitor: ‘Engine’ for SQL Server Engine and ‘Agent’ for SQL Server Agent services. Default: ‘Engine’
Output Values:
- ComputerName: Computer Name
- SQLEngineInstanceName: Engine Instance Name
- ServiceAccount: Service Account name
- IsLocalAdmin: ‘Yes’ or ‘No’
- ServiceName: Service Name
So let’s see how to use this…
Example 1: I want SQL Engine account information for all instances on my machine (local):
|
1 |
Get-SQLServerServiceInfo -SQLServerInstance 'ALL' |
Example 2: I want SQL Engine account information of instance INST1 on my machine (local):
|
1 |
Get-SQLServerServiceInfo -SQLServerInstance 'INST1' |
Example 3a: I want SQL Agent account information all instances on the machine Server2:
|
1 |
Get-SQLServerServiceInfo -Computername 'Server2' -SQLServerInstance 'All' - $ServiceType 'Agent' |
Example 3b: And scaling out, I want to get SQL Agent account information for all instances on multiple servers:
|
1 |
'Server1','Server2', 'Server3' | Get-SQLServerServiceInfo -SQLServerInstance 'All' |
Or:
|
1 |
Get-Content 'c:\temp\servers.txt | Get-SQLServerServiceInfo -SQLServerInstance 'All' |
…where C:\temp\servers.txt is a flat list of server names.
Adding Email Notification
Now that we have the ServiceNames (thanks to the function above), we can be notified if any of them have stopped.
As you probably know, there is a cmdlet in PowerShell 2.0 to send email (Send-MailMessage). I use Jakob Bindslet’s Send-SMTPmail (JB’s PowerShell), but this is simply a matter of personal taste. Either cmdlet is fine.
First, we have to download the Send-SMTPEmail cmdlet from Jakob Bindslet’s JB’s PowerShell Blog and put it into our User profile (In my case, the module is just Functions.psm1). If you use credentials, just make the necessary changes in the code (I included the relevant annotations to make it easy). Now that you’ve got that cmdlet installed, let’s see some examples of how to use it:
Email Notification for all SQL Server services on Server 1:
|
1 |
Get-SQLServerServiceInfo -Computername Server1 -SQLServerInstance 'All' -ServiceType 'Engine' | select computername,servicename | Send-EmailNotification |
Email Notification just for INST1 SQL Server services on Server 1:
|
1 |
Get-SQLServerServiceInfo -Computername Server1 -SQLServerInstance 'INST1' -ServiceType 'Engine' | select computername,servicename | Send-EmailNotification |
Email Notification for all SQL Server Agent services on Server 1:
|
1 |
Get-SQLServerServiceInfo -Computername Server1 -SQLServerInstance 'INST1' -ServiceType 'Agent' | select computername,servicename | Send-EmailNotification |
Email Notification for SQL Server services on multiple services, passing in server names by PipeLine:
|
1 |
'Server1','Server2','Server3' | Get-SQLServerServiceInfo -SQLServerInstance 'ALL' -ServiceType 'Engine' | select computername,servicename | Send-EmailNotification |
…Or, as before, using a flat text file to list servers:
|
1 |
Get-Content servers.txt | Get-SQLServerServiceInfo -SQLServerInstance 'ALL' -ServiceType 'Engine' | select computername,servicename | Send-EmailNotification |
Can you feel the Power?
Obviously, don’t forget to update the Send-EmailNotification function with your own details: SMTP server, “from” address, “to” address, and also change the “impo (import-module) functions” to your own module name:
|
1 |
Register-WMIEvent -ComputerName "$($ComputerName)" -Query "select * from __InstanceModificationEvent within 5 where TargetInstance ISA 'Win32_Service' and TargetInstance.Name=`'$($ServiceName)`' and TargetInstance.State='Stopped'" -Action { ipmo functions -Force ;Send-SMTPmail -smtpserver "yourSMTP" -from "YourFromEmail" -to "YourToEmail" -subject "Event Notification" -body "$($event.MessageData.ServiceName) Service Stops in $($event.MessageData.Computername)" } -MessageData $pso | Out-Null |
Unfortunately, this solution does currently have a small problem: the Events are not permanent. That is, if you restart the server, they will be dropped. In addition, (although it’s not so much a problem as a detail to bear in mind), you always need to have a PowerShell Window open for the solution to work.
Thankfully, these limitations are already being resolved; I’m in the process of finishing some tests with CodePlex PowerEvents, a module of functions developed by Trevor Sullivan (blog | Twitter) that allow us to work with permanent events. In other words, these limitations I’ve mentioned are merely temporary, and a blog post describing the finished solution will be coming soon!
Another thing that you have to bear in mind is security. You need to run PowerShell in elevated mode for this work, so in the words of the ever-wise @BuckWoody:
“@LaerteSQLDBA Right. But pay close attention to security. Starts to become an issue with WMI, and can be quite dangerous.”
So remember to always be safe, and I’ll see you soon.
Load comments