Working with Perfmon CSV logs in Powershell – Part 3


In the final post of the series, we will look at some of the common problems that you might encounter when reading CSV files, and how to circumvent these problems.

As usual, first we need to get a list of counters so let us quickly do that using the below code. The output is going to be a list of counters that are there in the files. The counters are going to be displayed on the screen.


$csvFile = '.\DBINST01_05070932.csv'
(Get-Content $csvFile|Select-Object -First 1).Split(",")

Once we have the counters that we need to read, the next thing to do would be to select the counters and work with them.

One of the common problems we might face is that the counter values maybe empty. When you encounter empty values in the data, we use the Where-Object cmdlet to skip that row. Unfortunately, I was not able to find a proper workaround because, we cannot skip the values for individual counters; the whole line will not be processed.  So, if you are working on multiple counters it is better to process data for each counter individually.

The other problem is with formatting. The default output of Measure-Object cmdlet is a list. This can be circumvented using Format-Table cmdlet along with column formatters defined beforehand.


$startDate = Get-Date "08/09/2011 07:00:00.000" -Format G
$endDate = Get-Date "08/09/2011 08:01:00.000" -Format G
$startTime = Get-Date $startDate -UFormat '%H:%M:%S'
$endTime = Get-Date $endDate -UFormat '%H:%M:%S'

$name = @{ l = "name"; e = { $_.Property} ;f = "{0,-50}" }
$minVal = @{ l = "Min.Value"; e = { $_.Minimum};    f = "{0:N2}"}
$maxVal = @{ l = "Max.Value"; e = { $_.Maximum};    f = "{0:N2}"}
$avgVal = @{ l = "Avg.Value";    e = { $_.Average}; f = "{0:N2}"}

$fields = $name, $minVal, $maxVal, $avgVal

Import-Csv $csvFile | `
Where-Object{
    ((Get-Date $_.'(PDH-CSV 4.0) (Central Daylight Time)(300)') -gt $startDate) -and `
    ((Get-Date $_.'(PDH-CSV 4.0) (Central Daylight Time)(300)') -lt $endDate) -and `
    ((Get-Date $_.'(PDH-CSV 4.0) (Central Daylight Time)(300)' -UFormat '%H:%M:%S') -gt $startTime) -and `  
    ((Get-Date $_.'(PDH-CSV 4.0) (Central Daylight Time)(300)' -UFormat '%H:%M:%S') -lt $endTime) -and `  
    ($_.'\\SERVR01\LogicalDisk(F:)\Avg. Disk sec/Read' -ne ' ') -and `   
    ($_.'\\SERVR01\LogicalDisk(F:)\Avg. Disk sec/Transfer' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(F:)\Avg. Disk sec/Write' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(G:)\Avg. Disk sec/Read' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(G:)\Avg. Disk sec/Transfer' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(G:)\Avg. Disk sec/Write' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(H:)\Avg. Disk sec/Read' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(H:)\Avg. Disk sec/Transfer' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(H:)\Avg. Disk sec/Write' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(L:)\Avg. Disk sec/Read' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(L:)\Avg. Disk sec/Transfer' -ne ' ') -and `
    ($_.'\\SERVR01\LogicalDisk(L:)\Avg. Disk sec/Write' -ne ' ') -and `
    ($_.'\\SERVR01\Processor(_Total)\% Processor Time' -ne ' ') -and `
    ($_.'\\SERVR01\System\Processor Queue Length' -ne ' ') -and `
    ($_.'\\SERVR01\MSSQL$DBINST01:Wait Statistics(Average wait time (ms))\Log write waits' -ne ' ') `
    }|`
Select-Object `
    '(PDH-CSV 4.0) (Central Daylight Time)(300)',`
 '\\SERVR01\LogicalDisk(F:)\Avg. Disk sec/Read',`
 '\\SERVR01\LogicalDisk(F:)\Avg. Disk sec/Transfer',`
 '\\SERVR01\LogicalDisk(F:)\Avg. Disk sec/Write',`
 '\\SERVR01\LogicalDisk(G:)\Avg. Disk sec/Read',`
 '\\SERVR01\LogicalDisk(G:)\Avg. Disk sec/Transfer',`
 '\\SERVR01\LogicalDisk(G:)\Avg. Disk sec/Write',`
 '\\SERVR01\LogicalDisk(H:)\Avg. Disk sec/Read',`
 '\\SERVR01\LogicalDisk(H:)\Avg. Disk sec/Transfer',`
 '\\SERVR01\LogicalDisk(H:)\Avg. Disk sec/Write',`
 '\\SERVR01\LogicalDisk(L:)\Avg. Disk sec/Read',`
 '\\SERVR01\LogicalDisk(L:)\Avg. Disk sec/Transfer',`
 '\\SERVR01\LogicalDisk(L:)\Avg. Disk sec/Write',`
 '\\SERVR01\Processor(_Total)\% Processor Time',`
 '\\SERVR01\System\Processor Queue Length' |`
Measure-Object `
    '\\SERVR01\LogicalDisk(F:)\Avg. Disk sec/Read',`
    '\\SERVR01\LogicalDisk(F:)\Avg. Disk sec/Transfer',`
 '\\SERVR01\LogicalDisk(F:)\Avg. Disk sec/Write',`
 '\\SERVR01\LogicalDisk(G:)\Avg. Disk sec/Read',`
 '\\SERVR01\LogicalDisk(G:)\Avg. Disk sec/Transfer',`
 '\\SERVR01\LogicalDisk(G:)\Avg. Disk sec/Write',`
 '\\SERVR01\LogicalDisk(H:)\Avg. Disk sec/Read',`
 '\\SERVR01\LogicalDisk(H:)\Avg. Disk sec/Transfer',`
 '\\SERVR01\LogicalDisk(H:)\Avg. Disk sec/Write',`
 '\\SERVR01\LogicalDisk(L:)\Avg. Disk sec/Read',`
 '\\SERVR01\LogicalDisk(L:)\Avg. Disk sec/Transfer',`
 '\\SERVR01\LogicalDisk(L:)\Avg. Disk sec/Write',`
 '\\SERVR01\Processor(_Total)\% Processor Time',`
 '\\SERVR01\System\Processor Queue Length',`
 -Minimum -Maximum -Average |`
Format-Table $fields -Wrap

 

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

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: