Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 3

In the final part of the process will be the process that creates all the reports and zips up the performance counter file on a central server.    The first part copies the PerfMon file from each server to a central location.  After the copy is complete and loops back through each server and creates the PAL reports.  To take advantage of this script you must use the SQLPSX (SQL Server PowerShell Extensions) available on CodePlex.  You will also need the Performance Analysis of Logs (PAL) Tool and 7-Zip installed.

For Part 1, visit this link.  You will need the table create in Part 1.

Part 3: Copy the Perfmon Log to the Central Server and Create PAL Report

# PAL Processing
Get-PSSnapin -Registered | Where-Object { $_.Name -like "SqlServer*" } | ForEach-Object { Write-Host "Adding Snapin " + $_.Name; Add-PSSnapin $_.Name }
clear

# Connect to SQL Server and pull list of servers to monitor
$params = @{'server'='<servername>';'Database'='DBA'} #<----Replace with your server name where you create the tables above
$servers = invoke-sqlcmd @params -Query "SELECT	s.Server_Name ,	Processors , Three_GB_Switch , bit64 , cast(Memory as int) Memory ,	ISNULL(RAID5_Drives, '') RAID5_Drives , ISNULL(RAID1_Drives, '') RAID1_Drives , Location FROM dbo.DBA_Server_PAL_Options o INNER JOIN dbo.DBA_Server s ON o.Server_ID = s.Server_ID ORDER BY Trace_Priority, Server_Name"

$days = -1

$7zip = "D:\Program Files (x86)\7-Zip\7z" #<----Replace with installed path to 7-zip
$PAL = "D:\Program Files\PAL\PAL v2.2"  #<----Replace with your path to where PAL installed
$sharename = "SQL_Trace" #<----Replace with sharename on server
$CentralLocation = "\\ServerName\" + $sharename  #<----Replace with name of your central server

$PALPS = $PAL + "\PAL.ps1"

$filedate = (get-date).AddDays($days).ToString("yyyyMMdd")	# used when retrieving file for each server

$CentralReports = $CentralLocation + "\" + (get-date).AddDays($days).ToString("yyyy") + "\" + (get-date).AddDays($days).ToString("MM") + "\" + (get-date).AddDays($days).ToString("dd")

# Set folders for today's reports
$filedateyyyyMMdd = $CentralLocation + "\" + (get-date).AddDays($days).ToString("yyyy") + "\" + (get-date).AddDays($days).ToString("MM") + "\" + (get-date).AddDays($days).ToString("dd")
$filedateyyyyMM = $CentralLocation + "\" + (get-date).AddDays($days).ToString("yyyy") + "\" + (get-date).AddDays($days).ToString("MM")
$filedateyyyy = $CentralLocation + "\" + (get-date).AddDays($days).ToString("yyyy") 

# Set archive folders for today's Perfmon files
$filedateyyyyMMdda = $CentralLocation + "\Archive\" + (get-date).AddDays($days).ToString("yyyy") + "\" + (get-date).AddDays($days).ToString("MM") + "\" + (get-date).AddDays($days).ToString("dd")
$filedateyyyyMMa = $CentralLocation + "\Archive\" + (get-date).AddDays($days).ToString("yyyy") + "\" + (get-date).AddDays($days).ToString("MM")
$filedateyyyya = $CentralLocation + "\Archive\" + (get-date).AddDays($days).ToString("yyyy") 

# Create report folders and archive folders if needed
if(!(Test-Path -Path $filedateyyyy))
{
	New-Item $filedateyyyy -type directory
	New-Item $filedateyyyyMM -type directory
	New-Item $filedateyyyyMMdd -type directory

	New-Item $filedateyyyya -type directory
	New-Item $filedateyyyyMMa -type directory
	New-Item $filedateyyyyMMdda -type directory
}
elseif(!(Test-Path -Path $filedateyyyyMM))
{
	New-Item $filedateyyyyMM -type directory
	New-Item $filedateyyyyMMdd -type directory

	New-Item $filedateyyyyMMa -type directory
	New-Item $filedateyyyyMMdda -type directory
}
elseif(!(Test-Path -Path $filedateyyyyMMdd))
{
	New-Item $filedateyyyyMMdd -type directory
	New-Item $filedateyyyyMMdda -type directory
}

# Copy file from each server to central location
foreach ($entry in $servers)
{
	$computer = $entry.Server_Name
	$ping = (Test-Connection $computer -Quiet -Count 1)

	if ($ping)
	{
		$file = "\\$computer\$sharename\"

		#Moves files to central lcoation
		$file = "\\$computer\$sharename\" + $computer + "_SQLPAL$filedate.blg"

		if((Test-Path -Path $file))
			{move $file $CentralLocation}
	}
}

# Process each Perfmon File
foreach ($entry in $servers)
{
	$computer = $entry.Server_Name
	$log = $CentralLocation + "\" + $computer + "_SQLPAL$filedate.blg"
	$sqlreport = $computer + "_SQLPAL_ANALYSIS_$filedate.htm"
	$Processors = $entry.Processors
	$Memory = $entry.Memory
	$RAID1_Drives = $entry.RAID1_Drives
	$RAID5_Drives = $entry.RAID5_Drives

	if ($entry.Three_GB_Switch -eq 0)
		{$3GB = $false}
	else
		{$3GB = $true}

	if ($entry.bit64 -eq 0)
		{$64bit = $false}
	else
		{$64bit = $true}

	if ($RAID5_Drives -eq "")
		{$RAID5_Drives = "NULL"}

	if ($RAID1_Drives -eq "")
		{$RAID1_Drives = "NULL"}

	if((Test-Path -Path $log))
	{
		# Process perfmon file
		& $PALPS -Log $log -ThresholdFile $PAL\SQLServer.xml -HtmlOutputFileName $sqlreport -OutputDir $CentralReports -NumberOfProcessors $Processors -ThreeGBSwitch $3GB -SixtyFourBit $64bit -TotalMemory $Memory -RAID5Drives $RAID5_Drives -RAID1Drives $RAID1_Drives

		# Archive perfmon file
		$archive = $filedateyyyyMMdda + "\" + $computer + "_SQLPAL$filedate.7z"
		& $7zip -t7z a $archive $log -mx9

		if((Test-Path -Path $archive))
		{
			# Delete Perfmon file if zip file created successfully
			del $log
		}

		# Close Internet Explorer once done creating reports
		Stop-Process -processname iexplore
	}
}