#This script requires the SQL Server PowerShell module (https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module)
Import-Module SqlServer

#If the version of PowerShell on the WSUS server is too old, you can install an older copy of the SQL Management Studio that
#includes the SQLPS module. (Or you can potentially upgrade PowerShell on the server). Either way, if that's the case, uncomment
#the following line and comment out the "Import-Module SqlServer" line above.
#Import-Module SQLPS

#Who to send the report from
$Email_From = "SchoolName-$($Env:Computername)@k12.sd.us"

#Where to send the report (can be an array of addresses)
$Email_To =  @("emailaddress1@k12.sd.us","emailaddress2@k12.sd.us")

#Report subject line
$Email_Subject = "WSUS - Needed Update Counts"

#Where to send errors (can be an array of addresses)
$SendErrorsTo = @("emailaddress1@k12.sd.us")

#How many days without reporting in before highlighting
$Days = 7

#SMTP EMAIL Server FQDN
$SMTPServerFQDN = "smtp.sd.gov"

######################################################################
#################### DO NOT EDIT BELOW ###############################
######################################################################

function Send-EmailDataTable {
	<#
		.SYNOPSIS
			Takes a PowerShell array or custom object and sends an email with the data formatted into a nice HTML table. The email message will be responsive on various devices including smartphones.

		.DESCRIPTION
			Takes a PowerShell array or custom object and sends an email with the data formatted into a nice HTML table.

		.PARAMETER  DataObject
			The array or custom object containing the data. If the array contains a "Highlight" column set to $true, that row will be highlighed in yellow.

		.PARAMETER  From
			The sender of the email message. Defaults to "computername@k12.sd.us"
			
		.PARAMETER  To
			The recipient of the email message. Can be an array of addresses.
			
		.PARAMETER  Cc
			The Cc recipient of the email message. Can be an array of addresses.
			
		.PARAMETER  Bcc
			The Bcc recipient of the email message. Can be an array of addresses.
			
		.PARAMETER  Subject
			The subject of the email message.
			
		.PARAMETER  Attachments
			Enter the full path to any attachment you'd like to include in the email message. Can be an array of file paths. Don't forget about message size limits when including attachments.

		.PARAMETER  Note
			Enter any text you'd like to add as a note that appears above the HTML table in the email message. The note will be surrounded by <p></p> tags. If you need line breaks within the note, use <br> tags. You can also use basic HTML formatting syntax.

		.PARAMETER  SmtpServer
			Enter the SMTP server you want to use to deliver the email message. Defaults to "smtp.sd.gov"
			
		.PARAMETER  UseSsl
			Using this switch will send the email message using an SSL connection. The SmtpServer must support SSL messages for this to work.
			
		.PARAMETER  AlternateRowColor
			Using this switch will set every other row color to a light gray. This makes it easier to read the data in long tables.

		.EXAMPLE
			PS C:\> Send-AdvancedHTMLEmail -DataObject $DPMServerSpace -From "$($Env:ComputerName)@k12.sd.us" -To @("john.doe@k12.sd.us","jane.doe@k12.sd.us") -Subject "This is the subject" -SmtpServer smtp.sd.gov -UseSsl -AlternateRowColor
			
			This sends the data from $DPMServerSpace to both John Doe and Jane Doe from <<computername>>@k12.sd.us. The message will be sent via the smtp.sd.gov server using SSL. The rows will alternate colors.

		.EXAMPLE
			PS C:\> Send-AdvancedHTMLEmail -DataObject $ExpiringCerts -From "$($Env:ComputerName)-ExpiringCerts@k12.sd.us" -To "john.doe@k12.sd.us" -Bcc "jane.doe@k12.sd.us" -Subject "This is the subject" -Note "This is a <strong>note</strong>"
			
			This sends the data in $ExpiringCerts to John Doe from <<computername>>-ExpiringCerts@k12.sd.us. Jane Doe will also get Bcc sent the message. There is a note that contains minor HTML formatting syntax. The default SMTP server will be used for sending the email message.

		.INPUTS

		.OUTPUTS

		.NOTES

		.LINK

		.LINK

	#>

[CmdletBinding(
	SupportsShouldProcess=$true,
	PositionalBinding=$false
	)]

param (
	[Parameter(Position=0, Mandatory=$true)]
	[ValidateNotNullOrEmpty()]
	$DataObject,
	
	[Parameter(Position=1)]
	[ValidateScript({$_ -like "*@*"})]
	[String]$From = "$($Env:ComputerName)@k12.sd.us",

	[Parameter(Position=2, Mandatory=$true)]
	[ValidateScript({$_ -like "*@*"})]
	[String[]]$To,

	[Parameter(Position=3)]
	[ValidateScript({$_ -like "*@*"})]
	[String[]]$Cc,
	
	[Parameter(Position=4)]
	[ValidateScript({$_ -like "*@*"})]
	[String[]]$Bcc,
	
	[Parameter(Position=5, Mandatory=$true)]
	[ValidateNotNullOrEmpty()]
	[String]$Subject,
	
	[Parameter(Position=6)]
	[String[]]$Attachments,
	
	[Parameter(Position=7)]
	[String]$Note,
	
	[Parameter(Position=8)]
	[String]$SmtpServer = "smtp.sd.gov",

	[Parameter(Position=9)]
	[Switch]$UseSsl,
	
	[Parameter(Position=10)]
	[Switch]$AlternateRowColor	
)

$NoteProperties = $DataObject | Get-Member -MemberType NoteProperty
$ColumnHeaders = $NoteProperties.Name

# 'Here Strings' need to be aligned all the way to the left
[String]$MsgBody = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>$Subject</title>
<style>
	TABLE {width: 100%; border-spacing: 0; border-collapse: collapse;}
	TR {border-spacing: 0; border-collapse: collapse; vertical-align: top;}
	TD {font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; -webkit-text-size-adjust: 100%; font-size: x-small; text-align: left; border: 1px solid; padding: 3px; border-spacing: 0; border-collapse: collapse;}
	TH {font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; -webkit-text-size-adjust: 100%; font-size: x-small; color: #FFFFFF; text-align: left; border: 1px solid; padding: 3px; border-spacing: 0; border-collapse: collapse; background-color: #0099CC;}
	.Alert {border-spacing: 0; border-collapse: collapse; background-color: #FFEE80; vertical-align: top;}
	.Alternate  {border-spacing: 0; border-collapse: collapse; background-color: #EEEEEE; vertical-align: top;}
</style>
</head><body>
<h3 style="font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; text-align: left;">$Subject</h3>
<p style="font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; font-size: x-small; text-align: left;">$Note</p>
<table>
<tr>

"@

$DataObject[0].PSObject.Properties | ForEach-Object {
if ($PSItem.Name -notlike "*Highlight*")
{
$MsgBody += @"
<th>$($PSItem.Name)</th>

"@
}
}

$MsgBody += @"
</tr>

"@

$i = 0
foreach ($D in $DataObject)
{
if ($D.Highlight -eq $true)
{
$MsgBody += @"
<tr class="Alert">

"@
}
elseif ($AlternateRowColor)
{
if ($i%2 -eq 1)
{
$MsgBody += @"
<tr class="Alternate">

"@
}
}
else
{
$MsgBody += @"
<tr>

"@
}

$DataObject[0].PSObject.Properties | ForEach-Object {
if ($PSItem.Name -notlike "*Highlight*")
{
$MsgBody += @"
<td>$($D.$($PSItem.Name))</td>

"@
}
}

$MsgBody += @"
</tr>

"@

$i++
}

$MsgBody += @"
</table>
</body>
</html>
"@

$MessageProperties = @{
	To = $To;
	From = $From;
	Subject = $Subject;
	SmtpServer = $SmtpServer
}

if ($Cc)
{
	$MessageProperties.Add("Cc",$Cc)
}
if ($Bcc)
{
	$MessageProperties.Add("Bcc",$Bcc)
}
if ($Attachments)
{
	$MessageProperties.Add("Attachments",$Attachments)
}
if ($UseSsl)
{
	$MessageProperties.Add("UseSsl",$UseSsl)
}

Send-MailMessage @MessageProperties -Body $MsgBody -BodyAsHtml

} #END function Send-EmailDataTable

#SQL query to pull the information from the WSUS database
$MissingUpdatesQuery = @"
SELECT tbComputerTarget.FullDomainName, vUpdate.DefaultTitle, vUpdate.CreationDate, tbComputerTarget.LastReportedStatusTime
FROM [SUSDB].[dbo].[tbUpdateStatusPerComputer]
INNER JOIN [SUSDB].[dbo].[tbComputerTarget]
ON tbUpdateStatusPerComputer.TargetID = tbComputerTarget.TargetID
INNER JOIN [SUSDB].[dbo].[tbUpdate]
ON tbUpdateStatusPerComputer.LocalUpdateID = tbUpdate.LocalUpdateID
INNER JOIN [SUSDB].[PUBLIC_VIEWS].[vUpdate]
ON tbUpdate.UpdateID = vUpdate.UpdateId
INNER JOIN [SUSDB].[PUBLIC_VIEWS].[vComputerGroupMembership]
ON tbComputerTarget.ComputerID = vComputerGroupMembership.ComputerTargetId
INNER JOIN [SUSDB].[PUBLIC_VIEWS].[vUpdateApproval]
ON vUpdateApproval.UpdateId = tbUpdate.UpdateID
WHERE (NOT tbUpdateStatusPerComputer.SummarizationState IN ('1','4')) AND (vUpdateApproval.Action = 'Install') AND (vUpdateApproval.ComputerTargetGroupId = vComputerGroupMembership.ComputerTargetGroupId) AND (NOT vUpdate.ClassificationId IN ('E0789628-CE08-4437-BE74-2495B842F43B','3689BDC8-B205-4AF4-8D4A-A63924C5E9D5'))
ORDER BY FullDomainName
"@

#Execute the SQL query to find how many computers are missing required updates
$MissingUpdates = @(Invoke-Sqlcmd -ServerInstance "\\.\pipe\Microsoft##WID\tsql\query" -Database "SUSDB" -Query $MissingUpdatesQuery)

#Count how many missing updates were returned
$MissingTotalCount = $MissingUpdates.Count

#How many days, set by the $Days variable
$DaysWithoutReporting = (Get-Date).AddDays(-$Days)

#Determines if there are either one or more missing updates, if not everything is good and the script won't email unless there are errors
if ($MissingTotalCount -ge 1)
{
    #Initialize a blank array for the entire results
	$TotalMissing = @()
    
	#Group the missing updates by computer name
	$Counts = $MissingUpdates | Group-Object -Property FullDomainName
    
	#Loop through each computer
	foreach ($C in $Counts)
 {
	    #Get the datetime the computer last reported to WSUS and convert to local time
		$LastReportTime = ($C.Group[0].LastReportedStatusTime).ToLocalTime()
	    
		#Make the last report datetime look nice for the report
		$LastReportTimeText = Get-Date $LastReportTime -Format g
		
		#The highlight boolean determines if the row will be colored yellow
		$Highlight = $false

        foreach ($G in $C.Group)
        {
			if ($G.LastReportedStatusTime -lt $DaysWithoutReporting)
            {
                #Sets the highlight boolean to true, this row will be yellow
				$Highlight = $true
            }
        }
        
		#Create the PowerShell custom object with the results for this row
		$ComputerMissing = [pscustomobject]@{
                "Computer" = $C.Name.ToUpper();
                "Needed" = $C.Count;
                "LastReport" = $LastReportTimeText;
                "Highlight" = $Highlight
            }
        
		#Add the custom object to the entire results array
		$TotalMissing += $ComputerMissing
    }
}

#SQL query to pull the total number of computers registered in the WSUS database
$ComputerCount = @"
select ComputerID 
FROM [SUSDB].[dbo].[tbComputerTarget]
"@

#Execute the SQL query for total computer count
$TotalComputerCount = @(Invoke-Sqlcmd -ServerInstance "\\.\pipe\Microsoft##WID\tsql\query" -Database "SUSDB" -Query $ComputerCount)

#Count how many computers are in the WSUS database
$TotalCount = $TotalComputerCount.Count


#Send the email, but only if there are results greater than zero
if ($TotalMissing)
{
	#Sort the results first by highlight, then by computer name
    $TotalMissing = $TotalMissing | Sort-Object -Property @{Expression="Highlight";Descending=$true},Computer
    
	#Splatting properties for the email
	$EmailProperties = @{
		'From' = $Email_From;
		'To' = $Email_To;
		'Subject' = $Email_Subject;
        'Note' = "Total number of computers reporting into the WSUS server: <b> $($TotalCount)</b> <br />Number of computers needing approved updates (details listed below):<b> $($TotalMissing.Count)</b> <br /><em>Rows in yellow indicate the computer is missing updates and has not checked in for more than <b> $($Days)</b> days.</em>"
	}
	
	#Use the Send-EmailDataTable function to send the report
	Send-EmailDataTable -AlternateRowColor -DataObject $TotalMissing @EmailProperties
}

#Email any errors
if ($Error)
{
	Send-MailMessage -Subject "Get-WSUSMissingUpdateCounts: Errors" -Body ($Error | Out-String) -From $Email_From -To $SendErrorsTo -SmtpServer $SMTPServerFQDN
}