Checking if the SQL Server Agent Service is Running with PBM and WQL

Lately I’ve studied a bit the power of WMI and WQL (Windows Management Instrumentation Query Language) and have become increasingly impressed with the flexibility of management we have in hand.

These days I was talking to our friend and MVP Jorge Segarra ( blog | twitter )  about these notifications using WQL and he said a feature of the PBM (and Jorge is a leading expert on PBM) that is possible using a WQL conditions in the  Policy. As a newbie I am slowly started to study this technology and still have plenty to learn, but it’s really fantastic.

Then he told me about a notification that he wanted to set up, which was to check if  the SQL Server Agent service was running and using PBM and WQL.

Firts we need to know what class to use. As  a service, the we use Win32_Service.

Lets play around Win32_Service and PowerShell to get some info. :

If we type :

   1: Get-WmiObject -Class win32_service 

will Display all the processes that make part of Win32_Service Class :

But I’m only interested in the SQLServerAgent, then if we type this command to display the Object type info that I can work :

   1: Get-WmiObject -Class win32_service | gm

Then there is a property Name and State  (both string), and we can query :

   1: Get-WmiObject  -query "SELECT *  FROM Win32_Service Where Name='SQLSERVERAGENT'"

And we can use the property State, that show the service state (State)  to know if the service is running or not .As I want to check by PBM if this service is running and in ExecuteWQL PBM Command only alows to you retrieve on information in Select, our final query will be :

   1: 'SELECT State FROM Win32_Service  WHERE Name ="SQLSERVERAGENT"'

Now The SQL Server : First we will create a policy  CheckSQLAgentStatePolicy and create a New ConDition CheckSQLAgentStateCondition  with facet Server :

Now we will click in the … button to show the advanced edit  tab :

and type our query  with ExecuteWQL function

ExecuteWql('String', 'root\CIMV2', 'SELECT State FROM Win32_Service  WHERE Name ="SQLSERVERAGENT"')

Remember to use double quotes (“) in the SQL Statement when you use the string condition. (Name = “SQLSERVERAGENT”)

from BOL  :

ExecuteWQL

Function signature: Variant ExecuteWQL (string returnType , string namespace, string wql)

Function description: Executes the WQL script against the namespace that is provided. Select statement can contain only a single return column. If more than one column is provided, error will be thrown.

Arguments and return type definition

• returnType – Specifies the return type of data that is returned by the WQL. The valid literals are Numeric, String, Bool, DateTime, Array, and Guid.

• namespace – Is the WMI Namespace to execute against.

• wql – Is the string that contains the WQL to be executed.

Example: ExecuteWQL(‘Numeric’, ‘root\CIMV2’, ‘select NumberOfProcessors from win32_ComputerSystem’) <> 0

Then click OK and back to condition window and select Operator = and value ‘Running’

 

Now It is just evaluate the Policy and :

and If I stop the Service :

This is cool.Can you see the things that  we can monitor using WQL and PBM. Can you imagine the possibilities?

Now we can Schedule, use the PBM Cmdlets in PowerShell, create a Central Management server… etc and Monitor when SQL Server Agent Stops

#PowerShellLifeStyle