Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Using the SSIS WMI Task to Gather System Information


By:   |   Read Comments (5)   |   Related Tips: More > Integration Services Control Flow Transformations

Problem

When I need to find server information such as OS info, disk usage, memory allocation, etc. from SQL Server I can usually query DMV's or system tables, however, what if I need to find server information where SQL Server is not installed and querying tables are not available?

Solution

Finding server information is vital when monitoring servers and is usually pretty easy to find on SQL Servers, however when monitoring web or app servers this may be a bit of a challenge when SQL querying is not at your disposal. This tip will talk about the SSIS task "WMI Data Reader" and how it can help you monitor your non-SQL Servers.

First, a short introduction to WMI....

WMI (Windows Management Instrumentation) is used for accessing management information in an enterprise environment. The ability to obtain management data from remote computers is what makes WMI useful. It provides a consistent approach to carrying out day-to-day management tasks with programming or scripting languages - MSDN

Some things you can do with WMI may include:

  • Rebooting a computer remotely
  • Get a list of applications installed on a local or remote computer
  • Find OS info such as the version of Windows and service pack levels
  • Query the event logs on a local or remote computer
  • Find system information such as Manufacturer and Model of a computer

As you can see, WMI is a very powerful tool and can be used locally or remotely.

There are tons of different metrics you can capture using WMI scripts, but for this example I'll capture disk space information using the Win32_LogicalDisk class and insert it into a database table using a SSIS package.

First, let's create our database table that will store our disk information:

CREATE TABLE DiskInfo
(
Drive varchar(4),
DriveSize numeric(18,2),
FreeSpace numeric(18,2),
[Date] Datetime Not NUll Default GetDate()
)

Once our table is created, we can create the SSIS package. Open BIDS or SSDT and create a new Integration Services project.

create the SSIS package

From the SSIS toolbox, drag the WMI Data Reader Task into the Design view.

From the SSIS toolbox...

Right click in the Connection Manager window and select New Connection and select WMI. Click Add:

click in the Connection Manager

select New Connection and select WMI

This will open the WMI Connection Manager Editor. Give the connection manager a name and description. If you are creating the package for a local computer then use \\localhost to connect. If you are creating the package for a remote server then use \\RemoteServer to connect. For this example, I'm connecting locally using Windows Authentication:

open the WMI Connection Manager Editor

Next we'll need to right click on the WMI Data Reader Task and choose edit. Click the WMI Options tab. This tab is where we will need to input information such as the WMI connection string and the WMI query. For this example, I have used the following:

  • WMI connection - This is the connection we created in the previous step.
  • WqlQuerySourceType - Direct input
  • WqlQuerySource - Select Name, Size, FreeSpace From Win32_LogicalDisk Where DriveType = 3
  • OutputType - Data table
  • OverwriteDestination - Overwrite destination
  • DestinationType - File connection
  • Destination - New destination (see below)
Click the WMI Options tab

You will need to create a new File Connection for Destination. Click the ellipsis (...) for Destination and choose new connection.

Usage type - Create File - Path to file (for this example, I'm using C:\DriveInfo.csv)

You will need to create a new File Connection for Destination

Once we have our WMI Data Reader Task configured, right click and select Execute Task:

we have our WMI Data Reader Task configured

After executing successfully, you should see the CSV file created with disk space:

you should see the CSV file created with disk space

Now that we have successfully created the CSV file with data, we need to put this into our database table we created.

In BIDS or SSDT, drag the Data Flow Task into design view, right click and select Edit:

In BIDS or SSDT, drag the Data Flow Task into design view

In Data Flow view, we need to drag the following three tasks in from the SSIS toolbox and connect using precedence constraints.

  • Flat File Source
  • Data Conversion
  • OLEDB Destination
drag the following three tasks in from the SSIS toolbox and connect using precedence constraints

Right click on the Flat File Source and choose Edit. Select New and browse to the CSV file we created earlier:

Right click on the Flat File Source

Click on the Columns tab to ensure you can see the data from the CSV:

Click on the Columns tab to ensure you can see the data from the CSV

Right click on the Data Conversion task and choose edit. Select Free Space and Size and change Data Type to numeric[DT_numberic] and click OK:

Select Free Space and Size and change Data Type to numeric[DT_numberic] and click OK

Right click on the OLE DB Destination and choose Edit. Select New for the OLE DB connection manager and create a connection string to the database and table you created at the beginning of the tip:

Right click on the OLE DB Destination and choose Edit

Click Mappings. Map input columns with available destination as shown below and click OK:

Map input columns with available destination as shown below and click OK

Go back to Control Flow and connect the WMI Data Reader task to the Data Flow task using a precedence constraint:

Go back to Control Flow and connect the WMI Data Reader task to the Data Flow task using a precedence constraint

That's it! Execute the package. Once the package is finished you can query the database table to see the results:

Execute the package

To convert the disk space to GB use the following query:

SELECT Drive,
 Cast(DriveSize/1024/1024/1024 as Numeric(10,2)) as [DriveSize(GB)],
 Cast(FreeSpace/1024/1024/1024 as Numeric(10,2)) as [FreeSpace(GB)],
 [Date]
FROM DiskInfo

To convert the disk space to GB use the following query

As you can see from the example above, WMI can be a very powerful tool for DBA's as well as other administrators. This was just one simple example. There are hundreds of different WMI calls that you can perform against a server.

Next Steps
  • Learn more about WMI queries from MSDN
  • To see more tips on SSIS click here


Last Update:





About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, April 16, 2013 - 2:49:06 PM - Raj Back To Top

--This was a very Informative Post..!! Helps users explore various System Maintaince tasks that can be done with SSIS..!!


Saturday, April 13, 2013 - 10:27:27 AM - dinesh Back To Top

Hi ,

I am using for mssql 2008 .But showing error."[WMI Event Watcher Task] Error: An error occurred with the following error message: "Invalid parameter ".

Plz let me know solution...

Friday, April 12, 2013 - 1:09:22 PM - Brady Back To Top

Thanks Manoj & DJ.

DJ, I don't see any reason why you couldn't send the results directly into a SQL table and bypass Excel. This should work as well.


Friday, April 12, 2013 - 12:45:56 PM - dj Back To Top

Nice article.  WMI is a tool with great potential.

I see you're using SQL 2012.  Isn't it possible in 2012 to send the results directly to a table?  In PowerShell, this is almost trivial to do.

Thanks for sharing with the community.

dj


Friday, April 12, 2013 - 12:07:21 PM - Manoj Back To Top

Thank You.. Nice Article.


Learn more about SQL Server tools