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:

Result with the charts

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

  1. Series about performance counters [1] - how to read the data
  2. Series about performance counters [2] - process the data and export them
  3. Series about performance counters [3] - show me the data in my browser
  4. 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.

Meta: 2009-11-18, Pepa

This is third part in series about how to work with performance counters in PowerShell and visualize them. The last step in our series is to display the data that we collected in previous post. The easier way is to generate HTML, the slitly more difficult – export to Excel – way will be covered next time.
The tool of choice is PowerShell, of course. We will use canvas to get pretty results. That's why you need to try it in Opera, Firefox, Chrome or Safari. Not in IE. It works in IE as well, although it is a little bit slow (tested in IE8).

Look at the result.

It displays counters related of Chrome browser when I started browsing Google Wave.

What we already know from previous posts

Start PowerShell console and get data from some performance counters.

[0]: Start-Job {
    d:\start-counters.ps1 `
        -dir c:\temp\counters\chrome-gw `
        -start (get-date) -end (get-date).addminutes(10) `
        -computer devol `
        -counterNames `
            '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)\Thread Count','Process(chrome)\Handle Count',
            'Process(chrome)\Working Set - Private' `
        -secsWait 5
}
[1]:  #we will wait 10 minutes until the task is finished
[2]: d:\Get-CountersData.ps1 C:\temp\counters\chrome-gw

First we collect data from counters and process them (convert them into csv and clixml files). If you are lost, check the two previous parts

Export to html

[3]: d:\Export-Counters2Html.ps1 -clixmlFile C:\temp\counters\chrome-gw\res.clixml `
        -outputFile C:\temp\counters\chrome-gw\res.html `
        -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'

What are the parameters?

  • -clixmlFile is output of previous function Get-CountersData.ps1
  • -outputFile is path to file that will contain the result html such as my example html file
  • -interestingCounters specifies the counters that should be displayed. Note that it is array of arrays. The structure is as follows:
    (name of counter1 for first chart, name of counter2 of first chart,...),
    (name of counterx for second chart, (name of countery for second chart, ...),
    ...,
    counterA that is the only in 1000th chart, # this need not be an array, just string is ok
    ...
    So in my example 'Process(chrome)\% Processor Time' will be the only counter in first chart, whereas 'Process(chrome)\% User Time' and 'Process(chrome)\% Privileged Time' will be displayed together in second chart.

After you have run the Export-Counters2Html script, you need to do a final step. The charts are drawn using great canvas library jQuery Visualize Plugin. I prepared only the needed files – just download visualize.zip, and unzip it into folder with the resulting html. That's all.

If you don't use browser that supports canvas, look at the screen shot or in light box:

Result with the charts

Download

Download the PowerShell script for exporting to html and jquery canvas plugin that draws the charts.

Check all the parts

Today I've shown you how to visualize the data in your browser using html5 and canvas with help of PowerShell. In the last chapter we will try to achieve the same – visualize the data using charts in Excel.

  1. Series about performance counters [1] - how to read the data
  2. Series about performance counters [2] - process the data and export them
  3. Series about performance counters [3] - show me the data in my browser – currently reading
  4. Series about performance counters [4] - export data into Excel

Meta: 2009-10-29, Pepa

In previous post we collected raw data from performance counters and now it's time to transform them into more usable form. It's very easy:

[0]: $res = d:\Get-CountersData.ps1  d:\temp\counters -pass

What it does

It takes all the *.clixml files from specified directory ('d:\temp\counters') produced by start-counters.ps1 from the first part and creates PsObjects so that command lets like e.g. select-object or format-list work properly on them. The result is stored in csv file and clixml file. If you would like to return the object from the script, use -passThrough parameter.

The csv file will be used later when we will visualize the data in Excel.

[1]: $res | gm -membertype noteproperty | select Name,Definition

Name                                           Definition
----                                           ----------
\\hydrant\process(firefox)\% privileged time     System.Double \\hydrant\process(firefox)\% pri...
\\hydrant\process(firefox)\% processor time      System.Double \\hydrant\process(firefox)\% pro...
\\hydrant\process(firefox)\% user time           System.Double \\hydrant\process(firefox)\% use...
\\hydrant\process(firefox)\page file bytes       System.Double \\hydrant\process(firefox)\page ...
\\hydrant\process(firefox)\private bytes         System.Double \\hydrant\process(firefox)\priva...
\\hydrant\process(firefox)\thread count          System.Double \\hydrant\process(firefox)\threa...
\\hydrant\process(firefox)\virtual bytes         System.Double \\hydrant\process(firefox)\virtu...
\\hydrant\process(firefox)\working set           System.Double \\hydrant\process(firefox)\worki...
\\hydrant\process(firefox)\working set - private System.Double \\hydrant\process(firefox)\worki...

[2]: $res[0]

\\hydrant\process(firefox)\% processor time      : 0
\\hydrant\process(firefox)\% user time           : 0
\\hydrant\process(firefox)\% privileged time     : 0
\\hydrant\process(firefox)\virtual bytes         : 120213504
\\hydrant\process(firefox)\working set           : 27373568
\\hydrant\process(firefox)\page file bytes       : 15654912
\\hydrant\process(firefox)\private bytes         : 15654912
\\hydrant\process(firefox)\thread count          : 16
\\hydrant\process(firefox)\working set - private : 13991936

Note to csv export

I had some difficulties with Excell if the data in csv file were delimited by comma or tab \t. That's why the fields are delimited by semicolon ;.
If names of your performance counters are not localized, you can change the encoding to Default or omit it at all. Why I used Unicode? It's because you can not specify the format of incomming data when you import csv file to Excel. Then the localized names of counters are screwed up. However, Excel is able to handle the Unicode encoding and read it correctly.

Now you have prepared your data for statistics and visualization tools. You can try basic measures via measure-object (like $res | measure-object -Average -Property '\\hydrant\process(firefox)\virtual bytes') or wait until next post when we will show the data in charts.

Check all the parts

  1. Series about performance counters [1] - how to read the data
  2. Series about performance counters [2] - process the data and export them – currently reading
  3. Series about performance counters [3] - show me the data in my browser
  4. Series about performance counters [4] - export data into Excel

Meta: 2009-10-22, Pepa

Have you ever developed a web application where performance plays a key role? Are you curious what are performance counters in your system? Do you use PowerShell and you would like to learn something new (or better something that is not mentioned very often)? If you answered 'yes' at least once, read on.

What you can expect

This mini series is mainly about how to use performance counters and visualize them. The tool of my choice is (believe or not) PowerShell. It's easy to read counters and process them. I won't explain what performance counters are, because it has been done many times before. Just check e.g. ASP.NET Performance Monitoring, and When to Alert Administrators, Performance Counters for ASP.NET, Chapter 15 - Measuring .NET Application Performance or any other resource.

This mini series is divided into more parts:

  • collecting data
  • processing data
  • visualizing data

How to collect data

I've written a powershell script that starts at specified time -start and collects data from performance counters until -end. The results are stored in a directory. How to call the script:

[0]: start-job {
  d:\start-counters.ps1 `
    -dir d:\temp\counters `
    -start (get-date) `
    -end (get-date).addminutes(15) `
    -computer hydrant `
    -secsWait 5 `
    -counterNames `
     '\Process(Firefox)\% Processor Time',`
     '\Process(Firefox)\% User Time',`
     '\Process(Firefox)\% Privileged Time',`
     '\Process(Firefox)\Virtual Bytes',`
     '\Process(Firefox)\Working Set',`
     '\Process(Firefox)\Page File Bytes',`
     '\Process(Firefox)\Private Bytes',
     '\Process(Firefox)\Thread Count',`
     '\Process(Firefox)\Working Set - Private'
 }

The script itself is executed as job, so that it doesn't block your console. The most important parameter is -counterNames that specifies names of observed counters. If you don't have any idea where did I take the performance counters, check some utility functions. Performance counters are queried every 5 seconds and the result for each query is stored at d:\temp\counters.
The great news is that you are able to check counters at other computer! So just run the checking script on your machine and query your server with test application. You will probably need administrator rights for that.

The output files at d:\temp\counters are waiting there to be processed by other scripts that are responsible for transforming the data into usable structures. This step will be covered in second post of this series.

Now if you know what counters to use (~ what to pass as value to -counterNames param), you are finished for today. Otherwise the following section will give you some means how to find the counters interesting for you.

Names of performance counters

If you are lost and you don't know where to to start, the functions defined below could help.

function ListbaseCounters {
  param ([Parameter(Mandatory=$true)][string]$computer )
  get-counter -list * -ComputerName $computer | select -exp CounterSetName
}

This function returns all the categories of performance counters. When I run it for my computer, the result is:

  • TBS counters
  • WSMan Quota Statistics
  • Netlogon
  • HTTP Service
  • HTTP Service Url Groups
  • HTTP Service Request Queues
  • Process
  • WMI Objects
  • MSiSCSI_InitiatorInstanceStatistics
  • ProcessorPerformance
Each of these categories contains specific counters, e.g. for Process there are '\Process(*)\% Processor Time', '\Process(*)\Working Set', … To find the names, the following function is useful:

function ListInnerCounters    {
 param([Parameter(Mandatory=$true)][string]$name, 
       [Parameter(Mandatory=$true)][string]$computer, 
       [switch]$pathsWithInstances
 )
 Get-Counter -list $name -computer $computer | 
     select -exp $(if($pathsWithInstances){'pathswithinstances'}else{'paths'})
}

Simply call

[1]: ListInnerCounters -name proces -computer hydrant -pathsWithInstances

and the output could be

\\hydrant\Process(Idle)\% Processor Time
\\hydrant\Process(System)\% Processor Time
\\hydrant\Process(smss)\% Processor Time
\\hydrant\Process(csrss)\% Processor Time
…

These names are used as value of -counterNames parameter. You may use this values without computer name '\\hydrant' because you specify the computer name in parameter -computer.
Note: you may try to call the function withou parameter -pathsWithInstances and see what it does.

Today we have collected some data. In second part we will read them and transform them into more usable form.

Check all the parts

  1. Series about performance counters [1] - how to read the data – currently reading
  2. Series about performance counters [2] - process the data and export them
  3. Series about performance counters [3] - show me the data in my browser
  4. Series about performance counters [4] - export data into Excel

Meta: 2009-10-19, Pepa