This is the last part of my series about performance counters and PowerShell. I've shown you how to
get data from performance counters,
export them into usable formats and
export them into html file.
If your are not fan of export to HTML file, it's your party now – the scripts below save the data into Excel file.
What we already know
I assume you have all the data available. If you don't, you might check post about export to HTML [3] You will find there how to call start-counters.ps1 and Get-CountersData.ps1
You might have a look at HTML version. It displays counters related to Chrome browser when I started browsing Google Wave. We will add the same charts into a Excel sheet.
Export to Excel
[3]: .\Export-Counters2Excel.ps1 `
-csvFile C:\temp\counters\chrome-gw\res.csv `
-outputFile C:\temp\counters\chrome-gw\res.ods `
-interestingCounters `
'Process(chrome)\% Processor Time',`
('Process(chrome)\% User Time','Process(chrome)\% Privileged Time'),`
('Process(chrome)\Virtual Bytes','Process(chrome)\Working Set',`
'Process(chrome)\Page File Bytes','Process(chrome)\Private Bytes',`
'Process(chrome)\Working Set - Private'), `
'Process(chrome)\Thread Count',`
'Process(chrome)\Handle Count'
It's pretty much the same as export to HTML. Instead of -cliXmlFile you use -csvFile which is (not surprisingly) csv file with counters data.
For details about -outputFile and -interestingCounters look at previous post [3].
Here is a screen shot with one chart:
How it works
You can find plenty of howtos that describe Excel automation via PowerShell. However I haven't found any that describes how to add charts to the sheet. So, here is a quick tour:
At the beginning we create a Excel com instance.
$xl = new-object -com excel.application
In the second step we need to import a csv file. I use semicolon as separator in csv to avoid mixing comma as separator and comma as floating point sign.
I had some troubles with encoding (czech letters) when I tried to import csv file in UTF8, that's why the data exported by Get-CountersData.ps1 are saved in Unicode.
$origin = [int][Microsoft.Office.Interop.Excel.XlPlatform]::xlWindows
$startRow = 1
$dataType = [int][Microsoft.Office.Interop.Excel.XlTextparsingType]::xlDelimited
$textQualifier = [int][Microsoft.Office.Interop.Excel.XlTextQualifier]::xlTextQualifierDoubleQuote
$consecutiveDelimiter = $false
$tab = $false
$semicolon = $true
$comma = $false
$space = $false
$other = $false
$otherChar = $false
$xl.Workbooks.OpenText( `
$csvFile, $origin, $startRow, $dataType, $textQualifier, $consecutiveDelimiter, $tab, `
$semicolon, $comma, $space, $other, $otherChar)
We get Excel worksheet and expand the columns width so that we can see all the data.
$wb = $xl.Workbooks.item(1)
$ws = $wb.Worksheets.item(1)
$wsb = $ws.UsedRange
[Void]$wsb.EntireColumn.AutoFit() # or $wsb.columns.AutoFit()
We have all prepared so we can add some charts. You can experiment with chart type from enum [Microsoft.Office.Interop.Excel.XlChartType].
Data for the chart are specified in command $ws.range – you pass in a string that contains columns separated by semicolon.
I needed to place charts one after another, so the only variable property is $ch.ChartArea.Top that I change.
$ch = $ws.shapes.addchart().chart
$ch.chartType = [Microsoft.Office.Interop.Excel.XlChartType]::xlLine
$range = $ws.range('$D$1:$D$98;$J$1:$J$98;$F$1:$F$98;$G$1:$G$98;$J$1:$J$98')
$ch.setSourceData($range, [int][Microsoft.Office.Interop.Excel.xlrowcol]::xlColumns)
$ch.ChartArea.Width, $ch.ChartArea.Height = 800,400
$ch.ChartArea.Left, $ch.ChartArea.Top = 0,($chartIndex*$ch.ChartArea.Height)
When we are done, we save the file and end the work with Excel.
$wb.SaveAs($outputFile, [int][Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenDocumentSpreadsheet)
$xl.quit()
Download
Download the PowerShell script:
Check all the parts
-
Series about performance counters [1] - how to read the data
-
Series about performance counters [2] - process the data and export them
-
Series about performance counters [3] - show me the data in my browser
-
Series about performance counters [4] - export data into Excel – currently reading
That's all. I hope you enjoyed the series. If you have any questions, contact me at Twitter or send me a message.