Working with Perfmon CSV logs in Powershell – Part 1


I was looking at Chad Millers(Blog|Twitter) post ‘Importing CSV Files to SQL Server‘ which was part of Ed Wilson’s (Blog|Twitter) guest blogger week and thought I would share my experiences working with CSV files in PowerShell.

Few months back, I was working on some performance issues which required me to go through huge PerfMon Logs(around 400MB in size). Since, manually doing this was time consuming I wrote a couple of PowerShell one-liners which can get this information without too much work.

While I was trying to automate this solution, I broke down the problem into 3 different components:

  1. Read the header of the CSV file to get the headers and see which columns we would be interested in.
  2. Select a range of values, determined by the time slots we are interested in.
  3. Summarize the results using Measure-Object cmdlet.

 
Now, the details:

1. How to read only the header line of a CSV file and get the list of columns which we are interested in:

 #read the header
(Get-Content.\DBINST01_05070932.csv|Select-Object -First 1).Split(",")

2. How to read each line in a CSV file and select only the range of values we are interested in:

#First define the boundaries of the range we are interested in.
$startTime = Get-Date "05/07/2011 09:34:51.421" -Format G
$endTime = Get-Date "05/07/2011 10:06:51.421" -Format G 

#Next get the data.
#When dealing with large CSV files always pipe the output of Import-Csv cmdlet as this
#will process the file one line at a time. Back tick can be used to split a long line across
#multiple lines to improve readability. 

Import-Csv .\DBINST01_05070932.csv | ` 
Where-Object{((Get-Date $_.'(PDH-CSV 4.0) (Central Daylight Time)(300)') -gt $startTime) -and ` 
((Get-Date $_.'(PDH-CSV 4.0) (Central Daylight Time)(300)') -lt $endTime)}|`  
Select-Object '(PDH-CSV 4.0) (Central Daylight Time)(300)',` 
'\\CLGRP01\Processor(_Total)\% Processor Time',` 
'\\CLGRP01\MSSQL$DBINST01:SQL Statistics\Batch Requests/sec',` 
'\\CLGRP01\MSSQL$DBINST01:Buffer Manager\Buffer cache hit ratio'

3. You want to get the min, max and average values of the selected columns just, throw in a Measure-Object at the end

Import-Csv .\DBINST01_05070932.csv |` 
Where-Object{
((Get-Date $_.'(PDH-CSV 4.0) (Central Daylight Time)(300)') -gt $startTime) -and `
 ((Get-Date $_.'(PDH-CSV 4.0) (Central Daylight Time)(300)') -lt $endTime)
}|` 
Select-Object '(PDH-CSV 4.0) (Central Daylight Time)(300)',`
'\\CLGRP01\Processor(_Total)\% Processor Time',`
'\\CLGRP01\MSSQL$DBINST01:SQL Statistics\Batch Requests/sec',`
'\\CLGRP01\MSSQL$DBINST01:Buffer Manager\Buffer cache hit ratio'|` 
Measure-Object
'\\CLGRP01\Processor(_Total)\% Processor Time',` 
'\\CLGRP01\MSSQL$DBINST01:SQL Statistics\Batch Requests/sec',` 
'\\CLGRP01\MSSQL$DBINST01:Buffer Manager\Buffer cache hit ratio' -Minimum -Maximum –Average

This approach works for most csv files. But, there are some problems with the code given here, these are not major problems but minor hiccups that you might run into. I will blog about them soon enough.

Advertisements
About

By profession, I’m a SQL Server Database Administrator. I love to poke my nose into different corners and see how stuff looks in there. I keep looking for new things to do as my mind refuses to settle on one topic.

Tagged with: , , , , ,
Posted in PowerShell
3 comments on “Working with Perfmon CSV logs in Powershell – Part 1
  1. Good one. I was playing with import-Csv last week. workin on a Adding power shell injected functionality/ add on for health check Perfmon section.

  2. Did I mention your post was very educating? 🙂

  3. Hemanth says:

    Add-on. That is an interesting idea. We can templatize this code with a common counter list and then you can generate reports based on that. Or maintain a daily summary of performance behaviour by hour stored in a DB. It should give us a long term perspective on the performance topography of the shop.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: