Overview

At times it is important to determine if a client machine has a certain certificate installed from a certificate template. I developed this script, Get-DMGCertificateTemplateExistance, to to detect if a certificate was created from a particular template name. It could be run stand-alone or is also deployable as an SCCM configuration item/baseline and will return the desired results in a true / false fashion.

Instructions

Add the namme of the certificate template you would like to check within the Invoke-DMGCertificateTemplateExistance function and then run the PowerShell script. If the computer you run the script on has a certificate that was created from the template you provided, the script will return true, and it will return false otherwise.

function Invoke-DMGCertificateTemplateExistance{
    $CertificateName = 'Display Name of Certificate Template Certificate Was Created From'
    Get-DMGCertificateTemplateExistance -CertificateName $CertificateName
}

Detecting Certificate Template Compliance in SCCM

Deploy this to your required machines as a configuration baseline compliance item. Deployed to this sample machine, we have configured the function to look for a Cisco ISE certificate. Here we can see the machine reports compliance.

PowerShell Script: Get-DMGCertificateTemplateExistance.ps1

<#

.SYNOPSIS
  Determines if a certificate exists on the local machine that matches the template name
.NOTES
  Version:        1.0
  Author:         David Maiolo
  Creation Date:  2018-02-02
  Purpose/Change: Initial script development

#>

function Get-DMGCertificateTemplateExistance{
    [CmdletBinding()]
    param(
    [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
    [String]$CertificateName
    )

    #Set Certificate Template Existance Count
    $i = 0
    #Get All of the local machine certificates
    $cert = $null
    $certs = $null
    $certs = get-childitem cert:\localmachine\my

    #Loop through each certificate
    foreach ($cert in $certs){ 
        $temp = $null

        #See if certificate associated with Microsoft Chryptogrophy:  szOID_ENROLL_CERTTYPE_EXTENSION
        $temp = $cert.Extensions | Where-Object{$_.Oid.Value -eq "1.3.6.1.4.1.311.20.2"}
        if(!$temp){
            #Else see if certificate associated with Microsoft CertSrv Infrastructure: Certificate template extension (v2) szOID_CERTIFICATE_TEMPLATE
            $temp = $cert.Extensions | Where-Object{$_.Oid.Value -eq "1.3.6.1.4.1.311.21.7"}
         }
         
         #Create a New Value, Template, and see if it mateches the template name we are looking for
         if($temp){
            $cert | Add-Member -Name Template -MemberType NoteProperty -Value $temp.Format(1)
        
            #If the template name is found, incrememnt the Certificate Template Existance Count
            if ($cert.template.contains($CertificateName)){
                $i++
                break;
            }
        }
    }

    #If the Certificate Template Existance Count is greater than one, we found a certificate with our template
    if ($i -gt 0){return $true; break}else{return $false}
}

function Invoke-DMGCertificateTemplateExistance{
    $CertificateName = 'Display Name of Certificate Template Certificate Was Created From'
    Get-DMGCertificateTemplateExistance -CertificateName $CertificateName
}

Invoke-DMGCertificateTemplateExistance

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
  }
} 

UniversityLite Design Overview

I developed UniversityLite as a rapid deployment e-commerce tool to market university products and information to university students over the web using PHP. In other words, UniversityLite creates, deploys and maintains university websites automatically using custom PHP function. As of 2016, this tool has generated and maintains over 7.000 websites with each receiving about 1,000 views a month. The tool is based on the Model-View-Controller (MVC) architectural programming framework.

UniversityLite as a Function f

In its simplest form, UniversityLite can be understood as a function, where the input is simply the name of a university and the output is a full and complete website tailored to that university. Therefore the job of the function is to automatically create the database and website code for a full and complete website. The method by which this is accomplished is through the systematic gathering of information and the resulting interpretation and presentation of that information based on complex, but predictable logic.

UniversityLite is written primarily in PHP and uses SQL, Javascript and BASH to collect data automatically from sources such as Wikipedia, Google, Bing, Youtube, Amazon, eBay and NCES (and other governmental data sources) to slowly build an understanding about the environment in which to create the site. Most data is gathered through open APIs, such as Bing’s Image API which is used to automatically gather relevant images to a topic, or Amazon’s AWS API to generate products.

UniversityLite Sample API REST Request with Amazon AWS

Generating Variables

As the base input for content generation, the tool was fed 7,000 university names that I gathered from the National Center for Education Statistics (NCES).

Let’s look at one of those university names, “Monroe Community College”, as an example to see how the tool generates basic information about the university.

–

Insert “Monroe Community College” into Algorithm

First, the tool must determine basic information about the string, “Monroe Community College”. We can reasonably assume this will be the name of a university and will develop an array of queries we will use that represent how the university might be referenced on the internet. First, the tool sees the substring, “Community College” and can reasonably determine we might also call the college by its acronym, “MCC”. This logic is used to generate up to four different unique names each university might go by, such as “Brockport University” from the string “State University of New York at Brockport”.

Now that the tool has an array of search terms, it can get to work using intelligent functions with several of the open APIs mentioned to build a SQL database of variables associated with this university. It would be exhaustive to go through the process of all variables generated as each university name generates thousands of variables. Yet, here are some obvious ones along with the techniques used to gather them:

• Preferred subdomain such as “mcc.universitylite.com” (query NCES API for website address and strip out the subdomain, ‘mcc’)
• University Colors such as “Blue and Gold”, as hex values (strippng relevant color names from wikipedia_infobar_request function and sending them to a color_names_to_hex function).
• City, State and Zip of university, such as “Rochester”, “NY” and “14626” (query NCES API for these values)

…and this process goes on and on to gather such information as demographics, weather, local attractions (such as “Monroe County Museum”), apparel categories (such as “MCC Men’s Pants”), degree programs and product categories based on degree programs (such as “Nursing Supplies”).

UniversityLite builds the SQL database with these variables, organizing each major category into a relevant table. To streamline access and storage, we break each table into the data, a data dictionary describing the data and a variable dictionary (as indicated by the VARS and DD tables below).

Universitylite Database Sample

Generating Content

Now that UniversityLite has created our SQL database of a couple thousand variables associated to “Monroe Community College”, we can begin to generate content associated to Monroe County College as a university.

For starters, the functions will begin to create the skeleton content for the website, such as colors, titles, logos and basic content. When displaying products, for example men’s apparel, we use variables such as the university’s name, colors, demographics and degree programs to query the Amazon AWS API.

Function for Sports Weight

In this example, we determine the university total student population is 52% male, which indicates it is fine to go ahead and generate the REST request to Amazon for a male category (as opposed to an all female university, for example). The variables also show us this university has several sports teams, so we decide to put weight on men’s apparel that is sports related. This weight comes in the form of an array of possible search terms that are given mathematical weights to them based on the funding the sports teams receive (aka their size relative to the overall university’s budget).

The chosen array search term, which let’s say is “MCC Men’s Basketball Activewear” is used to build an Amazon REST request by referencing a custom REST generator build for Men’s Apparel (to assure we are only returning items we want within a certain scope of Men’s apparel). This logic will be repeated across the scope of probability we determine from the sports weight function (and other similarly performing functions) to generate a product list which reflects the weight of sports and other variables actually represented by university statistical data.

REST Request URI Generation

Once we finally have created our REST request as a string to send to Amazon’s API, we pass it to Amazon, which will in turn provide us an XML database of product information. This will then be imported into the UniversityLite server as a local file for speed in calling the same product string again (dumped after 24 hours for the sake of having relevant pricing models). Each XML comes back between 10MB-30MB, which can quickly grow to over 10GB of XML data in a couple days, depending on how actively the site is being used by actual users. Management of this data with 7,000 active sites (not to mention all the other data generated such as images) requires tight tolerances of data purging to assure the servers that run UniversityLite do so smoothly.

Merging all of the XMLs related to the probability function for sports weight, etc. (using a custom XML Merge function just for this purpose) creates a final XML locally on our server that represents the final collection of information that will be used to display one collection of MCC apparel (in this case, MCC Men’s Apparel).

A sampled section of such a merged XML can be seen below.

Amazon XML Data Return Sample

This type of process is used for a couple dozen different product categories which are used through the site. In Monroe Community College’s case, it used for both men and women apparel and merchandise, in addition to several product categories that relate to variables relatable to the university.

Of the greatest importance is applying this logic to textbooks a student could purchase. Because we don’t know what textbooks a student might desire, the search query becomes the basis for the REST request. The only major difference is that the query is passed to a function expecting book titles or ISBNs specifically. One unique aspect, however, is the inclusion of the UniversityLite Aggresearch™. This is a very custom solution I put in place to assure that when UniversityLite Aggresearch™ is chosen, the very lowest priced book opportunities are returned. The basis for this logic is searching several possibilities from multiple vendors and inserting those into an array where we can sort it by the lowest price. The result in many cases is that UniversityLite Aggresearch™ often returns books at competitive prices versus other book searching algorithms found on the internet.

UniversityLite Aggresearch (TM) Search Example

One other major area of the site that falls into the same category is the ability for students to post and sell their own books. Although the process is intended to look seamless to a user, this area has completely different functionality. The buying and selling mechanism is based off the WPAdverts – Classifieds Plugin. A major change I made to the plugin is the ability to automatically fill in the classified posting with Author, Title, ISBN, pictures of the book and suggested prices of the book so the user does not have to. Simply entering in either the ISBN or Title will auto populate all relevant fields. This is a major improvement to the WPAdverts Plugin, and one that makes the plugin unique to UniversityLite. In addition, the e-mail domain variable is used to force users to login with a valid university email to sell a book. This creates a niche and protected market for students at a particular university where they know books are being bought and sold by verified students.

UniversityLite Automated Book Sale Feature

Aside from product generation, UniversityLite also determines useful characteristics about the university that create community pages that automatically update. One such page displays recent YouTube sports videos associated to the university, while another displays recent videos from university students based on a geolocation search to the API. In the former case, the sports team used for the YouTube API search is determined using the wikipedia API and in the geolocation is based on an NCES API call where we query the longitude and latitude. The resulting geolocation is passed to the code snippet below which in turns builds a list of YouTube videos we can insert.

UniversityLite Youtube GeoLocation Code Snippet

Another major component of the generated site are dozens of articles, reports and graphs that are automatically “written” for the purpose of displaying information about the university. This is used to drive traffic and interest to the site with unique content. A sample of such an article can be seen below.

UniversityLite News Article Generation Sample

The many graphs for the article are created using several different methods, most of which use the NCES and Wikipedia variable results now in the SQL Database. In the sample below, we iterate through available variables to build the foundation for a JPEG graph that will be displayed on the site.

A sentence within the article might follow logic something like this code snippet below.

UniversityLite Sentence Generation Sample

Load Balancing

UniversityLite is load balanced across three Ubuntu 14.04 LTS virtual servers located in New York, NY with one backup server in Webster, NY. The three primary servers are balanced alphabetically by university name. There are 7,000 websites available as subdomains off of the universitylite.com domain (such as rit.universitylite.com). Each subdomain is given resources by the server only if actively being viewed. As seen in the image below, an arbitrary university named ‘David’s University College’ would be hosted on Server1. Because the functions and algorithms to create any of the A-Z universities resides on each ot the servers, the balancing is done for resource balancing, not necessarily content balancing.

UniversityLite Server Balance

Because of the large set of active subdomains, load balancing through DNS would be cumbersome and time consuming as partial wildcards (such as a*.universitylite.com) in a DNS zone are not defined behavior within the RFC. Manually adding a record for rit.universitylite.com, for example, would also require us to add a manual record for the 6,999 other subdomains. Therefor a DNS zone can only practicaly be used with full wildcards such as *.universitylite.com to to one of the universitylite.com servers. As a result, load balancing is handled first by the Apache virtualhost file, and then subsequently by PHP logic once the subdomain is called.

As seen in the function below, we can query the SQL table once a subdomain passed to the UniversityLite webserver. If the subdomain is found as a SQL entry, we can go ahead and assume the server referenced is the correct load balanced server. However, if it isn’t, we a) either can assume it’s load balanced on one of the other servers or b) it is simply not a valid subdomain anywhere on the site. For example, trying to go to gaboldygooky.universitylite.com will fail all load balancing checks and return an error to the browser.

UniversityLite PHP Load Balance Example

In this way, I have created a dynamic load balancing system that allows sites to be added and removed ad-hoc without having to alter DNS. Of course, this is just general domain load balancing, and we must further balance the content itself. There are, afterall, 7,000 different subdomains with uniquely generated HTML, photos, graphs, etc. The easiest way I found to handle this is by having UniversityLite just be one central source of programming code, and to allow other content to be displayed dynamically when required. That is not to say, however, that each site can be dynamically created each time it is viewed, but we can remove some redundancy from the equation.

Below are areas of dialy generated content that CAN be shared between them, assuming the same calculator with description, etc. might be viewed by more than one subdomain. This data alone amounts to about 10GB of freshly generated content for a 24 hour period for the sites on server1. If we did not consolidate this data, this could easily have grown to 23TB of data in just 24 hours on one server.

Directory List of ~/www/universitylite.com/public_html/sites/shared$:

drwxr-xr-x 2 maiolo99 maiolo99 4096 Oct 20 16:49 adpics
drwxr-xr-x 7 maiolo99 maiolo99 4096 Oct 20 21:29 amazon_xml
drwxr-xr-x 2 maiolo99 maiolo99 4096 Sep 23 18:07 apparel_images
drwxrwx--- 2 maiolo99 maiolo99 4096 Sep 18 16:54 avatars
drwxrwx--- 2 maiolo99 maiolo99 4096 Jul 26 23:29 holidays
drwxr-xr-x 356 maiolo99 maiolo99 20480 Oct 8 00:30 ms_images
drwxr-xr-x 2 maiolo99 maiolo99 7966720 Oct 21 16:51 product_images


However, sometimes there is only so much we can do. As seen in the directory listing below, these files are created uniquely for one of the subdomains (mcc.universitylite.com) below. This information is perfectly unique to Monroe Community College, and there is nothing we can really do about it.

Directory Listing of ~/www/universitylite.com/public_html/sites/MCCTextbooks:

./daily_message:
MonroeCommunityCollege_20161019_daily.txt

./youtube-2016-10-19:
BevierMemorialBuildingRochesterNY_youtube_2016-10-19.txt
BridgeSquareHistoricDistrictRochesterNY_youtube_2016-10-19.txt
Campbell-WhittleseyHouseRochesterNY_youtube_2016-10-19.txt
FirstPresbyterianChurchRochesterNewYork_youtube_2016-10-19.txt
geosearch-431012652C-77608488--searchquery-_youtube.txt
geosearch-Rochester-NY--searchquery-RochesterNYMusic_youtube.txt
HerveyElyHouseRochesterNY_youtube_2016-10-19.txt
ImmaculateConceptionChurchRochesterNewYork_youtube_2016-10-19.txt
JonathanChildHouseBrewsterBurkeHouseHistoricDistrictRochesterNY_youtube_2016-10-19.txt
MainOakRIRTRstationRochesterNY_youtube_2016-10-19.txt
MonroeCommunityCollegeSports_youtube_2016-10-19.txt
MonroeCommunityCollege_youtube_2016-10-19.txt
NickTahouHotsRochesterNY_youtube_2016-10-19.txt
ThirdWardHistoricDistrictRochesterNewYork_youtube_2016-10-19.txt


The best we can do with this data is purge it (beyond what we might want to keep for historical purposes). We do this by adding historically usable data into a SQL table for that university, and simply deleting the rest.

The load balancing techniques used on UniversityLite have allowed us to maintain 7,000 unique websites, on only three servers.

Summary

In all, UniversityLite was written in over 800 PHP functions and algorithms, in conjunction with Javascript, SQL, BASH and HTML to provide the automated creation of a website based on a search string such as “Monroe Community College”. The result is a collection of over 7,000 automatically created and maintained websites that generate traffic and income in a most automatically landscape.

Now that I have the groundwork for this structure in place, I am already applying it to other markets such as automobile parts and niche Hawaiian products.

For more information, or a closer demo of my work or code, please feel free to contact me.