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 :

image

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

image

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'"

image

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 :

image

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

image

and type our query  with ExecuteWQL function

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

image

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’

image

 

Now It is just evaluate the Policy and :

image

image

and If I stop the Service :

image

image

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 Alegre

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

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

  1. mahmoud says:

    Hi, Thanks for the useful information.
    what if I want to know the sql server agent state on a remote server ???

    how can we alter this Statement

    Get-WmiObject -query “SELECT * FROM Win32_Service Where Name=’SQLSERVERAGENT'”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s