Extracting perfmon stats using PowerShell

I recently got asked to extract certain performance metrics from multiple servers (at the same time) and put them in an Excel graph.  As I didn't have any software to do this I decided to extract the results from PerfMon using Powershell and collate them into a CSV.

The fist step was creating the PowerShell command.  As we wanted to change the servers easily as well as the counters being used we created a generic one line command:

In the Counters.Txt contained:

\Processor Information(_Total)\% Processor Time
\Memory\% Committed Bytes In Use
 \network interface(*)\bytes total/sec
 \logicaldisk(_total)\current disk queue length

The Servers.txt file contained a list of all the servers that needed to be monitored:

Server1
Server2
Server3

Running the PowerShell produced a CSV file but when loaded into Excel the results in the file clearly needed some amending before we could create the graphs we needed:

The values for each server were stored in a single cell for each timestamp (as shown above).

So staying in Excel I created one of the most horrible Excel queries I've ever written it does the job but like all Excel queries it is not pretty!

This can be broken down to make it a bit more readable:

The first part:

This reformats the string by removing the carriage returns, slashes and replacing them with commas if necessary.

The final part of the formula inserts carriage returns for each server, so the final part replaces the server names we are extracting from and ensures that there is a return code first then it inserts the time (from the B1 cell in this case):

Placing the formula for into the D1 cell and copying it down to every cell creates a spreadsheet like this:

Copy the contents of column D into a new sheet and Paste it into column A (Paste using values) and then save that worksheet as a CSV file.
Accept the messages stating that formatting won't be saved and that only the selected workbook will be saved.
Also save the entire workbook as an Excel file, just in case.

I found that the file seems to have quotes in each cell making Excel look like the file is empty when it loads the file so I opened the file in Notepad++ and did a search and replace simply replacing the " with nothing:

This provides a file that can be loaded into Excel:

I then sorted the data by Column B (ServerName) and then by Column C (Setting) and finally by Column A (Time).  I also changed the formatting of Column A to a Time.

It now looks like this:

Lastly I then highlighted the Time (Column A) and the Value (Column E) for a given metric (in the example below Memory) and then clicked Insert and Scatter graph (I picked the one with straight lines) and you've got a graph!

You can cut and paste it onto a different worksheet if required.

Not the most straight forward process but could be useful?