solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Using the SSIS WMI Task to Gather System Information

MSSQLTips author Brady Upton 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: 4/12/2013


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, April 12, 2013 - 12:07:21 PM - Manoj Read The Tip

Thank You.. Nice Article.


Friday, April 12, 2013 - 12:45:56 PM - dj Read The Tip

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 - 1:09:22 PM - Brady Read The Tip

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.


Saturday, April 13, 2013 - 10:27:27 AM - dinesh Read The Tip

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...

Tuesday, April 16, 2013 - 2:49:06 PM - Raj Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.