Relogging Perfmon binary log files to SQL

Lately I’ve been doing quite a bit of work with Performance Monitor (or PerfMon for short) to monitor some performance counters on a collection of servers.

PerfMon has the ability to log it’s counter collections directly to a SQL ODBC data store. The downside of this is that if the store is remote and it is unavailable for some reason, then you’ve lost that data. Another alternative is to log the data to a *.blg (binary) format on the local disk of the machine that you’re monitoring, then post-process it later.

That’s what I’m doing. Every 24 hours I start a new .blg logfile (see screenshot below). Then at a convenient time I copy the directory log files onto another machine for post-processing using robocopy.

One of the very useful tools that comes with Windows that I didn’t know about is “relog”.

Microsoft r Relog.exe (6.0.6000.16386)

Relog creates new performance logs from data in existing performance logs by changing the sampling rate and/or
converting the file format. Supports all performance log formats, including Windows NT 4.0 compressed logs.

Usage:
relog <filename [filename ...]> [options]

Parameters:
  <filename [filename ...]>     Performance file to relog.

Options:
  -?                            Displays context sensitive help.
  -a                            Append output to the existing binary file.
  -c <path [path ...]>          Counters to filter from the input log.
  -cf <filename>                File listing performance counters to filter from the input log. Default is all
                                counters in the original log file.
  -f <CSV|TSV|BIN|SQL>          Output file format.
  -t <value>                    Only write every nth record into the output file. Default is to write every record.
  -o                            Output file path or SQL database.
  -b <M/d/yyyy h:mm:ss[AM|PM]>  Begin time for the first record to write into the output file.
  -e <M/d/yyyy h:mm:ss[AM|PM]>  End time for the last record to write into the output file.
  -config <filename>            Settings file containing command options.
  -q                            List performance counters in the input file.
  -y                            Answer yes to all questions without prompting.

Examples:
  relog logfile.csv -c "\Processor(_Total)\% Processor Time" -o logfile.blg
  relog logfile.blg -cf counters.txt -f bin
  relog logfile.blg -f csv -o logfile.csv -t 2
  relog logfile.blg -q -o counters.txt

Relog gives you the ability to extract out the counters you care about and put them into another file. It also allows you to import the binary logfile format into a SQL store. You have to create a SQL Server ODBC System DSN for this to work.

One of the things relog can do is summarize the data. If you collect performance counters every 1 minute and you want to summarize that data to the hour, use “-t 60” as a parameter.

The command to relog from a *.blg logfile to a SQL database is:

relog logfile.blg -cf CounterList.txt -f SQL -o SQL:PDB!LogFileIdentifierHere

CounterList.txt is a text file that contains a list of counters that you care about, for example:

\\SERVERNAME\LogicalDisk(C:)\Avg. Disk sec/Transfer
\\SERVERNAME\LogicalDisk(C:)\Disk Bytes/sec
\\SERVERNAME\LogicalDisk(C:)\Disk Transfers/sec
\\SERVERNAME\Memory\Pages/sec
\\SERVERNAME\Processor(_Total)\% Processor Time
\\SERVERNAME\System\Processor Queue Length

And here’s my latest one-liner. This will take a directory of *.blg files, check to see if they’ve been relogged into the SQL database and if not, it will relog them. It uses the filename as the key for the log file identifier.

for %a in (*.blg) DO osql -o NUL -b -E -S (LOCAL) -d PDB -Q "EXIT(SELECT COUNT([GUID]) FROM DisplayToID WHERE DisplayString = '%a')" & IF NOT ERRORLEVEL 1 relog %a -cf CounterList.txt -f SQL -o SQL:PDB!%a

It makes use of FOR, osql.exe, ERRORLEVEL and relog. All of which are available from the standard windows command prompt.