Using the SSIS WMI Task to Gather System Information

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 post 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:

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.

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

SQL Freelancer SQL Server SSIS WMI

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

SQL Freelancer SQL Server SSIS WMISQL Freelancer SQL Server SSIS 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:

SQL Freelancer SQL Server SSIS WMI

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)

SQL Freelancer SQL Server SSIS WMI
Click here to view the rest of this post.

Building Key Performance Indicators (KPIs) with PowerPivot

A KPI (Key Performance Indicator) is a graphical representation that displays progress against a predefined measure or business goal. KPIs make it easier for end users to evaluate the amount of progress without reading a bunch of data.

In this post, I’ll use AdventureWorksDW2012 sample data so you can follow along with me. The database can be downloaded here.

Let’s get started.

Enabling PowerPivot in Excel 2013

To enable PowerPivot, open Excel, go to File, Options, Add-Ins, select COM Add-ins and click Go. This will open up the COM Add-Ins dialog box. Click “Microsoft Office PowerPivot for Excel 2013” and hit OK. After successfully enabling PowerPivot, the tab should appear at the top of the Excel spreadsheet:

SQL Freelancer SQL Server PowerPivot KPI

Importing Data

Open Excel, click the PowerPivot tab, Manage:

SQL Freelancer SQL Server PowerPivot KPI
Upon clicking Manage, a new window should appear. From this window, you will import data. Click From Database and select From SQL Server:

SQL Freelancer SQL Server PowerPivot KPI
Type in the Server Name, Authentication mode, and browse to the AdventureWorksDW2012 database:
SQL Freelancer SQL Server PowerPivot KPIClick Next, choose “Select from a list of tables and views to choose the data to import” and click Next. The next screen is where we will select our data to import. For this example, choose FactInternetSales and click “Select Related Tables”. The Select Related Tables button enables you to automatically select every table that is related to the source table selected:

SQL Freelancer SQL Server PowerPivot KPI
After clicking Finish, the import will begin. Once the import finishes successfully you should be able to view all the tables separated into sheets:

SQL Freelancer SQL Server PowerPivot KPI

Creating PivotTable

Before creating a KPI we will need to slice and dice our data into a PivotTable. To do this, click PivotTable on the ribbon bar and choose New Worksheet:

SQL Freelancer SQL Server PowerPivot KPI
Click here to view the rest of this post.