Overview

I developed these PowerShell scripts to generate a daily email report of open tickets within the Software Desk Express software database. The scripts get the content via SQL queries against your SDE SQL database and are piped into my email reporting functions.

At the very bottom of the email a section which tallies up the selected and the other teams total open tickets and sorts them by which team has the most open. To make it a little more friendly to read, I created an array of the “SDE Group” name and the “Team” name which I somewhat made a few guesses on. If the team name is wrong somewhere let me know and I can easily change it.

Basic Functionality

Although the report may look complicated, at its core, each function is simply generating an array of data based off various SQL queries to the database.

There are two types of reports we can invoke. Either one for a specific team (where an example is attached below):

Import-Module "\\scriptserver\Scripts\Invoke-DMGSDEReport\Invoke-DMGSDEReport.psm1" -Force
#Generate The Team Report
$emailaddresses = @("ateam@emailaddress.com")
Invoke-DMGSDEReport -GroupName SDEGROUP2 -GroupDescription "Server Inf. Team" -emailaddresses $emailaddresses

Or a report that combines the functions in a useful way to provide a summary report that would be useful to management:

Import-Module "\\scriptserver\Scripts\Invoke-DMGSDEReport\Invoke-DMGSDEReport.psm1" -Force
#Generate The Manager Report
$emailaddresses = @("managers@emailaddress.com")
Invoke-DMGSDEReport -ManagerReport MANAGERGROUP1 -emailaddresses $emailaddresses

The arrays are combined into a single array of arrays and then passed onto my HTML E-Mail function which is discussed elsewhere on this site:

New-DMGEmailReport `
            -Arrays $OutputArrays `
            -ReportTitle "$GroupDescription Open Ticket Report" `
            -from "SDEReports@emailadress.com" `
            -To $emailaddresses `
            -subject "$GroupDescription SDE Open Ticket Report"

Sample Report

PowerShell Invocation: Invoke-DGMSDEServerReport.ps1

Import-Module "\\SCRIPTSERVER\scripts\New-DMGEmailReport\New-DMGEmailReport.psm1" -Force

Import-Module "\\SCRIPTSERVER\Scripts\Invoke-DMGSDEReport\New-DMGSDEOpenIncidents.psm1" -Force
Import-Module "\\SCRIPTSERVER\Scripts\Invoke-DMGSDEReport\New-DMGSDEOpenWorkOrders.psm1" -Force
Import-Module "\\SCRIPTSERVER\Scripts\Invoke-DMGSDEReport\New-DMGSDETeamsOpenHistory.psm1" -Force
Import-Module "\\SCRIPTSERVER\Scripts\Invoke-DMGSDEReport\New-DMGSDETeamTicketHistory2.psm1" -Force
Import-Module "\\SCRIPTSERVER\Scripts\Invoke-DMGSDEReport\New-DMGSDETeamUserHistory.psm1" -Force
Import-Module "\\SCRIPTSERVER\Scripts\Invoke-DMGSDEReport\New-DMGSDEAreasOfConcern.psm1" -Force

<#
.SYNOPSIS
  Generates an SDE Email Report
.NOTES
  Version:        1.0
  Author:         David Maiolo
  Creation Date:  2018-01-22
  Purpose/Change: Initial script development

#>

#---------------------------------------------------------[Initialisations]--------------------------------------------------------

function Invoke-DMGSDEReport{
    param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='TeamReport')]
        [ValidateSet("SDEGROUP1","SDEGROUP2","SDEGROUP3","SDEGROUP4","SDEGROUP5","SDEGROUP6","SDEGROUP7","SDEGROUP8","SDEGROUP9","SDEGROUP10")]
        [String]$GroupName,
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='TeamReport')]
        [String]$GroupDescription,
        [Parameter(Position=4,Mandatory=$false,ValueFromPipeline=$true,ParameterSetName='TeamReport')]
        [ValidateSet("Black","White","Red","Lime","Blue","Yellow","Cyan","Magenta","Silver","Gray","Maroon","Olive","Green","Purple","Teal","Navy")]
        [String]$Color,
        [ValidateSet("ManagerGroup1","ManagerGroup2","ManagerGroup3","ManagerGroup4","ManagerGroup5","ManagerGroup6")]
        [Parameter(Position=2,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='ManagerReport')]
        [String]$ManagerReport,
        [Parameter(Position=3,Mandatory=$true,ValueFromPipeline=$true)]
        $emailaddresses
    )
    
    $OutPutArrays = @()

    if([bool]($MyInvocation.BoundParameters.Keys -contains 'ManagerReport')){

        #A Manager Report

        $colors = @("Lime","Blue","Cyan","Magenta","Gray","Maroon","Olive","Purple","Teal","Navy")
        $teams = @()

         switch ($ManagerReport) {
           "ManagerGroup1" {
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP1'; 'Description' = 'IT Governance'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP2'; 'Description' = 'DS User Request'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP3'; 'Description' = 'Directory Services Administrators'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP4'; 'Description' = 'Move / Add / Change'};
           }
           "ManagerGroup2" {
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP1'; 'Description' = 'Tech-Ops Channels'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP2'; 'Description' = 'Tech-Ops Core & IPS'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP3'; 'Description' = 'Tech-Ops Lending Credit'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP4'; 'Description' = 'Tech-IT Platform Managers'};
           }
           "ManagerGroup3" {
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP1'; 'Description' = 'Database Administrators'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP2'; 'Description' = 'Data Warehouse'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP3'; 'Description' = 'EDW Administration'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP4'; 'Description' = 'IBM Administration'};
           }
           "ManagerGroup4" {
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP1'; 'Description' = 'Enterprise App'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP2'; 'Description' = 'Enterprise Development'};
           }
           "ManagerGroup5" {
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP1'; 'Description' = 'Network Infr. Team'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP2'; 'Description' = 'Server Infrastructure Team'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP3'; 'Description' = 'Endpoint Admins Team'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP4'; 'Description' = 'Technology Help Desk'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP5'; 'Description' = 'Telecom Team'}
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP6'; 'Description' = 'Network - General'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP7'; 'Description' = 'Network - Imaging'};
           }
           "ManagerGroup6" {
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP1'; 'Description' = 'Branch Help Desk'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP2'; 'Description' = 'Corporate Security'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP3'; 'Description' = 'Information Security'};
            $teams += [PSCustomObject] @{'Group' = 'SDEGROUP4'; 'Description' = 'Xerox'};
           }
           default {}
        }

        $OutputArrays+= New-DMGSDETeamsOpenHistory -ShowAllTeams -Color "Blue"

        foreach ($team in $teams){
            #Create a New Random Color For Each Team
            $LastColor = $Color
            do{$Color = $Colors[(Get-Random -Maximum ([array]$Colors).count)]} while ($Color -eq $LastColor)

            #The reports for each team of the manager report
            $OutputArrays+= New-DMGSDETeamUserHistory -GroupName $team.Group -GroupDescription $team.Description -Color $Color
            $OutputArrays+= New-DMGSDEAreasOfConcern -GroupName $team.Group -GroupDescription $team.Description -MaximumNumberOfRowsToDisplay 5 -DaysUntilConcern 45 -Color $Color
            $OutputArrays+= New-DMGSDETeamTicketHistory2 -GroupName $team.Group -GroupDescription $team.Description -NumOfMonthsToShowTrends 8 -Color $Color
        }

        #Email Manager Report
        New-DMGEmailReport `
            -Arrays $OutputArrays `
            -ReportTitle "SDE Manager Report`: $ManagerReport" `
            -from "SDEReports@DMGhawaii.com" `
            -To $emailaddresses `
            -subject "SDE Manager Report`: $ManagerReport"
    }
    else{
        #A Team Report

        if([bool]($MyInvocation.BoundParameters.Keys -contains 'Color')){
            $DMGSDEAreasOfConcern = New-DMGSDEAreasOfConcern -GroupName $GroupName -GroupDescription $GroupDescription -MaximumNumberOfRowsToDisplay 5 -DaysUntilConcern 60 -Color $Color
            $DMGSDEOpenIncidents = New-DMGSDEOpenIncidents -GroupName $GroupName -GroupDescription $GroupDescription -MaximumNumberOfRowsToDisplay 50 -Color $Color
            $DMGSDEOpenWorkOrders = New-DMGSDEOpenWorkOrders -GroupName $GroupName -GroupDescription $GroupDescription -MaximumNumberOfRowsToDisplay 50 -Color $Color
            $DMGSDETeamsOpenHistory = New-DMGSDETeamsOpenHistory -ShowAllTeams -Color $Color
            $DMGSDETeamTicketHistory2 = New-DMGSDETeamTicketHistory2 -GroupName $GroupName -GroupDescription $GroupDescription -NumOfMonthsToShowTrends 12 -Color $Color
            $DMGSDETeamUserHistory = New-DMGSDETeamUserHistory -GroupName $GroupName -GroupDescription $GroupDescription -Color $Color
        }
        else{
            $DMGSDEAreasOfConcern = New-DMGSDEAreasOfConcern -GroupName $GroupName -GroupDescription $GroupDescription -MaximumNumberOfRowsToDisplay 5 -DaysUntilConcern 60
            $DMGSDEOpenIncidents = New-DMGSDEOpenIncidents -GroupName $GroupName -GroupDescription $GroupDescription -MaximumNumberOfRowsToDisplay 50
            $DMGSDEOpenWorkOrders = New-DMGSDEOpenWorkOrders -GroupName $GroupName -GroupDescription $GroupDescription -MaximumNumberOfRowsToDisplay 50
            $DMGSDETeamsOpenHistory = New-DMGSDETeamsOpenHistory -ShowAllTeams
            $DMGSDETeamTicketHistory2 = New-DMGSDETeamTicketHistory2 -GroupName $GroupName -GroupDescription $GroupDescription -NumOfMonthsToShowTrends 12
            $DMGSDETeamUserHistory = New-DMGSDETeamUserHistory -GroupName $GroupName -GroupDescription $GroupDescription
        }

        $OutputArrays+=$DMGSDEAreasOfConcern
        $OutputArrays+=$DMGSDEOpenIncidents
        $OutputArrays+=$DMGSDEOpenWorkOrders
        $OutputArrays+=$DMGSDETeamsOpenHistory
        $OutputArrays+=$DMGSDETeamTicketHistory2
        $OutputArrays+=$DMGSDETeamUserHistory


        if (($DMGSDEOpenIncidents -ne $null) -or ($DMGSDEOpenWorkOrders -ne $null)){
        #Email Multiple Arrays
        New-DMGEmailReport `
            -Arrays $OutputArrays `
            -ReportTitle "$GroupDescription Open Ticket Report" `
            -from "SDEReports@emailadress.com" `
            -To $emailaddresses `
            -subject "$GroupDescription SDE Open Ticket Report"
        }else{
            Write-Host "There were no open incidents or work orders, so the email was not sent."
        }
   }

    
}

PowerShell Module: New-DGMSDEAreasOfConcern.psm1

<#
.SYNOPSIS
  Generates New-DMGSDEOpenIncidents
.NOTES
  Version:        1.0
  Author:         David Maiolo
  Creation Date:  2018-01-22
  Purpose/Change: Initial script development

#>

#---------------------------------------------------------[Initialisations]--------------------------------------------------------

function New-DMGSDEAreasOfConcern{
param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)]
        [ValidateSet("SDEGROUP1","SDEGROUP2","SDEGROUP3","SDEGROUP4","SDEGROUP5","SDEGROUP6","SDEGROUP7","SDEGROUP8","SDEGROUP9","SDEGROUP10")]
        [String]$GroupName,
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)]
        [String]$GroupDescription,
        [Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$true)]
        [Int]$MaximumNumberOfRowsToDisplay = 5,
        [Parameter(Position=4,Mandatory=$false,ValueFromPipeline=$true)]
        [Int]$DaysUntilConcern = 30,
        [Parameter(Position=5,Mandatory=$false,ValueFromPipeline=$true)]
        [ValidateSet("Black","White","Red","Lime","Blue","Yellow","Cyan","Magenta","Silver","Gray","Maroon","Olive","Green","Purple","Teal","Navy")]
        [String]$Color,
        [Parameter(Position=6,Mandatory=$false,ValueFromPipeline=$true)]
        [String]$ServerInstance = "SDESQLSERVER"
    )
    
    $OutputArrays =@()
    $sqlquery = $null
    $Array = $null

    $Currentyear = get-date -Format yyyy
    $Currentdate = get-date -Format MM/dd
    $Lastyear = ($Currentyear-1)
    $DateOfConcern=(Get-Date).AddDays(-($DaysUntilConcern)).ToString('yyyy/MM/dd')

    #========================================================================================
    #SQL Query To Find Open Work Orders
    #========================================================================================
    $sqlquery = "SELECT [Incident #],[First Name],[Last Name],[Login ID Assigned To],[Open Date & Time],[Incident Description] ,[LastModified]
                  FROM [SDE_PROD].[_SMDBA_].[Incident]
                  WHERE ([Status ID:] != 'Closed' AND [Status ID:] != 'ADMIN_CLOSE' AND [Status ID:] != 'CANCEL' AND [Status ID:] != 'REJECT' AND [Open Date & Time] <= '$DateOfConcern' AND [Group Name] = '$GroupName')
                  ORDER BY [Open Date & Time] ASC"

    Write-Host $sqlquery

    #Generate Incident Concern Count
    $IncidentConcern = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance  | `
                        Select `
                        @{L='Concern #';E={$_."Incident #"}},`
                        @{L='Customer';E={$_."First Name"+" "+$_."Last Name"}},`
                        @{L='Assigned To';E={$_."Login ID Assigned To"}},
                        @{L='Days Old';E={-((NEW-TIMESPAN –Start (GET-DATE) –End ([datetime]$_."Open Date & Time")).Days)}},`
                        @{L='Brief Description';E={($_."Incident Description").SubString(0,80)}},`
                        @{L='Days Ago Last Modified';E={-((NEW-TIMESPAN –Start (GET-DATE) –End ([datetime]$_."LastModified")).Days)}}
    [int]$IncidentConcernCount = $IncidentConcern.Count

    $sqlquery = "SELECT [Work Order #],[First Name],[Last Name],[Login ID Assigned To],[Open Date & Time],[Incident Description] ,[LastModified]
                  FROM [SDE_PROD].[_SMDBA_].[Work Orders]
                  WHERE ([Status ID:] != 'Closed' AND [Status ID:] != 'ADMIN_CLOSE' AND [Status ID:] != 'CANCEL' AND [Status ID:] != 'REJECT' AND [Open Date & Time] <= '$DateOfConcern' AND [Group Name] = '$GroupName')
                  ORDER BY [Open Date & Time] ASC"

    Write-Host $sqlquery

    #Generate Work Order Concern Count
    $WorkOrderConcern = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance  | `
                        Select `
                        @{L='Concern #';E={$_."Work Order #"}},`
                        @{L='Customer';E={$_."First Name"+" "+$_."Last Name"}},`
                        @{L='Assigned To';E={$_."Login ID Assigned To"}},`
                        @{L='Days Old';E={-((NEW-TIMESPAN –Start (GET-DATE) –End ([datetime]$_."Open Date & Time")).Days)}},`
                        @{L='Brief Description';E={($_."Incident Description").SubString(0,80)}},`
                        @{L='Days Ago Last Modified';E={-((NEW-TIMESPAN –Start (GET-DATE) –End ([datetime]$_."LastModified")).Days)}}
    [int]$WorkOrderConcernCount = $WorkOrderConcern.Count

    $AllConcerns = @()
    $AllConcerns += $IncidentConcern
    $AllConcerns += $WorkOrderConcern

    $AllConcernCount = $IncidentConcernCount+$WorkOrderConcernCount

    if([bool]($MyInvocation.BoundParameters.Keys -contains 'Color')){
        if ($AllConcernCount -le 2){$Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. You should take care of these soon, but good work keeping this number low. If you solve all of these, this section will be removed."}
        elseif ($AllConcernCount -le 5){$Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. All though this number is low, ideally it should be zero."}
        elseif ($AllConcernCount -le 10){$Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. The number of older concerns is becoming critical."}
        elseif ($AllConcernCount -le 15){$Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. The number of older concerns is becoming increasingly critical."}
        elseif ($AllConcernCount -le 20){$Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. The amount of concerns is almost critital and should be addressed as a top priority."}
        else{$Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. This is a critical and should be addressed as a priority."}
    }else{

        if ($AllConcernCount -le 2){$Color = "Green"; $Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. You should take care of these soon, but good work keeping this number low. If this number goes above two, we will display this as yellow. If you solve all of these, this section will be removed."}
        elseif ($AllConcernCount -le 5){$Color = "Yellow"; $Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. All though this number is low, ideally it should be zero. Once you get this down to two, we will show this area as green."}
        elseif ($AllConcernCount -le 10){$Color = "Yellow"; $Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. The number of older concerns is becoming critical. Once you get this down to two, we will show this area as green."}
        elseif ($AllConcernCount -le 15){$Color = "Red"; $Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. The number of older concerns is becoming increasingly critical. Once you get this down to ten, we will show this area as yellow."}
        elseif ($AllConcernCount -le 20){$Color = "Red"; $Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. The amount of concerns is almost critital and should be addressed as a top priority. Once you get this down to ten, we will show this area as yellow."}
        else{$Color = "Red"; $Message = "There are $IncidentConcernCount Incidents and $WorkOrderConcernCount Work Orders that have been open at least $DaysUntilConcern days. This is a critical and should be addressed as a priority. Once you get this down to ten, we will show this area as yellow."}
    }

    if ($AllConcernCount -gt $MaximumNumberOfRowsToDisplay){
        $Title = "$GroupDescription`: At Least $DaysUntilConcern days old (only showing top $MaximumNumberOfRowsToDisplay)";
    }else{
        $Title = "$GroupDescription`: At Least $DaysUntilConcern days old";
    }

    #Array2
    $output = [PSCustomObject] @{
    'Message' = $Message;
    'Title' = $Title;
    'Color' = $Color;
    'Array' = $AllConcerns | Sort-Object -Property "Days Old" -Descending | Select-Object -First $MaximumNumberOfRowsToDisplay;
    }
    if ($output.Array -ne $NULL){return $output}
}

PowerShell Module: New-DGMSDEOpenIncidents.psm1

<#
.SYNOPSIS
  Generates New-DMGSDEOpenIncidents
.NOTES
  Version:        1.0
  Author:         David Maiolo
  Creation Date:  2018-01-22
  Purpose/Change: Initial script development

#>

#---------------------------------------------------------[Initialisations]--------------------------------------------------------

function New-DMGSDEOpenIncidents{
param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)]
        [ValidateSet("SDEGROUP1","SDEGROUP2","SDEGROUP3","SDEGROUP4","SDEGROUP5","SDEGROUP6","SDEGROUP7","SDEGROUP8","SDEGROUP9","SDEGROUP10")]
        [String]$GroupName,
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)]
        [String]$GroupDescription,
        [Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$true)]
        $MaximumNumberOfRowsToDisplay = 50,
        [Parameter(Position=4,Mandatory=$false,ValueFromPipeline=$true)]
        [ValidateSet("Black","White","Red","Lime","Blue","Yellow","Cyan","Magenta","Silver","Gray","Maroon","Olive","Green","Purple","Teal","Navy")]
        [String]$Color = "Blue",
        [Parameter(Position=5,Mandatory=$false,ValueFromPipeline=$true)]
        $ServerInstance = "SDESQLSERVER"
    )
    
    $OutputArrays =@()
    $sqlquery = $null
    $Array = $null

    #========================================================================================
    #SQL Query To Find Open Incidents
    #========================================================================================
    $sqlquery = "SELECT [Incident #],[First Name],[Last Name],[Login ID Assigned To],[Open Date & Time],[Incident Description]
      FROM [SDE_PROD].[_SMDBA_].[Incident]
      WHERE ([Status ID:] != 'Closed' AND [Status ID:] != 'ADMIN_CLOSE' AND [Status ID:] != 'CANCEL' AND [Status ID:] != 'REJECT' AND [Group Name] = '$GroupName')
      ORDER BY [Incident #] DESC"

    #Generate different messages and colors based on the number open
    $Array = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance  | Select `
        "Incident #",`
        @{L='Customer';E={$_."First Name"+" "+$_."Last Name"}},`
        @{L='Assigned To';E={$_."Login ID Assigned To"}},
        @{L='Opened Date';E={($_."Open Date & Time").ToString("MM/dd/yyyy")}},
        "Incident Description"
    [int]$IncidentCount = $Array.Count


    if([bool]($MyInvocation.BoundParameters.Keys -contains 'Color')){
        if ($IncidentCount -le 19){$Message = "These are the $IncidentCount open incidents currently assigned to your group. This small number opened is considered excellent!"}
        elseif ($IncidentCount -le 30){$Message = "These are the $IncidentCount open incidents currently assigned to your group."}
        elseif ($IncidentCount -le 60){$Message = "These are the $IncidentCount open incidents currently assigned to your group. The number of open incidents is getting somewhat critical."}
        elseif ($IncidentCount -le 99){$Message = "These are the $IncidentCount open incidents currently assigned to your group. The number of open incidents is getting critical."}
        elseif ($IncidentCount -le 199){$Message = "These are the $IncidentCount open incidents currently assigned to your group. With over 100 open incidents, the number is critical!"}
        else{$Message = "These are the $IncidentCount open incidents currently assigned to your group. At over 200 tickets, the number of open incidents is severe!"}
    }
    else{
        if ($IncidentCount -le 19){$Color = "Green"; $Message = "These are the $IncidentCount open incidents currently assigned to your group. This small number opened is considered excellent!"}
        elseif ($IncidentCount -le 30){$Color = "Green"; $Message = "These are the $IncidentCount open incidents currently assigned to your group."}
        elseif ($IncidentCount -le 60){$Color = "Yellow"; $Message = "These are the $IncidentCount open incidents currently assigned to your group. The number of open incidents is getting somewhat critical."}
        elseif ($IncidentCount -le 99){$Color = "Red"; $Message = "These are the $IncidentCount open incidents currently assigned to your group. The number of open incidents is getting critical."}
        elseif ($IncidentCount -le 199){$Color = "Red"; $Message = "These are the $IncidentCount open incidents currently assigned to your group. With over 100 open incidents, the number is critical!"}
        else{$Color = "Red"; $Message = "These are the $IncidentCount open incidents currently assigned to your group. At over 200 tickets, the number of open incidents is severe!"}
    }

    if ($Array.Count -gt $MaximumNumberOfRowsToDisplay){
        $Title = "$GroupDescription`: $IncidentCount Open Incidents (only showing top $MaximumNumberOfRowsToDisplay)";
    }else{
        $Title = "$GroupDescription`: $IncidentCount Open Incidents";
    }

    #Array1
    $output = [PSCustomObject] @{
    'Message' = $Message;
    'Title' = $Title;
    'Color' = $Color;
    'Array' = $Array | Select-Object -First $MaximumNumberOfRowsToDisplay;
    }
    if ($output.Array -ne $NULL){return $output}
}

PowerShell Module: New-DGMSDEOpenWorkOrders.psm1

<#
.SYNOPSIS
  Generates New-DMGSDEOpenIncidents
.NOTES
  Version:        1.0
  Author:         David Maiolo
  Creation Date:  2018-01-22
  Purpose/Change: Initial script development

#>

#---------------------------------------------------------[Initialisations]--------------------------------------------------------

function New-DMGSDEOpenWorkOrders{
param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)]
        [ValidateSet("SDEGROUP1","SDEGROUP2","SDEGROUP3","SDEGROUP4","SDEGROUP5","SDEGROUP6","SDEGROUP7","SDEGROUP8","SDEGROUP9","SDEGROUP10")]
        [String]$GroupName,
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)]
        [String]$GroupDescription,
        [Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$true)]
        $MaximumNumberOfRowsToDisplay = 50,
        [Parameter(Position=4,Mandatory=$false,ValueFromPipeline=$true)]
        [ValidateSet("Black","White","Red","Lime","Blue","Yellow","Cyan","Magenta","Silver","Gray","Maroon","Olive","Green","Purple","Teal","Navy")]
        [String]$Color = "Blue",
        [Parameter(Position=5,Mandatory=$false,ValueFromPipeline=$true)]
        $ServerInstance = "SDESQLSERVER"
    )
    
    $OutputArrays =@()
    $sqlquery = $null
    $Array = $null

    #========================================================================================
    #SQL Query To Find Open Work Orders
    #========================================================================================
    $sqlquery = "SELECT [Work Order #],[First Name],[Last Name],[Login ID Assigned To],[Open Date & Time],[Incident Description]
      FROM [SDE_PROD].[_SMDBA_].[Work Orders]
      WHERE ([Status ID:] != 'Closed' AND [Status ID:] != 'ADMIN_CLOSE' AND [Status ID:] != 'CANCEL' AND [Status ID:] != 'REJECT' AND [Group Name] = '$GroupName')
      ORDER BY [Work Order #] DESC"

    #Generate different messages and colors based on the number open
    $Array = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance  | Select `
        "Work Order #",`
        @{L='Customer';E={$_."First Name"+" "+$_."Last Name"}},`
        @{L='Assigned To';E={$_."Login ID Assigned To"}},
        @{L='Opened Date';E={($_."Open Date & Time").ToString("MM/dd/yyyy")}},
        "Incident Description"
    [int]$WorkOrderCount = $Array.Count

    if([bool]($MyInvocation.BoundParameters.Keys -contains 'Color')){
        if ($WorkOrderCount -le 19){$Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. This small number opened is considered excellent!"}
        elseif ($WorkOrderCount -le 30){$Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group."}
        elseif ($WorkOrderCount -le 60){$Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. The number of open Work Orders is getting somewhat critical."}
        elseif ($WorkOrderCount -le 99){$Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. The number of open Work Orders is getting critical."}
        elseif ($WorkOrderCount -le 199){$Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. With over 100 open Work Orders, the number is critical!"}
        else{$Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. At over 200 tickets, the number of open Work Orders is severe!"}
    }
    else{
        if ($WorkOrderCount -le 19){$Color = "Green"; $Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. This small number opened is considered excellent!"}
        elseif ($WorkOrderCount -le 30){$Color = "Green"; $Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group."}
        elseif ($WorkOrderCount -le 60){$Color = "Yellow"; $Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. The number of open Work Orders is getting somewhat critical."}
        elseif ($WorkOrderCount -le 99){$Color = "Red"; $Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. The number of open Work Orders is getting critical."}
        elseif ($WorkOrderCount -le 199){$Color = "Red"; $Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. With over 100 open Work Orders, the number is critical!"}
        else{$Color = "Red"; $Message = "These are the $WorkOrderCount open Work Orders currently assigned to your group. At over 200 tickets, the number of open Work Orders is severe!"}
    }

    if ($Array.Count -gt $MaximumNumberOfRowsToDisplay){
        $Title = "$GroupDescription`: $WorkOrderCount Open Work Orders (only showing top $MaximumNumberOfRowsToDisplay)";
    }else{
        $Title = "$GroupDescription`: $WorkOrderCount Open Work Orders";
    }

    #Array2
    $output = [PSCustomObject] @{
    'Message' = $Message;
    'Title' = $Title;
    'Color' = $Color;
    'Array' = $Array | Select-Object -First $MaximumNumberOfRowsToDisplay;
    }
    if ($output.Array -ne $NULL){return $output}
}

PowerShell Module: New-DGMSDETeamsOpenHistory.psm1

<#
.SYNOPSIS
  Generates New-DMGSDEOpenIncidents
.NOTES
  Version:        1.0
  Author:         David Maiolo
  Creation Date:  2018-01-22
  Purpose/Change: Initial script development

#>

#---------------------------------------------------------[Initialisations]--------------------------------------------------------

function New-DMGSDETeamsOpenHistory{
param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='Single Team')]
        [ValidateSet("SDEGROUP1","SDEGROUP2","SDEGROUP3","SDEGROUP4","SDEGROUP5","SDEGROUP6","SDEGROUP7","SDEGROUP8","SDEGROUP9","SDEGROUP10")]
        [String]$GroupName,
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='Single Team')]
        [String]$GroupDescription,
        [Parameter(Position=2,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='All Teams')]
        [Switch]$ShowAllTeams,
        [Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$true)]
        [ValidateSet("Black","White","Red","Lime","Blue","Yellow","Cyan","Magenta","Silver","Gray","Maroon","Olive","Green","Purple","Teal","Navy")]
        [String]$Color = "Blue",
        [Parameter(Position=4,Mandatory=$false,ValueFromPipeline=$true)]
        $ServerInstance = "SDESQLSERVER"
    )
    
    $OutputArrays =@()
    $sqlquery = $null
    $Array = $null
    $teams = @()
    $Currentyear = get-date -Format yyyy
    $Currentdate = get-date -Format MM/dd
    $Lastyear = ($Currentyear-1)

    #========================================================================================
    #SQL Query To Find Open Tickets For Each Team
    #========================================================================================

    if([bool]($MyInvocation.BoundParameters.Keys -contains 'ShowAllTeams')){

        $teams += [PSCustomObject] @{'Group' = 'BHD'; 'Description' = 'Branch Help Desk'};
        $teams += [PSCustomObject] @{'Group' = 'CCENT'; 'Description' = 'DS User Request'};
        $teams += [PSCustomObject] @{'Group' = 'CORPSEC'; 'Description' = 'Corporate Security'};
        $teams += [PSCustomObject] @{'Group' = 'DBA'; 'Description' = 'Database Administrators'};
        $teams += [PSCustomObject] @{'Group' = 'DS_ADMIN'; 'Description' = 'Directory Services Administrators'};
        $teams += [PSCustomObject] @{'Group' = 'DW'; 'Description' = 'Data Warehouse'};
        $teams += [PSCustomObject] @{'Group' = 'EA'; 'Description' = 'Enterprise Architecture'};
        $teams += [PSCustomObject] @{'Group' = 'EA_DEV'; 'Description' = 'Enterprise Architecture - Dev'};
        $teams += [PSCustomObject] @{'Group' = 'EDW_ADMIN'; 'Description' = 'EDW Administration'};
        $teams += [PSCustomObject] @{'Group' = 'GOVERNANCE'; 'Description' = 'IT Governance'};
        $teams += [PSCustomObject] @{'Group' = 'HELP DESK'; 'Description' = 'Technology Help Desk'};
        $teams += [PSCustomObject] @{'Group' = 'INFOSEC'; 'Description' = 'Information Security'};
        $teams += [PSCustomObject] @{'Group' = 'MAC'; 'Description' = 'Move / Add / Change'};
        $teams += [PSCustomObject] @{'Group' = 'N2K'; 'Description' = 'Network 2000 - General'};
        $teams += [PSCustomObject] @{'Group' = 'N2K_IMAGE'; 'Description' = 'Network 2000 - Imaging'};
        $teams += [PSCustomObject] @{'Group' = 'NOG'; 'Description' = 'Network Team'};
        $teams += [PSCustomObject] @{'Group' = 'SHRSVS'; 'Description' = 'Server Infr. Team'};
        $teams += [PSCustomObject] @{'Group' = 'TECH_OPS_CHANNELS'; 'Description' = 'Tech-Ops Channels'};
        $teams += [PSCustomObject] @{'Group' = 'TECH_OPS_CORE_IPS'; 'Description' = 'Tech-Ops Core & IPS'};
        $teams += [PSCustomObject] @{'Group' = 'TECH_OPS_LENDING_CREDIT'; 'Description' = 'Tech-Ops Lending Credit'};
        $teams += [PSCustomObject] @{'Group' = 'TECH_PLATFORM_MANAGERS'; 'Description' = 'Tech-IT Platform Managers'};
        $teams += [PSCustomObject] @{'Group' = 'TELECOMS'; 'Description' = 'Telecom Team'};
        #$teams += [PSCustomObject] @{'Group' = 'WORKFLOW'; 'Description' = 'Nautilus Workflow'};
        $teams += [PSCustomObject] @{'Group' = 'WSG'; 'Description' = 'Endpoint Admins Team'};
        $teams += [PSCustomObject] @{'Group' = 'XEROX'; 'Description' = 'Xerox'};
    }else{
        $teams += [PSCustomObject] @{'Group' = $GroupName; 'Description' = $GroupDescription};
    }

    
    $Array = @()

    foreach ($team in $teams){
      
      #==Currently Open
      $sqlquery = "SELECT COUNT ([Work Order #])
      FROM [SDE_PROD].[_SMDBA_].[Work Orders]
      WHERE ([Status ID:] != 'Closed' AND [Status ID:] != 'ADMIN_CLOSE' AND [Status ID:] != 'CANCEL' AND [Status ID:] != 'REJECT' AND [Group Name] = '"+($team.Group)+"')"

      $team_WO = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance
      [int]$team_WO_Count = $team_WO.Column1

      $sqlquery = "SELECT COUNT ([Incident #])
      FROM [SDE_PROD].[_SMDBA_].[Incident]
      WHERE ([Status ID:] != 'Closed' AND [Status ID:] != 'ADMIN_CLOSE' AND [Status ID:] != 'CANCEL' AND [Status ID:] != 'REJECT' AND [Group Name] = '"+($team.Group)+"')"

      $team_IN = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance
      [int]$team_IN_Count = $team_IN.Column1

      #==This Year Count
      $sqlquery = "SELECT COUNT ([Incident #])
      FROM [SDE_PROD].[_SMDBA_].[Incident]
      WHERE ([Close Date & Time] between '$Currentyear/01/01' and '$Currentyear/12/31' AND [Group Name] = '"+($team.Group)+"')"

      $Closed_This_Year_IN = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

      $sqlquery = "SELECT COUNT ([Work Order #])
      FROM [SDE_PROD].[_SMDBA_].[Work Orders]
      WHERE ([Close Date & Time] between '$Currentyear/01/01' and '$Currentyear/12/31' AND [Group Name] = '"+($team.Group)+"')"

      $Closed_This_Year_WO = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

      [int]$Closed_This_Year_Count = $Closed_This_Year_IN.Column1 + $Closed_This_Year_WO.Column1

      #==Last Year Count
      $sqlquery = "SELECT COUNT ([Incident #])
      FROM [SDE_PROD].[_SMDBA_].[Incident]
      WHERE ([Close Date & Time] between '$Lastyear/01/01' and '$Lastyear/12/31' AND [Group Name] = '"+($team.Group)+"')"

      $Closed_Last_Year_IN = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

      $sqlquery = "SELECT COUNT ([Work Order #])
      FROM [SDE_PROD].[_SMDBA_].[Work Orders]
      WHERE ([Close Date & Time] between '$Lastyear/01/01' and '$Lastyear/12/31' AND [Group Name] = '"+($team.Group)+"')"

      $Closed_Last_Year_WO = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

      [int]$Closed_Last_Year_Count = $Closed_Last_Year_IN.Column1 + $Closed_Last_Year_WO.Column1

      #==Last Year to This Day Count
      $sqlquery = "SELECT COUNT ([Incident #])
      FROM [SDE_PROD].[_SMDBA_].[Incident]
      WHERE ([Close Date & Time] between '$Lastyear/01/01' and '$LastYear/$Currentdate' AND [Group Name] = '"+($team.Group)+"')"

      $Closed_Last_Year_To_This_Day_IN = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

      $sqlquery = "SELECT COUNT ([Work Order #])
      FROM [SDE_PROD].[_SMDBA_].[Work Orders]
      WHERE ([Close Date & Time] between '$Lastyear/01/01' and '$LastYear/$Currentdate' AND [Group Name] = '"+($team.Group)+"')"

      $Closed_Last_Year_To_This_Day_WO = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

      [int]$Closed_Last_Year_To_This_Day_Count = $Closed_Last_Year_To_This_Day_IN.Column1 + $Closed_Last_Year_To_This_Day_WO.Column1

      #==Tickets Open Exactly a year ago
      $sqlquery = "SELECT COUNT ([Incident #])
      FROM [SDE_PROD].[_SMDBA_].[Incident]
      WHERE ([Open Date & Time] <= '$LastYear/$Currentdate' AND [Close Date & Time] > '$LastYear/$Currentdate' AND [Group Name] = '"+($team.Group)+"')"

      $Open_Exactly_A_Year_Ago_IN = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

      $sqlquery = "SELECT COUNT ([Work Order #])
      FROM [SDE_PROD].[_SMDBA_].[Work Orders]
      WHERE ([Open Date & Time] <= '$LastYear/$Currentdate' AND [Close Date & Time] > '$LastYear/$Currentdate' AND [Group Name] = '"+($team.Group)+"')"

      $Open_Exactly_A_Year_Ago_WO = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

      [int]$Open_Exactly_A_Year_Ago_Count = $Open_Exactly_A_Year_Ago_IN.Column1 + $Open_Exactly_A_Year_Ago_WO.Column1

      $output = [PSCustomObject] @{
        'Team' = $team.Description;
        'SDE Group' = $team.Group;
        "Closed Total $Lastyear" = $Closed_Last_Year_Count; 
        "Closed So Far $Lastyear" = $Closed_Last_Year_To_This_Day_Count;
        "Closed So Far $Currentyear" = $Closed_This_Year_Count;
        #'Open Work Orders' = $team_WO_Count;
        #'Open Incidents' = $team_IN_Count;
        "# Open This Day in $Lastyear" = $Open_Exactly_A_Year_Ago_Count;
        'Open Today' = $team_WO_Count+$team_IN_Count;
        }

        $Array+=$output

    }
    
    #Array3
    $output = [PSCustomObject] @{
    'Message' = "This is a look at all of the open tickets and closed ticket trends amongst the other teams. For reference, your SDE Group is $GroupName. This list is sorted by Open Now";
    'Title' = "SDE Group Trends";
    'Color' = $Color;
    'Array' = $Array | Sort-Object -Descending -Property "Open Today";
    }
    if ($output.Array -ne $NULL){return $output}
}

PowerShell Module: New-DGMSDETeamTicketHistory2.psm1

<#
.SYNOPSIS
  Generates New-DMGSDETeamTicketHistory
.NOTES
  Version:        2.0
  Author:         David Maiolo
  Creation Date:  2018-01-26
  Purpose/Change: Initial script development

#>

#---------------------------------------------------------[Initialisations]--------------------------------------------------------

function New-DMGSDETeamTicketHistory2{
param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)]
        [ValidateSet("SDEGROUP1","SDEGROUP2","SDEGROUP3","SDEGROUP4","SDEGROUP5","SDEGROUP6","SDEGROUP7","SDEGROUP8","SDEGROUP9","SDEGROUP10")]
        [String]$GroupName,
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)]
        [String]$GroupDescription,
        [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$true)]
        [Int]$NumOfMonthsToShowTrends = 7,
        [Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$true)]
        [ValidateSet("Black","White","Red","Lime","Blue","Yellow","Cyan","Magenta","Silver","Gray","Maroon","Olive","Green","Purple","Teal","Navy")]
        [String]$Color = "Blue",
        [Parameter(Position=4,Mandatory=$false,ValueFromPipeline=$true)]
        $ServerInstance = "SDESQLSERVER"
    )
    
    $OutputArrays =@()
    $sqlquery = $null
    $Array = $null

    $Currentyear = get-date -Format yyyy
    $Currentdate = get-date -Format MM/dd
    $Lastyear = ($Currentyear-1)

    #========================================================================================
    #SQL Query To Find Ticket History
    #========================================================================================
    $Array = @()
    
    #Create Array Of All The Dates
    $Dates = @()
    $i=$NumOfMonthsToShowTrends
    while ($i -ge 0){
        
        <#
        $Date=(Get-Date).AddMonths(-($i)).ToString('yyyy/MM/dd')
        $DateMinusOne = (Get-Date).AddMonths(-($i+1)).ToString('yyyy/MM/dd')
        #>

        $CurrentDate=(Get-Date).AddMonths(-($i)).ToString('MM/dd/yyyy')

        $FirstDayOfMonth=GET-DATE $CurrentDate -Day 1
        $LastDayOfMonth=GET-DATE $FirstDayOfMonth.AddMonths(1).AddSeconds(-1)

        $Year = $FirstDayOfMonth.ToString('yy')
        $Month = $FirstDayOfMonth.ToString('MM')

        $Date = $LastDayOfMonth.ToString('yyyy/MM/dd')
        $DateMinusOne = $FirstDayOfMonth.ToString('yyyy/MM/dd')

        $output = [PSCustomObject] @{
        'Date' = $Date;
        'DateMinusOne' = $DateMinusOne;
        'Month' = $Month;
        'Year' = $Year;
        }

        $Dates+=$output
        $i--
    }

    foreach ($DateToProcess in $Dates){
        
        $Date = $DateToProcess.Date
        $DateMinusOne = $DateToProcess.DateMinusOne
        $Month = $DateToProcess.Month
        $Month = (Get-Culture).DateTimeFormat.GetMonthName($Month)
        $Year = $DateToProcess.Year

        <#
        Write-Host "Processing..."
        Write-Host "Date: $Date"
        Write-Host "DateMinusOne: $DateMinusOne"
        Write-Host "Month: $Month"
        Write-Host "Year: $Year"
        #>

        #Incidents Opened
        $sqlquery = "SELECT COUNT ([Incident #])
        FROM [SDE_PROD].[_SMDBA_].[Incident]
        WHERE ([Open Date & Time] between '$DateMinusOne' and '$Date' AND [Group Name] = '$GroupName')"
        $IncidentsOpened = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance
        [int]$IncidentsOpenedCount = $IncidentsOpened.Column1

        #Work Orders Opened
        $sqlquery = "SELECT COUNT ([Work Order #])
        FROM [SDE_PROD].[_SMDBA_].[Work Orders]
        WHERE ([Open Date & Time] between '$DateMinusOne' and '$Date' AND [Group Name] = '$GroupName')"
        $WorkOrdersOpened = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance
        [int]$WorkOrdersOpenedCount = $WorkOrdersOpened.Column1

        #Total Opened
        [int]$TotalOpenedCount = $IncidentsOpenedCount+$WorkOrdersOpenedCount

        #Incidents Closed
        $sqlquery = "SELECT COUNT ([Incident #])
        FROM [SDE_PROD].[_SMDBA_].[Incident]
        WHERE ([Close Date & Time] between '$DateMinusOne' and '$Date' AND [Group Name] = '$GroupName')"
        $IncidentsClosed = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance
        [int]$IncidentsClosedCount = $IncidentsClosed.Column1

        #Work Orders Closed
        $sqlquery = "SELECT COUNT ([Work Order #])
        FROM [SDE_PROD].[_SMDBA_].[Work Orders]
        WHERE ([Close Date & Time] between '$DateMinusOne' and '$Date' AND [Group Name] = '$GroupName')"
        $WorkOrdersClosed = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance
        [int]$WorkOrdersClosedCount = $WorkOrdersClosed.Column1

        #Total Closed
        [int]$TotalClosedCount = $IncidentsClosedCount+$WorkOrdersClosedCount

        #Ratio Opened to Closed
        if($TotalOpenedCount -gt 0){
            $RatioOpenedtoClosedCount = ([math]::Round((($TotalClosedCount)/($TotalOpenedCount)),2))
        }else{
            $RatioOpenedtoClosedCount = 0
        }

        $output = [PSCustomObject] @{
        'Month' = $Month+" `'"+$Year;
        #'Incidents Opened' = $IncidentsOpenedCount;
        #'Work Orders Opened' = $WorkOrdersOpenedCount;
        "Total Opened" = $TotalOpenedCount; 
        #"Incidents Closed" = $IncidentsClosedCount;
        #"Work Orders Closed" = $WorkOrdersClosedCount;
        "Total Closed" = $TotalClosedCount;
        'Ratio Opened to Closed' = $RatioOpenedtoClosedCount;
        }

        $Array+=$output

    }

    #Array4
    $output = [PSCustomObject] @{
    'Message' = "This is a look at the ticket trends for this group over the past $NumOfMonthsToShowTrends months. Ratios greater than 1.0 mean more tickets were closed than were opened, whereas those lower than 1.0 mean more tickets were opened than closed. ";
    'Title' = "$GroupDescription`: Open / Close Ratios";
    'Color' = $Color;
    'Array' = $Array;
    }
    if ($output.Array -ne $NULL){return $output}
}

PowerShell Module: New-DGMSDETeamUserHistory.psm1

<#
.SYNOPSIS
  Generates New-DMGSDEOpenIncidents
.NOTES
  Version:        1.0
  Author:         David Maiolo
  Creation Date:  2018-01-22
  Purpose/Change: Initial script development

#>

#---------------------------------------------------------[Initialisations]--------------------------------------------------------

function New-DMGSDETeamUserHistory{
param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)]
        [ValidateSet("SDEGROUP1","SDEGROUP2","SDEGROUP3","SDEGROUP4","SDEGROUP5","SDEGROUP6","SDEGROUP7","SDEGROUP8","SDEGROUP9","SDEGROUP10")]
        [String]$GroupName,
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)]
        [String]$GroupDescription,
        [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$true)]
        [Int]$NumOfMonthsToShowTrends = 7,
        [Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$true)]
        [ValidateSet("Black","White","Red","Lime","Blue","Yellow","Cyan","Magenta","Silver","Gray","Maroon","Olive","Green","Purple","Teal","Navy")]
        [String]$Color = "Blue",
        [Parameter(Position=4,Mandatory=$false,ValueFromPipeline=$true)]
        $ServerInstance = "SDESQLSERVER"
    )
    
    $OutputArrays =@()
    $sqlquery = $null
    $Array = $null

    #Compute Month 0 Variables
    $i=0
    $CurrentDate0=(Get-Date).AddMonths(-($i)).ToString('MM/dd/yyyy')

    $FirstDayOfMonth0=GET-DATE $CurrentDate0 -Day 1
    $LastDayOfMonth0=GET-DATE $FirstDayOfMonth0.AddMonths(1).AddSeconds(-1)

    $Year0 = $FirstDayOfMonth0.ToString('yy')
    $Month0 = $FirstDayOfMonth0.ToString('MM')
    $Month0 = (Get-Culture).DateTimeFormat.GetMonthName($Month0)

    $Date0 = $LastDayOfMonth0.ToString('yyyy/MM/dd')
    $DateMinusOne0 = $FirstDayOfMonth0.ToString('yyyy/MM/dd')

    #Compute Month 1 Variables
    $i=1
    $CurrentDate1=(Get-Date).AddMonths(-($i)).ToString('MM/dd/yyyy')

    $FirstDayOfMonth1=GET-DATE $CurrentDate1 -Day 1
    $LastDayOfMonth1=GET-DATE $FirstDayOfMonth1.AddMonths(1).AddSeconds(-1)

    $Year1 = $FirstDayOfMonth1.ToString('yyy')
    $Month1 = $FirstDayOfMonth1.ToString('MM')
    $Month1 = (Get-Culture).DateTimeFormat.GetMonthName($Month1)

    $Date1 = $LastDayOfMonth1.ToString('yyyy/MM/dd')
    $DateMinusOne1 = $FirstDayOfMonth1.ToString('yyyy/MM/dd')

    #Compute Month 2 Variables
    $i=2
    $CurrentDate2=(Get-Date).AddMonths(-($i)).ToString('MM/dd/yyyy')

    $FirstDayOfMonth2=GET-DATE $CurrentDate2 -Day 1
    $LastDayOfMonth2=GET-DATE $FirstDayOfMonth2.AddMonths(1).AddSeconds(-1)

    $Year2 = $FirstDayOfMonth2.ToString('yyyy')
    $Month2 = $FirstDayOfMonth2.ToString('MM')
    $Month2 = (Get-Culture).DateTimeFormat.GetMonthName($Month2)

    $Date2 = $LastDayOfMonth2.ToString('yyyy/MM/dd')
    $DateMinusOne2 = $FirstDayOfMonth2.ToString('yyyy/MM/dd')

    #Compute Month 3 Variables
    $i=3
    $CurrentDate3=(Get-Date).AddMonths(-($i)).ToString('MM/dd/yyyy')

    $FirstDayOfMonth3=GET-DATE $CurrentDate3 -Day 1
    $LastDayOfMonth3=GET-DATE $FirstDayOfMonth3.AddMonths(1).AddSeconds(-1)

    $Year3 = $FirstDayOfMonth3.ToString('yyyy')
    $Month3 = $FirstDayOfMonth3.ToString('MM')
    $Month3 = (Get-Culture).DateTimeFormat.GetMonthName($Month3)

    $Date3 = $LastDayOfMonth3.ToString('yyyy/MM/dd')
    $DateMinusOne3 = $FirstDayOfMonth3.ToString('yyyy/MM/dd')

    #========================================================================================
    #SQL Query To Find User Trends
    #========================================================================================
    $Array = @()

    $sqlquery = "SELECT [Support Staff ID]
      ,[Full Name]
      FROM [SDE_PROD].[_SMDBA_].[Groups Details]
      WHERE ([Group ID] = '$GroupName')"

    $TeamMmbers = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance


    foreach ($TeamMmber in $TeamMmbers){

        #Query For Incidents Open
        $sqlquery = "SELECT COUNT ([Incident #])
          FROM [SDE_PROD].[_SMDBA_].[Incident]
          WHERE ([Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"' AND [Status ID:] != 'Closed' AND [Status ID:] != 'ADMIN_CLOSE' AND [Status ID:] != 'CANCEL' AND [Status ID:] != 'REJECT')"

        $TotalIncidentsOpen = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

        #Query For Work Orders Open
        $sqlquery = "SELECT COUNT ([Work Order #])
          FROM [SDE_PROD].[_SMDBA_].[Work Orders]
          WHERE ([Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"' AND [Status ID:] != 'Closed' AND [Status ID:] != 'ADMIN_CLOSE' AND [Status ID:] != 'CANCEL' AND [Status ID:] != 'REJECT')"

        $TotalWorkOrdersOpen = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

        #=====
        #Query For Incidents Closed
        $sqlquery = "SELECT COUNT ([Incident #])
          FROM [SDE_PROD].[_SMDBA_].[Incident]
          WHERE ([Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"' AND ([Status ID:] = 'Closed' OR [Status ID:] = 'ADMIN_CLOSE'))"

        $TotalIncidentsClosed = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

        #Query For Work Orders Closed
        $sqlquery = "SELECT COUNT ([Work Order #])
          FROM [SDE_PROD].[_SMDBA_].[Work Orders]
          WHERE ([Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"' AND ([Status ID:] = 'Closed' OR [Status ID:] = 'ADMIN_CLOSE'))"

        $TotalWorkOrdersClosed = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

        #Query For Work Orders Closed At Date0
        $sqlquery = "SELECT COUNT ([Work Order #])
            FROM [SDE_PROD].[_SMDBA_].[Work Orders]
            WHERE ([Close Date & Time] between '$DateMinusOne0' and '$Date0' AND [Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"')"

        $TotalWorkOrdersClosedAtDate0 = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

        #Query For Incidents Closed At Date0
        $sqlquery = "SELECT COUNT ([Incident #])
            FROM [SDE_PROD].[_SMDBA_].[Incident]
            WHERE ([Close Date & Time] between '$DateMinusOne0' and '$Date0' AND [Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"')"

        $TotalIncidentsClosedAtDate0 = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

        #Query For Work Orders Closed At Date1
        $sqlquery = "SELECT COUNT ([Work Order #])
            FROM [SDE_PROD].[_SMDBA_].[Work Orders]
            WHERE ([Close Date & Time] between '$DateMinusOne1' and '$Date1' AND [Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"')"

        $TotalWorkOrdersClosedAtDate1 = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

        #Query For Incidents Closed At Date1
        $sqlquery = "SELECT COUNT ([Incident #])
            FROM [SDE_PROD].[_SMDBA_].[Incident]
            WHERE ([Close Date & Time] between '$DateMinusOne1' and '$Date1' AND [Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"')"

        $TotalIncidentsClosedAtDate1 = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

         #Query For Work Orders Closed At Date2
        $sqlquery = "SELECT COUNT ([Work Order #])
            FROM [SDE_PROD].[_SMDBA_].[Work Orders]
            WHERE ([Close Date & Time] between '$DateMinusOne2' and '$Date2' AND [Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"')"

        $TotalWorkOrdersClosedAtDate2 = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

        #Query For Incidents Closed At Date2
        $sqlquery = "SELECT COUNT ([Incident #])
            FROM [SDE_PROD].[_SMDBA_].[Incident]
            WHERE ([Close Date & Time] between '$DateMinusOne2' and '$Date2' AND [Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"')"

        $TotalIncidentsClosedAtDate2 = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

         #Query For Work Orders Closed At Date3
        $sqlquery = "SELECT COUNT ([Work Order #])
            FROM [SDE_PROD].[_SMDBA_].[Work Orders]
            WHERE ([Close Date & Time] between '$DateMinusOne3' and '$Date3' AND [Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"')"

        $TotalWorkOrdersClosedAtDate3 = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance

        #Query For Incidents Closed At Date3
        $sqlquery = "SELECT COUNT ([Incident #])
            FROM [SDE_PROD].[_SMDBA_].[Incident]
            WHERE ([Close Date & Time] between '$DateMinusOne3' and '$Date3' AND [Login ID Assigned To] = '"+($TeamMmber."Support Staff ID")+"')"

        $TotalIncidentsClosedAtDate3 = Invoke-Sqlcmd -Query $sqlquery -ServerInstance $ServerInstance


        $output = [PSCustomObject] @{
            'Team Member' = ($TeamMmber."Full Name");
            #'Incidents Closed' = ($TotalIncidentsClosed.Column1);
            #'Work Orders Closed' = ($TotalWorkOrdersClosed.Column1);
            "Closed $Month3 $Year3" = (($TotalWorkOrdersClosedAtDate3.Column1)+($TotalIncidentsClosedAtDate3.Column1))
            "Closed $Month2 $Year2" = (($TotalWorkOrdersClosedAtDate2.Column1)+($TotalIncidentsClosedAtDate2.Column1))
            "Closed $Month1 $Year1" = (($TotalWorkOrdersClosedAtDate1.Column1)+($TotalIncidentsClosedAtDate1.Column1))
            "Closed $Month0 $Year0" = (($TotalWorkOrdersClosedAtDate0.Column1)+($TotalIncidentsClosedAtDate0.Column1))
            #'Open Incidents' = ($TotalIncidentsOpen.Column1);
            #'Open Work orders' = ($TotalWorkOrdersOpen.Column1);
            'Total Closed' = (($TotalWorkOrdersClosed.Column1)+($TotalIncidentsClosed.Column1));
            'Currently Assigned' = (($TotalIncidentsOpen.Column1)+($TotalWorkOrdersOpen.Column1));
        }
        $Array+=$output
    }

    #Array5
    $output = [PSCustomObject] @{
    'Message' = "This is a look at the user trends over the entire SDE timeline.";
    'Title' = "$GroupDescription`: User Trends";
    'Color' = $Color;
    'Array' = $Array | Sort-Object -Property "Team Member";
    }
    if ($output.Array -ne $NULL){return $output}
}

Overview

I created this series of functions to allow you to conveniently take one or more PowerShell object and have it turn into an email report. For example, if you had a list of computers you have come out of a PowerShell script you wrote, these functions could help you turn them into an email with the same data without much effort.

Create an Email with A Simple Array of Data: EASY

Import-Module \\scriptserver\scripts\Get-DGMEmailReport\Get-DGMEmailReport.psm1 -Force

Import-Module \\pathtoscriptfile\Get-DGMEmailReport.psm1 -Force

$myarray = (Get-ADComputer -properties Description -filter {description -like "*sql*"} | Select Name,Description)

#Single Array Email Report
Get-DGMEmailReport `
    -Array $myarray `
    -ArrayTitle "SQL Servers" `
    -ArrayTitleColor "Blue" `
    -ArrayMessage "These are all the SQL Servers" `
    -ReportTitle "SQL Report 2017" `
    -from "SQLReports@emailaddress.com" `
    -To "c-dmaiolo@emailaddress.com" `
    -subject "SQL Test Report"  

Create an Email With Multiple Arrays of Data: MODERATE DIFFICULTY

Import-Module \\pathtoscriptfile\Get-DGMEmailReport.psm1 -Force 

#Create Some Arrays Of Data To Display in Report. You can create as many as you want.
$OutputArrays = @()
   
#Array1
$output = [PSCustomObject] @{
'Message' = "These are deployments older than 90 days, with lower than an 80% success rate where 100 or more computers were targeted.";
'Title' = "SCCM Problem Deployments";
'Color' = "Red";
'Array' = Get-DGMSCCMProblemDeploymentsArray -Days 90 -PercentSuccessThreshold .8 -NumberOfTargetedThreshold 100;
}

$OutputArrays+=$output

#Array2
$output = [PSCustomObject] @{
'Message' = "These are all deployments that were deployed over a year ago.";
'Title' = "SCCM Deployments Greater Than 1 Year Old";
'Color' = "Green";
'Array' = Get-DGMSCCMProblemDeploymentsArray -Days 365 -PercentSuccessThreshold 1 -NumberOfTargetedThreshold 0;
}
    
$OutputArrays+=$output
    
#Array3
$output = [PSCustomObject] @{
'Message' = "These are all our SQL Servers.";
'Title' = "SQL Servers";
'Array' = Get-ADComputer -properties Description -filter {description -like "*sql*"} | Select Name,Description
}

$OutputArrays+=$output 

#Multiple Arrays Email Report
Get-DGMEmailReport `
    -Arrays $OutputArrays `
    -ReportTitle "SQL Report 2017" `
    -from "SQLReports@emailaddress.com" `
    -To "c-dmaiolo@emailaddress.com" `
    -subject "SQL Test Report" 

Sample Single Array Output

Sample Multiple Array Output Email

PowerShell Functions

<#
.SYNOPSIS
  Generates an Email Report
.NOTES
  Version:        1.0
  Author:         David Maiolo
  Creation Date:  2018-01-02
  Purpose/Change: Initial script development

#>

#---------------------------------------------------------[Initialisations]--------------------------------------------------------

Import-Module \\scriptserver\scripts\DMGSCCM\New-DMGCMTraceLog\New-DMGCMTraceLog.psm1 -Force

function New-DMGCombinedHTMLTable{
 param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)]
        $DMGCombinedHTMLTable
    )
    $CombinedHTML = ""
    
    foreach ($object in $DMGCombinedHTMLTable){

            $CombinedHTML+= "
"
            
            if ($object.Title){
                if ($object.Color){
                    $CombinedHTML+= ""
                    #$CombinedHTML+= "
"+($object.Title)+"
"
                    $CombinedHTML+= "
"+($object.Title)+"
"

                }else{
                    $CombinedHTML+= "
"+($object.Title)+"
"
                }
                
            }
            if($object.Message){
                $CombinedHTML+= "
➥ "+($object.Message)+"
"
            }
            if ($object.Array){
                $CombinedHTML+= $object.Array | ConvertTo-Html
            }
            
            $CombinedHTML+= "
"

    }

    return $CombinedHTML
}

function New-DMGHTMLTable{
 param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)]
        $Array,
        [Parameter(Position=1,Mandatory=$false,ValueFromPipeline=$true)]
        $Title,
        [Parameter(Position=1,Mandatory=$false,ValueFromPipeline=$true)]
        $Message,
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)]
        [ValidateSet("Black","White","Red","Lime","Blue","Yellow","Cyan","Magenta","Silver","Gray","Maroon","Olive","Green","Purple","Teal","Navy")]
        $Color
    )
    $HTML = ""
    $HTML+= "
"

    if([bool]($MyInvocation.BoundParameters.Keys -match 'Title')){
        if([bool]($MyInvocation.BoundParameters.Keys -match 'Color')){
            $HTML+= ""
        }
        $HTML+= "
"+($Title)+"
"
    }
    if([bool]($MyInvocation.BoundParameters.Keys -match 'Message')){
        $HTML+= "
➥ "+($Message)+"
"
    }
    if([bool]($MyInvocation.BoundParameters.Keys -match 'Array')){
        $HTML+= $Array | ConvertTo-Html
    }

    $HTML+= "
"

    return $HTML
}

function New-DMGEmailReport{

 param(
        [Parameter(Position=0,Mandatory=$false,ValueFromPipeline=$true,ParameterSetName='Multiple Arrays')]
        $Arrays,
        [Parameter(Position=1,Mandatory=$false,ValueFromPipeline=$true,ParameterSetName='Single Array')]
        $Array,
        [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$true,ParameterSetName='Single Array')]
        $ArrayMessage,
        [Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$true,ParameterSetName='Single Array')]
        $ArrayTitle,
        [Parameter(Position=9,Mandatory=$false,ValueFromPipeline=$true,ParameterSetName='Single Array')]
        [ValidateSet("Red","Yellow","Green","Black")]
        $ArrayTableColor = "Black",
        [Parameter(Position=4,Mandatory=$true,ValueFromPipeline=$true)]
        $From,
        [Parameter(Position=5,Mandatory=$true,ValueFromPipeline=$true)]
        $To,
        [Parameter(Position=6,Mandatory=$true,ValueFromPipeline=$true)]
        $Subject,
        [Parameter(Position=7,Mandatory=$true,ValueFromPipeline=$true)]
        $ReportTitle,
        [Parameter(Position=8,Mandatory=$false,ValueFromPipeline=$true)]
        $SmtpServer = "mail.emailaddress.com",
        [Parameter(Position=9,Mandatory=$false,ValueFromPipeline=$true)]
        [Switch]$AttatchResults,
        [Parameter(Position=10,Mandatory=$false,ValueFromPipeline=$true)]
        [Switch]$AttatchSource
    )

    #Set Logging Varibales
    $invocation = (Get-Variable MyInvocation -Scope 1).Value
    $ScriptDirectory = Split-Path $invocation.MyCommand.Path
    $ScriptName = ($MyInvocation.MyCommand.Name)+".psm1"
    $LogName = ($MyInvocation.MyCommand.Name)+".log"
    $LogFile = Join-Path $ScriptDirectory $LogName
    $ScriptFile = Join-Path $ScriptDirectory $ScriptName
    $ReportDate = Get-Date 
    $WrapperScriptPath = $MyInvocation.PSCommandPath

    #Map a Drive to UNC Path
    New-PSDrive -Name UNCPath -PSProvider FileSystem -Root $ScriptDirectory

    #Log Start of Function
    New-DMGCMTraceLog -message ("Starting Logging for $ScriptName") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile

    #Create Attachments Directory If It Doesn't Exist
    if (!(Test-Path "UNCPath:\Attachments\")){
        New-DMGCMTraceLog -message ("UNCPath:\Attachments\ Not Found. Created.") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile
        New-Item "UNCPath:\Attachments\" -ItemType Directory
    }


    if([bool]($MyInvocation.BoundParameters.Keys -contains 'Arrays')){
        $HTMLTable = New-DMGCombinedHTMLTable -DMGCombinedHTMLTable $Arrays
        #Log
        New-DMGCMTraceLog -message ("An Array of Arrays Was Passed") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile
        if([bool]($MyInvocation.BoundParameters.Keys -contains 'AttatchResults')){
            $attachment = @()
            
            foreach ($object in $Arrays){
                $AttachmentName = (Remove-DMGInvalidFileNameChars($object.Title))+".csv"
                $AttachmentFile = Join-Path UNCPath:\Attachments\ $AttachmentName
                $object.Array | export-csv -path $AttachmentFile -notypeinformation
                $attachment += $AttachmentFile
            }
            New-DMGCMTraceLog -message ("Attatching multiple files: $attachment") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile 
        }

    }elseif([bool]($MyInvocation.BoundParameters.Keys -contains 'Array')){
        $HTMLTable = New-DMGHTMLTable -Array $Array -Message $ArrayMessage -Title $ArrayTitle -Color $ArrayTableColor
        #Log
        New-DMGCMTraceLog -message ("An Single Array Was Passed") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile
        if([bool]($MyInvocation.BoundParameters.Keys -contains 'AttatchResults')){
            $attachment = @()
            $AttachmentName = (Remove-DMGInvalidFileNameChars($ArrayTitle))+".csv"
            $AttachmentFile = Join-Path UNCPath:\Attachments\ $AttachmentName
            $Array | export-csv -path $AttachmentFile -notypeinformation
            $attachment += $AttachmentFile
         }
         New-DMGCMTraceLog -message ("Attatching the single file: $Attachment") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile  
    }

#Construct HTML for Email

$HTMLEmailMessage = @"


$Subject

Report Title:
$ReportTitle
Date:
$ReportDate

$HTMLTable
Path to script: $ScriptFile
Path to log file: $LogFile
Path to script that called $ScriptName`: $WrapperScriptPath

"@

if([bool]($MyInvocation.BoundParameters.Keys -contains 'AttatchSource')){
    if ($attachment -eq $null){$attachment = @()}
    
    $AttachmentName = (Remove-DMGInvalidFileNameChars($ReportTitle))+"_html_source.txt"
    $AttachmentFile = Join-Path UNCPath:\ $AttachmentName
    $HTMLEmailMessage | Out-File -FilePath $AttachmentFile
    $attachment += $AttachmentFile
}
       
    #Send The Email
    if ($Attachment){
        Send-MailMessage -From $from -To $To -Subject $subject -SmtpServer $smtpServer -BodyAsHtml -Body $HTMLEmailMessage -Attachments $Attachment
        #Log Sent Email
        New-DMGCMTraceLog -message ("Email Sent`: Subject`:$subject To`:$To From`:$From Attatchments`:$attachment") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile
    }else{
        Send-MailMessage -From $from -To $To -Subject $subject -SmtpServer $smtpServer -BodyAsHtml -Body $HTMLEmailMessage
        #Log Sent Email
        New-DMGCMTraceLog -message ("Email Sent`: Subject`:$subject To`:$To From`:$From") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile
    }

    #Remove Attachments
    
    if (Test-Path "UNCPath:\Attachments\"){
        Remove-Item "UNCPath:\Attachments\" -Recurse
        New-DMGCMTraceLog -message ("UNCPath:\Attachments\ Found. Removing files within.") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile
    }

    #Log End Of Function
    New-DMGCMTraceLog -message ("End Logging for $ScriptName") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile
}

function Get-DMGColorHexValue{

param(
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)]
        [ValidateSet("Black","White","Red","Lime","Blue","Yellow","Cyan","Magenta","Silver","Gray","Maroon","Olive","Green","Purple","Teal","Navy")]
        $Color
    )

    switch ($Color)
    {
        "Black" {"#000000"} 
        "White" {"#FFFFFF"} 
        "Red" {"#FF0000"} 
        "Lime" {"#00FF00"} 
        "Blue" {"#0000FF"} 
        "Yellow" {"#FFFF00"} 
        "Cyan" {"#00FFFF"}
        "Magenta" {"#FF00FF"}
        "Silver" {"#C0C0C0"} 
        "Gray" {"#808080"} 
        "Maroon" {"#800000"} 
        "Olive" {"#808000"} 
        "Green" {"#008000"} 
        "Purple" {"#800080"} 
        "Teal" {"#008080"}
        "Navy" {"#000080"} 

        default {"#DDDDFF"}
    }

}

Function Remove-DMGInvalidFileNameChars {
  param(
    [Parameter(Mandatory=$true,
      Position=0,
      ValueFromPipeline=$true,
      ValueFromPipelineByPropertyName=$true)]
    [String]$String
  )
    $pattern = '[^a-zA-Z]'
    $CleanString = $String -replace $pattern
    return $CleanString
}

PowerShell Code: The Logging Functions

Import this module to enable the logging functions.

function New-DGMCMTraceLog{
  param (
  [Parameter(Mandatory=$true)]
  $ScriptName,
  [Parameter(Mandatory=$true)]
  $LogFile,
  [Parameter(Mandatory=$true)]
  $ScriptFile,
  [Parameter(Mandatory=$true)]
  $message,
  [Parameter(Mandatory=$true)]
  $component,
  [Parameter(Mandatory=$true)]
  $type )

  $VerboseLogging = "true"
  [bool]$Verbose = [System.Convert]::ToBoolean($VerboseLogging)
  $MaxLogSizeInKB = 10240
  $ScriptStatus = 'Success'

  switch ($type)
  {
    1 { $type = "Info" }
    2 { $type = "Warning" }
    3 { $type = "Error" }
    4 { $type = "Verbose" }
  }

  if (($type -eq "Verbose") -and ($Verbose))
  {
    $toLog = "{0} `$$<{1}><{2} {3}>" -f ($type + ":" + $message), ($ScriptName + ":" + $component), (Get-Date -Format "MM-dd-yyyy"), (Get-Date -Format "HH:mm:ss.ffffff"), $pid
    $toLog | Out-File -Append -Encoding UTF8 -FilePath ("filesystem::{0}" -f $LogFile)
    Write-Host $message
  }
  elseif ($type -ne "Verbose")
  {
    $toLog = "{0} `$$<{1}><{2} {3}>" -f ($type + ":" + $message), ($ScriptName + ":" + $component), (Get-Date -Format "MM-dd-yyyy"), (Get-Date -Format "HH:mm:ss.ffffff"), $pid
    $toLog | Out-File -Append -Encoding UTF8 -FilePath ("filesystem::{0}" -f $LogFile)
    if ($type -eq 'Info') { Write-Host $message }
    if ($type -eq 'Warning') { Write-Host $message -ForegroundColor Yellow}
    if ($type -eq 'Error') { Write-Host $message -ForegroundColor Red}
    

  }
  if (($type -eq 'Warning') -and ($ScriptStatus -ne 'Error')) { $ScriptStatus = $type }
  if ($type -eq 'Error') { $ScriptStatus = $type }

  if ((Get-Item $LogFile -ErrorAction SilentlyContinue).Length/1KB -gt $MaxLogSizeInKB)
  {
    $log = $LogFile
    Remove-Item ($log.Replace(".log", ".lo_"))
    Rename-Item $LogFile ($log.Replace(".log", ".lo_")) -Force
  }
}