A few weeks ago I ran into an issue after setting up perfmon (Performance Monitor) to run on one of my servers and trying to save the data to a .csv file for further analysis. I was getting some weird error. Data source C:\PerfLogs\test.csv is either invalid or cannot be found. The specified log type has not been installed on this computer”
OK. No problem. I know I did not have MS Excel on the server. Maybe that was the cause of the error? I decided to try and run the counters from one of my test servers that did MS Excel installed. I still got the same error. When I posted a question about this error on Twitter only one person replied. Aaron Nelson ( twitter | blog ) He mentioned using PowerShell to get the data and send it right to excel (or csv). So I started exploring that idea.
Finding the Counters
There are certain counters I want to use but I am not all too sure about how to use or even call them with PowerShell. This is just one of the areas PowerShell is so awesome. When you are starting to use a new commandlet (for me anyway), one of the first thing I like to do is use the built in help to get some details. You do that with the following command.
Out spills a wealth of information. I highly recommend trying this with any commands you use in PowerShell. O.K. now that we have a little more knowledge about “Get-Counter” let’s try a few of the commands we just found.
OK. Now that’s a lot of information. It is kind of confusing if not intimidating at first glance. We don’t need all of it for now so let’s narrow down our return to make things easier to read and digest.
Now, that’s what we want to see, a nice clean and easy to read list.
Now that we have a list of all the counters by names and I hope you agree this is easier to view. All of these counters have specific (sub) counters and paths we need to find under their covers. So, let’s expose them a bit more by using the following command.
Now we are getting somewhere. This list is much easier to work with and figure out what we need. But we still want to narrow things down more and get only specific memory counters. Try this one.
Now the we have the “path” needed for the counter we will return some data of the counter to the screen.
You can have the data continuously sent to the screen (or a file). We make that happen with the following.
Pulling the Counters Together
After you determine the counters you want to monitor you can send the data to many different file formats like a text file or a csv file. Now this next bit of code is a little more advanced, but not too far out of reach. First we are setting the counter path to a variable named “page_Counter”.
$page_Counter = “\Paging File(*)\% Usage”
Then using that variable we are piping Get-Counter to another variable called “pagedata”
$pagedata = Get-Counter $page_Counter
After that we are using the Select-Object to only return the rows we want to see. Then we pipe the results to a text file and a csv file. Keep in mind you need to have the code on a single line.
$pagedata.counterSamples | Select-Object TimeStamp, CookedValue, RawValue, SecondValue | Export-CSV “C:\test1.csv” -NoTypeInformation
That’s all there is too it. This solved my error problem and learned some new stuff in the process. I hope this helps provide some instructions and inspiration into using PowerShell to gather PerfMon data.