Dan McKinley

Here's the scenario: I had about eight hours of perfmon output, for several hundred counters sampled once per second. I wanted to put this into a database already containing the parsed IIS logs from the same machine, to try to correlate some of the resource utilization peaks with URL's.

The only snag was that Microsoft's LogParser couldn't handle the CSV files that perfmon generated. The input lines were far too long for it.

The call is from heroism. Will you accept the charges?

The solution I came up with was to hack together an MSH script that processed the files. It's not pretty (because it didn't have to be), but here it is.

#
# trim-perflog.msh
#   This pares down a set of enormous perfmon logs to a
#   size that can be managed by the Microsoft LogParser.
#
#   This finds the column indices that we're interested in,
#   then runs through each line in the input CSV file(s)
#   and pulls them out.
#

# The counters in the perfmon trace that we're interested in.
#
$counters =
"(PDH-CSV 4.0) (Eastern Standard Time)(300)",
"\\machine\.NET CLR Exceptions(w3wp)\# of Exceps Thrown / sec",
"\\machine\.NET CLR LocksAndThreads(w3wp)\Contention Rate / sec",
"\\machine\.NET CLR Memory(w3wp)\% Time in GC",
"\\machine\.NET CLR Remoting(w3wp)\Remote Calls/sec",
"\\machine\Process(w3wp)\Page Faults/sec",
"\\machine\Process(w3wp)\% Processor Time",
"\\machine\Process(w3wp)\% User Time",
"\\machine\Process(w3wp)\% Privileged Time",
"\\machine\Process(w3wp)\Private Bytes";

# Prettier names for the counters.
#
$columnAlias = "time", "exceptionsPerSecond",
"contentionPerSecond", "pctTimeInGC",
"remoteCallsPerSecond", "pageFaultsPerSecond",
"pctProcessorTime", "pctUserTime",
"pctPrivelegedTime", "privateBytes";

# Returns an array that contains the index of each of the
# $counters in the csv.
#
function getPerflogIndices
{
    param([System.String]$columns)

    $tokens = $columns.Split(',');
    for($i = 0; $i -lt $tokens.Length; $i++)
    {
        $t = $tokens[$i];
        $t = $t.Substring(1, $t.Length-2);
        $index = -1 ;
        for($j = 0; $j -lt $counters.Length; $j++)
        {
            if($t -eq $counters[$j])
            {
                $index = $j;
            }
        }
        if($index -ge 0)
        {
            write-object $i;
        }
    }
}

# writes a CSV line using the values in $array.
#
function write-array
{
    param([System.IO.StreamWriter]$writer, $array)
    $j = 0;
    $last = $array.Length;
    foreach($a in $array)
    {
        $writer.Write($a);
        if($j -ne $last)
        {
            $writer.Write(",");
        }
        $j++;
    }
    $writer.WriteLine();
}

# Writes a single line to the result CSV file.
# This requires:
#    $writer  - The output stream.
#    $ln      - A single line read from
#                the input stream.
#    $indices - The column indices of the
#                  subject perfmon counters.
#
function write-csv-line
{
    param([System.IO.StreamWriter]$writer,
       [System.String]$ln, [System.Object[]]$indices)

    $vals = $ln.Split(',');
    $j = 0;
    $last = $indices.Length - 1;
    foreach($i in $indices)
    {
        $writer.Write($vals[$i]);
        if($j -ne $last)
        {
            $writer.Write(",");
        }
        $j++;
    }
    $writer.WriteLine();
}

# Trims down the input CSV file. If $names is True,
# writes the names of the columns as the first line
# in the output.
function trim-perflog
{
    param([System.IO.FileInfo]$in, [System.Boolean]$names)

    $r = $in.OpenText();
    $ln = $r.ReadLine();
    $indices = getPerfLogIndices $ln;

    $wr = [System.IO.File]::AppendText($out);
    if($names)
    {
        write-array $wr $columnAlias;
    }
    while($r.Peek() -ge 0)
    {
        write-csv-line $wr $r.ReadLine() $indices;
    }
    $wr.Close();
}

$out = "c:\perflogs\output.csv";

trim-perflog c:\perflogs\prod_000005.csv True
trim-perflog c:\perflogs\prod_000006.csv False
trim-perflog c:\perflogs\prod_000007.csv False