Hey, Scripting Guy! Good morning. I would like to use WMI to get diagnostic information for my server. I would like to be able to do this remotely and to write the results to an Excel spreadsheet. When I am done, I would like the spreadsheet to also create a chart of the results. Is this asking too much? I have seen pieces of all this on the Script Center, but I have not found exactly what I am looking for. Help!

– CC

Hi CC,

Guten morgen. Of course this can be done, but it will be a rather lengthy script. You do not believe me? Well, take a look at the GetWmiPerformanceDataCreateExcelChart.ps1 script, which follows this paragraph. Even though we do not have this exact script written in VBScript, there are a number of scripts that talk about creating charts and working with Excel in the Script Center. There are also almost 100 “Hey, Scripting Guy!” articles—nearly 4 months’ worth of articles—that specifically address VBScript and Excel. The concepts are the same. If you wish, you can migrate this script to VBScript. Use this reference for assistance if you need it. If you do decide to convert the script, why don’t you paste the script into the text of an e-mail message, send it to scripter@microsoft.com to share it with the Community-Submitted Scripts Center. If you need more information about performance monitoring with Windows PowerShell, refer to the scripts from earlier this week.

Here’s the GetWmiPerformanceDataCreateExcelChart.ps1 script:

Function TestSheetName($sheetName)
{
if($sheetName.length -ge 30)
 {
  $script:sheetName = $sheetName.substring(0,30)
 }
} #end TestSheetName
Function CreateEmptyArray($ubound)
{
 [int[]]$script:aryProp = [array]::CreateInstance("int",$ubound)
} #end CreateEmptyArray
Function GetWmiPerformanceData()
{
 For($i = 0 ; $i -le $reps -1 ; $i++)
  {
   $aryProp[$i] +=([wmi]"\\$computer\root\cimv2:$class.$key='$instance'").$Property
   Write-Progress -Activity "Obtaining Processor info" -Status "% complete: " `
   -PercentComplete $i
   Start-Sleep -Seconds $delay
  } #end for
}#end GetWmiPerformanceData
Function NewExcelSheet($sheetName)
{
 $excel = new-object -comobject excel.application
 $excel.visible = $true
 $workbook = $excel.workbooks.add()
 $workbook.workSheets.item(3).delete()
 $workbook.WorkSheets.item(2).delete()
 $workbook.WorkSheets.item(1).Name = $sheetName
 $sheet = $workbook.WorkSheets.Item($sheetName)
 AddExcelContent
} #end NewExcelSheet
Function AddExcelContent()
{
 $x = 2
 $lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
 $colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
 $borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
 For($b = 1 ; $b -le 2 ; $b++)
 {
  $sheet.cells.item(1,$b).font.bold = $true
  $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
  $sheet.cells.item(1,$b).borders.ColorIndex =$colorIndex::xlColorIndexAutomatic
  $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
 }
 $sheet.cells.item(1,1) = "CPU Time"
 Foreach($item in $aryProp)
 {
  $sheet.cells.item($x, 1) = $item
  $x++
 } #end foreach
 NewExcelRange
} #end AddExcelContent
Function NewExcelRange()
{
 $range = $sheet.usedRange
 $range.EntireColumn.AutoFit() | Out-Null
 NewExcelChart
} #end NewExcelRange
Function NewExcelChart()
{
 $chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
 $workbook.charts.add() | Out-Null
 $workbook.ActiveChart.chartType = $chartType::xl3DLine
 $workbook.ActiveChart.SetSourceData($range)
 $workbook.ActiveChart.HasTitle = $true
 $workbook.ActiveChart.ChartTitle.Text = "$Class`n$Property"
 SaveExcel
} #end NewExcelChart
Function SaveExcel()
 {
 IF(Test-Path $path)
   { 
    Remove-Item $path
    $Excel.ActiveWorkbook.SaveAs($path)
   }
 ELSE
   {
    $Excel.ActiveWorkbook.SaveAs($path)
   }
} #end SaveExcel
# *** EntryPoint ***
$computer = "."
$delay = 1
$reps = 10
$Class = "Win32_PerfFormattedData_PerfOS_Processor"
$key = "name"
$instance = "_Total"
$property = "PercentProcessorTime"
$script:sheetName = $Class.substring(6)
TestSheetName($script:sheetName)
$Path="c:\fso\$sheetName.xls"
CreateEmptyArray($reps)
GetWmiPerformanceData
NewExcelSheet($sheetName) 

The GetWmiPerformanceDataCreateExcelChart.ps1 script is organized into a series of functions. These functions help to make it easier to read, as well as easier to re-use the code. This technique also makes it easier to add additional functionality to the script later or to troubleshoot the script if something does not work properly.

The script begins with the TestSheetName function. This function is required because Microsoft Office Excel will not allow a worksheet name that is more than 31 characters in length. However, we have no such restriction for WMI class name lengths. Because the WMI class name is used to create the sheet name, it is necessary to be able to trim the length of the name if it is too long. To do this, we use the length property to see if it is greater than or equal to 30 characters. If it is, we use the substring method to grab 30 characters beginning at position 0. This is seen here:

Function TestSheetName($sheetName)
{
if($sheetName.length -ge 30)
 {
  $script:sheetName = $sheetName.substring(0,30)
 }
} #end TestSheetName

The next function we come to is the CreateEmptyArray function. This is used to create an empty function to hold the data from the WMI query. If you wanted to capture multiple data sets, you could create multiple arrays here. To create the empty array we use the static method CreateInstance from the system.array class. We give it the data type it will hold and the upper boundary. This is stored in a script level variable as seen here:

Function CreateEmptyArray($ubound)
{
 [int[]]$script:aryProp = [array]::CreateInstance("int",$ubound)
} #end CreateEmptyArray

The next thing we need to do is gather the WMI performance information. To do this, we use a for loop to control how many times we query for the information. The [WMI] type accelerator is used to facilitate direct connection to a specific instance of the WMI data. Everything in the connection string is abstracted into variables that control the way the query is generated. The computer name comes from the $computer variable and the class name is from the $class variable. The key value for the class and the specific instance of the performance counter are similarly supplied. By putting the entire query into a set of parentheses, we force the line to be parsed inside the parentheses first, and then we grab the value of the property we are interested in and write it to our array. This line of code is seen here:

$aryProp[$i] +=([wmi]"\\$computer\root\cimv2:$class.$key='$instance'").$Property

The next thing we do is use the Write-Progress cmdlet to display a progress indicator bar while we are gathering the information (for more information on Write-Progress, see this article):

Function GetWmiPerformanceData()
{
 For($i = 0 ; $i -le $reps -1 ; $i++)
  {
   $aryProp[$i] +=([wmi]"\\$computer\root\cimv2:$class.$key='$instance'").$Property
   Write-Progress -Activity "Obtaining Processor info" -Status "% complete: " `
   -PercentComplete $i
   Start-Sleep -Seconds $delay
  } #end for
}#end GetWmiPerformanceData

Now we need to create a Microsoft Excel spreadsheet. The code to create the spreadsheet is in the NewExcelSheet function. As a best practice, I try to use the verb/noun pattern that was established by the Windows PowerShell team. We first need to create an instance of the Excel.Application object. We do this by using the new-object cmdlet. Next, we make the spreadsheet visible by setting the visible property to true. After we have a blank Excel application, we need to add a workbook to it. We do this by using the workbooks.add method. We then delete the two extra spreadsheets that are not needed, rename the spreadsheet, and then use the item method from the worksheets collection to return a single worksheet. We then call the addExcelContent function. This code is seen here:

Function NewExcelSheet($sheetName)
{
 $excel = new-object -comobject excel.application
 $excel.visible = $true
 $workbook = $excel.workbooks.add()
 $workbook.workSheets.item(3).delete()
 $workbook.WorkSheets.item(2).delete()
 $workbook.WorkSheets.item(1).Name = $sheetName
 $sheet = $workbook.WorkSheets.Item($sheetName)
 AddExcelContent
} #end NewExcelSheet

The AddExcelContent function is used to add content to the Excel spreadsheet. The first thing we do is create some type enumerations that will govern the way the borders, color, and weight of the Excel spreadsheet. This information is all documented in MSDN. After we have created the enumerations, we use them to configure the first two rows in the spreadsheet. This is seen here:

$x = 2
 $lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
 $colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
 $borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]

 For($b = 1 ; $b -le 2 ; $b++)
 {
  $sheet.cells.item(1,$b).font.bold = $true
  $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
  $sheet.cells.item(1,$b).borders.ColorIndex =$colorIndex::xlColorIndexAutomatic
  $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
 }

Now we add our column heading and enter the data that is stored in the $aryProp array. To add the column heading we use the (1,1) syntax. This writes to the first cell in the first row. We then use a foreach loop to go through the elements of the array and write to subsequent rows in the spreadsheet. This is shown here:

$sheet.cells.item(1,1) = "CPU Time"
 Foreach($item in $aryProp)
 {
  $sheet.cells.item($x, 1) = $item
  $x++
 } #end foreach

The entire AddExcelContent function is shown here:

Function AddExcelContent()
{
 $x = 2
 $lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
 $colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
 $borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]

 For($b = 1 ; $b -le 2 ; $b++)
 {
  $sheet.cells.item(1,$b).font.bold = $true
  $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
  $sheet.cells.item(1,$b).borders.ColorIndex =$colorIndex::xlColorIndexAutomatic
  $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
 }
 
 $sheet.cells.item(1,1) = "CPU Time"

 Foreach($item in $aryProp)
 {
  $sheet.cells.item($x, 1) = $item
  $x++
 } #end foreach
 NewExcelRange
} #end AddExcelContent

At this point, we have an Excel spreadsheet with data from the WMI performance query written into the first column. We also have a column head and a dot-dash style border around the first two cells:

After we have created the spreadsheet, we need to create a range. The range will be used to create the chart in the next step. To create the range, we use the usedrange property from the worksheet object. We then use the autofit method to size the range to the number of cells that contain data. This is a nice feature. The NewExcelRange function is seen here:

Function NewExcelRange()
{
 $range = $sheet.usedRange
 $range.EntireColumn.AutoFit() | Out-Null
 NewExcelChart
} #end NewExcelRange

It is now time to create the chart. The Excel chart object is very rich, and you can waste a lot of time trying to create the perfect Excel chart. Here we create a basic three-dimensional line chart with the title that comes from the WMI class and the property we selected. The range object is used to set the data source for the chart. This is, I have found, the easiest way to add the data to the Excel chart. The NewExcelChart function is seen here:

Function NewExcelChart()
{
 $chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
 $workbook.charts.add() | Out-Null
 $workbook.ActiveChart.chartType = $chartType::xl3DLine
 $workbook.ActiveChart.SetSourceData($range)
 $workbook.ActiveChart.HasTitle = $true
 $workbook.ActiveChart.ChartTitle.Text = "$Class`n$Property"
 SaveExcel
} #end NewExcelChart

The completed chart is shown here:

We now want to save our work. To do this, we first see if an old version of the Excel spreadsheet exists. If it does, we delete it, and then create a new one by using the SaveAs method from the workbook object. This is seen in the SaveExcel function shown here:

Function SaveExcel()
 {
 IF(Test-Path $path)
   { 
    Remove-Item $path
    $Excel.ActiveWorkbook.SaveAs($path)
   }
 ELSE
   {
    $Excel.ActiveWorkbook.SaveAs($path)
   }
} #end SaveExcel

That is all the functions. To set everything up and to set everything in motion, we use the following code that initializes the variables, checks the spreadsheet name, and then calls the functions in the appropriate order. This is seen here:

$computer = "."
$delay = 1
$reps = 10
$Class = "Win32_PerfFormattedData_PerfOS_Processor"
$key = "name"
$instance = "_Total"
$property = "PercentProcessorTime"

$script:sheetName = $Class.substring(6)
TestSheetName($script:sheetName)
$Path="c:\fso\$sheetName.xlsx"

CreateEmptyArray($reps)
GetWmiPerformanceData
NewExcelSheet($sheetName) 

Well, CC, this has been a long answer to a short question. But I hope you were able to stay with it. Keep in mind that we will be having an Excel week coming up toward the end of December. There is also a TechNet magazine article on the way with some more Excel automation. I kind of jumped the gun a little because it fit in well with your question and with the performance monitoring we have been doing. The advantage of the function approach to building the script is that once you have particular functions, you just stick them together. Have an awesome day, and I will see you tomorrow for Quick-Hits Friday.

Ed Wilson and Craig Liebendorfer, Scripting Guys