By: Brady Upton | Comments (5) | Related: 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.
From the SSIS toolbox, drag the WMI Data Reader Task into the Design view.
Right click in the Connection Manager window and select New Connection and select WMI. Click Add:
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:
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)
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)
Once we have our WMI Data Reader Task configured, right click and select Execute Task:
After executing successfully, 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 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
Right click on the Flat File Source and choose Edit. Select New and browse to the CSV file we created earlier:
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](/tipimages2/2932_Transform.png)
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:
Click Mappings. 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:
That's it! Execute the package. Once the package is finished you can query the database table to see the results:
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
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
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
