![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/h2>\n <\/h2>\nHey, 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!<\/p>\n
– CC<\/p>\n
![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
<\/p>\n
Hi CC,<\/p>\n
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<\/b> script, which follows this paragraph. Even though we do not have this exact script written in VBScript, there are a number of scripts<\/a> that talk about creating charts and working with Excel in the Script Center. There are also almost 100 \u201cHey, Scripting Guy!\u201d articles<\/a>\u2014nearly 4 months\u2019 worth of articles\u2014that specifically address VBScript and Excel. The concepts are the same. If you wish, you can migrate this script to VBScript. Use this reference<\/a> 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<\/a> to share it with the Community-Submitted Scripts Center<\/a>. If you need more information about performance monitoring with Windows PowerShell, refer to the scripts from earlier this week<\/a>.<\/p>\n Here\u2019s the GetWmiPerformanceDataCreateExcelChart.ps1<\/b> script:<\/p>\n The GetWmiPerformanceDataCreateExcelChart.ps1<\/b> 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.<\/p>\n The script begins with the TestSheetName<\/b> 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<\/b> property to see if it is greater than or equal to 30 characters. If it is, we use the substring<\/b> method to grab 30 characters beginning at position 0. This is seen here:<\/p>\n The next function we come to is the CreateEmptyArray<\/b> 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<\/b> from the system.array<\/b> 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:<\/p>\n The next thing we need to do is gather the WMI performance information. To do this, we use a for<\/b> loop to control how many times we query for the information. The [WMI]<\/b> 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<\/b> variable and the class name is from the $class<\/b> 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:<\/p>\n The next thing we do is use the Write-Progress<\/b> cmdlet to display a progress indicator bar while we are gathering the information (for more information on Write-Progress<\/b>, see this article<\/a>):<\/p>\n Now we need to create a Microsoft Excel spreadsheet. The code to create the spreadsheet is in the NewExcelSheet<\/b> function. As a best practice, I try to use the verb\/noun pattern<\/a> that was established by the Windows PowerShell team. We first need to create an instance of the Excel.Application<\/b> object. We do this by using the new-object<\/b> cmdlet. Next, we make the spreadsheet visible by setting the visible<\/b> 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<\/b> method. We then delete the two extra spreadsheets that are not needed, rename the spreadsheet, and then use the item<\/b> method from the worksheets<\/b> collection to return a single worksheet. We then call the addExcelContent<\/b> function. This code is seen here:<\/p>\nFunction TestSheetName($sheetName)\n{\nif($sheetName.length -ge 30)\n {\n $script:sheetName = $sheetName.substring(0,30)\n }\n} #end TestSheetName\nFunction CreateEmptyArray($ubound)\n{\n [int[]]$script:aryProp = [array]::CreateInstance(\"int\",$ubound)\n} #end CreateEmptyArray\nFunction GetWmiPerformanceData()\n{\n For($i = 0 ; $i -le $reps -1 ; $i++)\n {\n $aryProp[$i] +=([wmi]\"\\\\$computer\\root\\cimv2:$class.$key='$instance'\").$Property\n Write-Progress -Activity \"Obtaining Processor info\" -Status \"% complete: \" `\n -PercentComplete $i\n Start-Sleep -Seconds $delay\n } #end for\n}#end GetWmiPerformanceData\nFunction NewExcelSheet($sheetName)\n{\n $excel = new-object -comobject excel.application\n $excel.visible = $true\n $workbook = $excel.workbooks.add()\n $workbook.workSheets.item(3).delete()\n $workbook.WorkSheets.item(2).delete()\n $workbook.WorkSheets.item(1).Name = $sheetName\n $sheet = $workbook.WorkSheets.Item($sheetName)\n AddExcelContent\n} #end NewExcelSheet\nFunction AddExcelContent()\n{\n $x = 2\n $lineStyle = \"microsoft.office.interop.excel.xlLineStyle\" -as [type]\n $colorIndex = \"microsoft.office.interop.excel.xlColorIndex\" -as [type]\n $borderWeight = \"microsoft.office.interop.excel.xlBorderWeight\" -as [type]\n For($b = 1 ; $b -le 2 ; $b++)\n {\n $sheet.cells.item(1,$b).font.bold = $true\n $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot\n $sheet.cells.item(1,$b).borders.ColorIndex =$colorIndex::xlColorIndexAutomatic\n $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium\n }\n $sheet.cells.item(1,1) = \"CPU Time\"\n Foreach($item in $aryProp)\n {\n $sheet.cells.item($x, 1) = $item\n $x++\n } #end foreach\n NewExcelRange\n} #end AddExcelContent\nFunction NewExcelRange()\n{\n $range = $sheet.usedRange\n $range.EntireColumn.AutoFit() | Out-Null\n NewExcelChart\n} #end NewExcelRange\nFunction NewExcelChart()\n{\n $chartType = \"microsoft.office.interop.excel.xlChartType\" -as [type]\n $workbook.charts.add() | Out-Null\n $workbook.ActiveChart.chartType = $chartType::xl3DLine\n $workbook.ActiveChart.SetSourceData($range)\n $workbook.ActiveChart.HasTitle = $true\n $workbook.ActiveChart.ChartTitle.Text = \"$Class`n$Property\"\n SaveExcel\n} #end NewExcelChart\nFunction SaveExcel()\n {\n IF(Test-Path $path)\n { \n Remove-Item $path\n $Excel.ActiveWorkbook.SaveAs($path)\n }\n ELSE\n {\n $Excel.ActiveWorkbook.SaveAs($path)\n }\n} #end SaveExcel\n# *** EntryPoint ***\n$computer = \".\"\n$delay = 1\n$reps = 10\n$Class = \"Win32_PerfFormattedData_PerfOS_Processor\"\n$key = \"name\"\n$instance = \"_Total\"\n$property = \"PercentProcessorTime\"\n$script:sheetName = $Class.substring(6)\nTestSheetName($script:sheetName)\n$Path=\"c:\\fso\\$sheetName.xls\"\nCreateEmptyArray($reps)\nGetWmiPerformanceData\nNewExcelSheet($sheetName) \n<\/pre>\nFunction TestSheetName($sheetName)\n{\nif($sheetName.length -ge 30)\n {\n $script:sheetName = $sheetName.substring(0,30)\n }\n} #end TestSheetName\n<\/pre>\nFunction CreateEmptyArray($ubound)\n{\n [int[]]$script:aryProp = [array]::CreateInstance(\"int\",$ubound)\n} #end CreateEmptyArray\n<\/pre>\n$aryProp[$i] +=([wmi]\"\\\\$computer\\root\\cimv2:$class.$key='$instance'\").$Property<\/pre>\n
Function GetWmiPerformanceData()\n{\n For($i = 0 ; $i -le $reps -1 ; $i++)\n {\n $aryProp[$i] +=([wmi]\"\\\\$computer\\root\\cimv2:$class.$key='$instance'\").$Property\n Write-Progress -Activity \"Obtaining Processor info\" -Status \"% complete: \" `\n -PercentComplete $i\n Start-Sleep -Seconds $delay\n } #end for\n}#end GetWmiPerformanceData\n<\/pre>\nFunction NewExcelSheet($sheetName)\n{\n $excel = new-object -comobject excel.application\n $excel.visible = $true\n $workbook = $excel.workbooks.add()\n $workbook.workSheets.item(3).delete()\n $workbook.WorkSheets.item(2).delete()\n $workbook.WorkSheets.item(1).Name = $sheetName\n $sheet = $workbook.WorkSheets.Item($sheetName)\n AddExcelContent\n} #end NewExcelSheet\n<\/pre>\n
![\"Image](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/hsg\/hey1127\/HSG_Perf4_01.jpg\")
<\/p>\n![\"Image](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/hsg\/hey1127\/HSG_Perf4_02.jpg\")
<\/p>\n