Reporting Services Querying with WQL

Welcome to Israel MCS blog , this is the first technical post and I belive you're going to see them a lot :)

Today I want to share a solution I had last week.

From time to time we need to "ask" our Operation system questions such as:

1. How are you today?

2. How was your night?

3. Do you need anything?

4. Did somebody do something to you? Who?

Well, the best way to do it is querying the system with WMI queries by WQL language.

Few days ago my customer needed to decide what the best way is.

So, how we can do it? , the following solutions are some examples of ways to do it:

1. Working with the magnificent task in SSIS "WMI Data Reader Task", you can run it against the system and load it to a DB destination and querying it with a Report tool.

2. Design a new .NET data extension for Reporting services and querying the system.

3. Using a CLR integration in SQL Server 2005/2008 and develop a stored procedure to integrate with WMI. You have to load the system.managment.dll to SQL server.

4. "Use it at home..." J , using SSIS as data source in Reporting Services.

And the list can grow....

My customer needed for a generic source without any Report tool dependency.

We decide to develop a .NET stored procedure with CLR integration which gives us a generic WMI source for any report tool that can querying SQL server.

The solution has two projects: (Download the WMIConnector Project)

1. .NET stored procedure with a dedicated class with WMI support.(Scripts under "Test Scripts" Directory).

2. Reporting Services Project with sample WMI report.

The report has three parameters:

1. Server name

2. WMI namespace

3. WMI query.

Installation:

1. Create a SQL Server database with a name: "WMIProxyDB"

2. Load the system.managment namespace to SQL server - Run the Step I.sql script in the Test Scripts directory.

3. Open and deploy the .Net stored procedure project. (WMIConnector.sln).

4. Run the Sample script against it. (Step II.sql).