Perfmon – put data automatically into database


People have seen I have been working with perfmon lately, collecting data on performance counters on my local machine and servers to get a grip of it all. Until now I have been collecting data into *.BLG files and manually run the a relog command to put these perfmon counter values into my database. I have been working on some routine to make this go automatically using tasks and powershell. The first thing to do, is to tell perfmon what task that shall be run when the data collector set stops, either automatically when the schedule stops it, or when a user is manually stopping it. The screenshot below shows that my “SQLServer” data collector set shall run the task “UpdateBaseline” taking the predefined value “{logs}” as input argument. This is the current file that is generated  since the data collector set is started.

Runtask1

The standard task “UpdateBaseline” will contain an action that looks like the following screenshoot. The program should be a command file that fires with an argument (prameter {logs} from the data collector set. And you can also set the “Start in” directory or “Working folder” that people are calling it.

Runtask3

The Collect.cmd will in my case look like the following command line that will fire  powershell with the script file Collect.ps1 and send in the values from the {logs} parameter from perfmon.

      start powershell 
              -noprofile 
              -NonInteractive 
              -ExecutionPolicy ByPass 
              -Command "& {.\collect.ps1 %1}"

The Collect.ps1 is looking like this:

param([String]$path="")

$startDate = Get-Date;
$basename = gi $path | select BaseName 

<# ----------- Defining logfiles ---------------------- #>
$resLogFile = "C:\temp\relogFiltered\relogresult.log";
$CollectLogFile = "C:\temp\relogFiltered\collect.log";
$targetDir = "C:\temp\relogFiltered\Completed\";

<# ----------- Running relog utility ------------------ #>
$params = " " + $path + " -f SQL -o SQL:<ODBC-Name>!<Server-Name>";

$ps = new-object System.Diagnostics.Process
$ps.StartInfo.Arguments = $params
$ps.StartInfo.Filename = "C:\Windows\System32\relog.exe"
$ps.StartInfo.RedirectStandardOutput = $True
$ps.StartInfo.UseShellExecute = $false
$ps.start()
$ps.WaitForExit()
[string] $Out = $ps.StandardOutput.ReadToEnd();

<# ------------ logging output from relog ------------- #>
Add-Content $resLogFile $Out ;

<# logging summary for relog #>
$endDate = Get-Date;
$elapsedTime = New-TimeSpan $startDate $endDate;
$res = $endDate.ToString() + ": Collect.ps1 - File: " + 
       $basename[0].BaseName + ", Time = " + 
       $elapsedTime.TotalSeconds + " second(s)";

Add-Content $CollectLogFile $res ;

<# moving file to completed directory #>

Copy-Item $path $targetDir;
Advertisements

4 thoughts on “Perfmon – put data automatically into database

  1. Hey, popped across this post when having issues with getting relog to launch via powershell. But I found a bug within your code which is a quick and easy fix. Apparently in .NET if you wait for close before you read from STDOUT it can dead lock the process which will cause it to hang. On larger log files 70+MB I had relog hanging on me but no data was flowing.

    Simple fix is to ReadToEnd before you WaitForExit!

    Source: http://stackoverflow.com/questions/139593/processstartinfo-hanging-on-waitforexit-why

    • Thanks…I seldom user that many counters in my colleector sets. I have specific sets for specific problem solving. Normally, I get .blg files less than 10MB pr day.

      • Ah ok, thats fair. We do heavy automated perf monitoring on our applications and it requires collecting many counters. Thanks for the post, helped get my pointed in the right direction!

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