PowerShell functions for Grading Operating System of SQL Server

##beta release of functions which are based on best practices used to grade operating system configurations for SQL Server 2008 R2 Enterprise Edition on Windows Server 2008 R2 Enterprise Edition

function Get-GradePowerPlan {
 $Reference = “http://support.microsoft.com/kb/2207548“;
 $Name = “GradePowerPlan”;
 $Worse = [int]0;
 $Better = [int]1;
 $Value = [int](([array](Get-WmiObject -Class “Win32_PowerPlan” -Namespace “root\cimv2\power” | Where-Object {$_.IsActive -eq $true -and $_.ElementName -eq “High performance”})).Count);
 $Grade = if ($Value -ge 1) {100.0} elseif ($Value -eq 0) {70.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradePowerPlan experienced an unexpected error”)’} else {“POWERCFG.EXE -setactive $([string]((POWERCFG.EXE -list | Where-Object{$_ -like ‘*(High performance)’}).Replace(‘(High performance)’, ”).Replace(‘Power Scheme GUID:’, ”).Trim())); Get-GradePowerPlan;”};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeLicenseStatus {
 $Reference = “http://blogs.technet.com/b/jamesone/archive/2009/07/22/how-to-activate-windows-from-a-script-even-remotely.aspx“;
 $Name = “GradeLicenseStatus”;
 $Worse = [int]6;
 $Better = [int]1;
 $Value = ((Get-WmiObject -Class “SoftwareLicensingProduct” | Where-Object {$_.LicenseIsAddon -eq $False -and $_.PartialProductKey -gt “”}).LicenseStatus);
 $Grade = if ($Value -eq 1) {100.0} elseif ($Value -eq 0 -or $Value -gt 1) {50.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradeLicenseStatus experienced an unexpected error”)’} else {‘$ProductKey = “XXXXX-XXXXX-XXXXX-XXXXX-XXXXX”; $SoftwareLicensingService = Get-WmiObject -Class “SoftwareLicensingService”; $SoftwareLicensingService.InstallProductKey($ProductKey) | Out-Null; $SoftwareLicensingService.RefreshLicenseStatus() | Out-Null; Get-GradeLicenseStatus;’};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeDeviceStatus {
 $Reference = “http://msdn.microsoft.com/en-us/library/aa387884.aspx“;
 $Name = “GradeDeviceStatus”;
 $Worse = [int](([array](Get-WmiObject -Class “CIM_LogicalDevice”)).Count);
 $Better = [int]0;
 $Value = [int](([array](Get-WmiObject -Class “CIM_LogicalDevice” | Where-Object {$_.Status -eq “Error”})).Count);
 $Grade = if ($Value -ge 1) {60.0} elseif ($Value -eq 0) {100.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradeDeviceStatus experienced an unexpected error”)’} else {‘throw (“GradeDeviceStatus fix action cannot be scripted”)’};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeHyperThreading {
 $Reference = “http://msdn.microsoft.com/en-us/library/cc615012.aspx“;
 $Name = “GradeHyperThreading”;
 $Worse = [int]1;
 $Better = [int]0;
 $Value = ((Get-WmiObject -Class “Win32_Processor” | Select-Object @{Name=”HyperThreadingProcessors”;Expression={($_.NumberOfLogicalProcessors – $_.NumberOfCores)}}).HyperThreadingProcessors);
 $Grade = if ($Value -ge 1) {70.0} elseif ($Value -eq 0) {100.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradeHyperThreading experienced an unexpected error”)’} else {‘throw (“GradeHyperThreading fix action cannot be scripted”)’};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeScreenSaver {
 $Reference = “http://support.microsoft.com/kb/121282“;
 $Name = “GradeScreenSaver”;
 $Worse = [int]1;
 $Better = [int]0;
 $Value = [int](([array](Get-WmiObject -Class “Win32_Desktop” | Where-Object {$_.ScreenSaverActive -eq $true})).Count);
 $Grade = if ($Value -ge 1) {60.0} elseif ($Value -eq 0) {100.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradeScreenSaver experienced an unexpected error”)’} else {‘Get-ChildItem -Path “Registry::HKEY_USERS” | ForEach-Object {Set-ItemProperty -path “$($_.PSPath)\Control Panel\Desktop” -Name “SCRNSAVE.EXE” -Value “” -ErrorAction “SilentlyContinue”}; Get-GradeScreenSaver;’};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeApplicationBoost {
 $Reference = “http://msdn.microsoft.com/en-us/library/ms176012.aspx“;
 $Name = “GradeApplicationBoost”;
 $Worse = [int]2;
 $Better = [int]0;
 $Value = ((Get-WmiObject -Class “Win32_OperatingSystem”).ForegroundApplicationBoost);
 $Grade = if ($Value -ge 2) {[int]50.0} elseif ($Value -eq 0) {[int]100.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {[string]”} elseif ($Grade -eq $null) {[string]’throw (“GradeApplicationBoost experienced an unexpected error”)’} else {[string]”Set-ItemProperty -Path ‘HKLM:\SYSTEM\CurrentControlSet\Control\PriorityControl’ -Name ‘Win32PrioritySeparation’ -Value $Better; Get-GradeApplicationBoost;”};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradePageFile {
 $Reference = “http://msdn.microsoft.com/en-us/library/ms187877.aspx“;
 $Name = “GradePageFile”;
 $Worse = [int]0;
 $Better = $null;
 $Value = $null;
 $Grade = $null;
 $Fixit = [string]’throw (“GradePageFile experienced an unexpected error”)’;

 If (([int](([array](Get-WmiObject -Class “Win32_PageFileSetting” | Where-Object {$_.InitialSize -gt 0})).Count)) -eq 1)
 {
  $PoolNonpagedBytesMB = [int]((Get-WmiObject -Class “Win32_PerfFormattedData_PerfOS_Memory”).PoolNonpagedBytes / 1MB);
  $PoolPagedBytesMB = [int]((Get-WmiObject -Class “Win32_PerfFormattedData_PerfOS_Memory”).PoolPagedBytes / 1MB);
  $KernelMemoryPoolSizeMB = [int]($PoolNonpagedBytesMB + $PoolPagedBytesMB);

  $TotalVisibleMemorySizeMB = [int]((Get-WmiObject -Class “Win32_OperatingSystem”).TotalVisibleMemorySize * 1KB / 1MB);
  $Better=[int](($TotalVisibleMemorySizeMB * 1.5) + 0.5);

  $Value = [int]((Get-WmiObject “Win32_PageFile”).MaximumSize);

  $Grade = if ($Value -ge $Better) {[double]100.0} elseif ($Value -ge $TotalVisibleMemorySizeMB) {[double]80.0} elseif ($Value -ge $KernelMemoryPoolSizeMB) {[double]60.0} elseif ($Value -ge 0) {[double]50.0} else {$null};
  $Fixit = if ($Grade -eq 100.0) {[string]”} elseif ($Grade -eq $null) {‘throw (“GradePageFile experienced an unexpected error”)’} else {[string]”`$PageFile = Get-WmiObject ‘Win32_PageFile’;`$PageFile.MaximumSize=’$Better’;`$PageFile.Put() | Out-Null; Get-GradePageFile;”};
 }

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

Get-GradePowerPlan; #If ((Get-GradePowerPlan).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradePowerPlan).FixIt};
Get-GradeLicenseStatus; #If ((Get-GradeLicenseStatus).Grade -lt 100.0) {Invoke-Expression -Command ((Get-GradeLicenseStatus).FixIt.Replace(“XXXXX-XXXXX-XXXXX-XXXXX-XXXXX”, “”))};
Get-GradeDeviceStatus; #If ((Get-GradeDeviceStatus).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradeDeviceStatus).FixIt};
Get-GradeHyperThreading; #If ((Get-GradeHyperThreading).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradeHyperThreading).FixIt};
Get-GradeScreenSaver; #If ((Get-GradeScreenSaver).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradeScreenSaver).FixIt};
Get-GradeApplicationBoost; #If ((Get-GradeApplicationBoost).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradeApplicationBoost).FixIt};
Get-GradePageFile; #If ((Get-GradePageFile).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradePageFile).FixIt};

Advertisements

About Robert Matthew Cook

Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at www.sqlmashup.com. For more profile information or to leave a comment, please visit me there.
This entry was posted in Uncategorized. Bookmark the permalink.