ExecuteWQL() in Policy Based Management

I always look for ways to access OS data from within SQL Server. As a DBA, this helps you make better decisions. In the Policy Based Management(PBM) realm, imagine a policy being run against all SQL Servers that checks for ERROR events in the event logs for the past 24 hours. Your morning report could include any servers having issues. Another scenario might be a one time report that checks the firmware revision of your raid controller. A WQL query and a couple clicks then you have a list of boxes that do not have the latest firmware. When you are managing a lot of SQL boxes, this could be powerful and time saving. You could also combine with ExecuteSQL() for more complex scripts.

Here is what is in BOL on ExecuteWQL():

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

I don’t know why I need to expand on a topic where the example verifies that the local server has at least one processor. :)

Here is a quick example of one of the cool things you can do with PBM in SQL Server 2008. It is pretty simple. We get the freespace for each drive and we fail evaluation if the drive has less than 10000MB free. You don’t want this to be your complete monitoring system but it could be a nice supplement.

email it! |   |   |   |  | 

Permalink     1 Comments  

Rate this Post: 12345
COMMENTS:

I like it, could be very useful. In this specific example though, setting a fixed size across multiple servers is not very realistic... we have some servers where 10 GB is gobs of room, and others where we want a warning at 75 GB...

posted @ Tuesday, November 11, 2008 2:08 PM by Aaron Bertrand

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]