I am trying to create a script to look at a list of servers from a .txt doc and find the SQL instances that are on that server as well as the sql service pack version build for each instance. The script runs but returns no info. I think I am having trouble with the $svcPack variable and the corresponding variables that find the data needed. Any help would be greatly appriciated. Thanks.
# Continue even if there are errors
$ErrorActionPreference = "Continue";
# load the SQL SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
# Sets the server versions
$vs2005sp4 = "9.00.5000";
$vs2008sp3 = "10.00.5500.0";
$vs2008r2sp2 = "10.50.4000.0";
# EMAIL PROPERTIES
# Set the recipients of the report.
$users = "test@test.com"
#$users = "You@company.com" # I use this for testing by uing my email address.
#$users = "you@company.com", "manager@company.com", "etc@company.com"; # can be sent to individuals.
# REPORT PROPERTIES
# Path to the report
$reportPath = "c:\Scripts\Reports\";
# Report name
$reportName = "ServicePackRpt_$(get-date -format ddMMyyyy).html";
# Path and Report name together
$servicePackReport = $reportPath + $reportName
#Set colors for table cell backgrounds
$redColor = "#FF0000"
$greenColor = "#34F01F"
$yellowColor = "#F0EC22"
$orangeColor = "#F2991D"
$whiteColor = "#FFFFFF"
# Count if any computers have low disk space. Do not send report if less than 1.
$i = 0;
# Get computer list to check disk space
$servers = Get-Content "c:\Scripts\InPutFiles\servers.txt";
$datetime = Get-Date -Format "MM-dd-yyyy_HHmmss";
# Remove the report if it has already been run today so it does not append to the existing report
If (Test-Path $servicePackReport)
{
Remove-Item $servicePackReport
}
# Cleanup old files..
$Daysback = "-7"
$CurrentDate = Get-Date;
$DateToDelete = $CurrentDate.AddDays($Daysback);
Get-ChildItem $reportPath | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item;
# Create and write HTML Header of report
$titleDate = get-date -uformat "%m-%d-%Y - %A"
$header = "
<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>
<title>Service Pack Report</title>
<STYLE TYPE='text/css'>
<!--
td {
font-family: Tahoma;
font-size: 11px;
border-top: 1px solid #999999;
border-right: 1px solid #999999;
border-bottom: 1px solid #999999;
border-left: 1px solid #999999;
padding-top: 0px;
padding-right: 0px;
padding-bottom: 0px;
padding-left: 0px;
}
body {
margin-left: 5px;
margin-top: 5px;
margin-right: 0px;
margin-bottom: 10px;
table {
border: thin solid #000000;
}
-->
</style>
</head>
<body>
<table width='100%'>
<tr bgcolor='#CCCCCC'>
<td colspan='7' height='25' align='center'>
<font face='tahoma' color='#003399' size='4'><strong>DTG Environment Service Pack Report for $titledate</strong></font>
</td>
</tr>
</table>
"
Add-Content $servicePackReport $header
# Create and write Table header for report
$tableHeader = "
<table width='100%'><tbody>
<tr bgcolor=#CCCCCC>
<td width='10%' align='center'>Server</td>
<td width='15%' align='center'>Instance</td>
<td width='5%' align='center'>Version Build</td>
<td width='10%' align='center'>Version Name</td>
</tr>
"
Add-Content $servicePackReport $tableHeader
# Start processing disk space reports against a list of servers
foreach($computer in $servers)
{
#$svcPacks = Get-WmiObject -ComputerName $computer -Class win32_volume | Where-object {$_.label -ne $null} | Sort-Object -property "name"
#$svcPacks = Get-WmiObject -ComputerName $computer -Class "__NAMESPACE" -namespace "root\Microsoft\SqlServer\ReportServer"
$svcPacks = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server($computer)
$computer = $computer.toupper()
foreach($packs in $svcPacks)
{
#$instanceID = $packs.Instance;
$versionBuild = $packs.VersionString;
$versionName = $packs.ProductLevel;
$color = $redColor;
# Set background color to green if service pack is 2008r2 SP2
if($versionBuild -eq $vs2008r2sp2)
{
$color = $greenColor
# Set background color to yellow if service pack is 2008 SP3
if($versionBuild -eq $vs2008sp3)
{
$color = $yellowColor
# Set background color to orange if service pack is 2005 SP4
if($versionBuild -eq $vs2005sp4)
{
$color = $orangeColor
# Create table data rows
#<td width='15%' align='center'>$instanceID</td>
$dataRow = "
<tr>
<td width='10%'>$computer</td>
<td width='5%' align='center'>$versionBuild</td>
<td width='10%' align='center'>$versionName</td>
</tr>
"
# If statement needed to remove label that were null
If ($versionID -ne 'null')
{
Add-Content $servicePackReport $dataRow;
Write-Host -ForegroundColor DarkYellow "$computer $deviceID service pack build = $versionBuild";
$i++
}
}
}
}
}
}
# Create table at end of report showing legend of colors for the critical and warning
$tableDescription = "
</table><br><table width='20%'>
<tr bgcolor='White'>
<td width='10%' align='center' bgcolor='#34F01F'>SQL Server 2008 R2 with SP2 - " + $vs2008r2sp2 +"</td>
<td width='10%' align='center' bgcolor='#F0EC22'>SQL Server 2008 with SP3 - " + $vs2008sp3 +"</td>
<td width='10%' align='center' bgcolor='#F2991D'>SQL Server 2005 with SP4 - " + $vs2005sp4 +"</td>
</tr>
"
Add-Content $servicePackReport $tableDescription
Add-Content $servicePackReport "</body></html>"
# Send Notification if alert $i is greater then 0
if ($i -gt 0)
{
foreach ($user in $users)
{
Write-Host "Sending Email notification to $user"
$smtpServer = "test.com"
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$msg = New-Object Net.Mail.MailMessage
$msg.To.Add($user)
$msg.From = "NoReply@test.com"
$msg.Subject = "Environment Service Pack Report for $titledate"
$msg.IsBodyHTML = $true
$msg.Body = get-content $servicePackReport
$smtp.Send($msg)
$body = ""
}
}
