Send an html email report containing a list of overlapping IP range boundaries in MEMCM

New-CMOverlappingBoundariesReport.ps1

# MEMCM database params
$script:dataSource = 'myMEMCMSQLserver' # MEMCM SQL server name, include instance if needed
$script:database = 'CM_ABC' # MEMCM database name

# Html CSS style 
$Style = @"
<style>
table { 
    border-collapse: collapse;
    font-family: sans-serif
    font-size: 10px
}
td, th { 
    border: 1px solid #ddd;
    padding: 6px;
}
th {
    padding-top: 8px;
    padding-bottom: 8px;
    text-align: left;
    background-color: #3700B3;
    color: #03DAC6
}
</style>
"@

# Function to get data from SQL server
function Get-SQLData {
    param($Query)
    $connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;"
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()
    
    $command = $connection.CreateCommand()
    $command.CommandText = $Query
    $reader = $command.ExecuteReader()
    $table = New-Object -TypeName 'System.Data.DataTable'
    $table.Load($reader)
    
    # Close the connection
    $connection.Close()
    
    return $Table
}

# SQL query
$Query = "
Select * from dbo.BoundaryEx
Where BoundaryType = 3 
and (NumericValueLow is not null
or NumericValueHigh is not null)
"

# Get SQL data
$Results = Get-SQLData -Query $Query 

# Custom class
class OverLappedBoundary
{
    [string]$BoundaryName
    [string]$BoundaryValue
    [string]$OverLappingBoundary
    [string]$OverLappingBoundaryValue
}

# Find the overlapping boundaries
$OverLappingBoundaries = @()
foreach ($Result in $Results)
{
    foreach($Boundary in $Results)
    {
        If ($Result.BoundaryID -ne $Boundary.BoundaryID -and (($Result.NumericValueLow -gt $Boundary.NumericValueLow -and $Result.NumericValueLow -lt $Boundary.NumericValueHigh) -or ($Result.NumericValueHigh -lt $Boundary.NumericValueHigh -and $Result.NumericValueHigh -gt $Boundary.NumericValueLow)))
        {
            $OverLappedBoundary = [OverLappedBoundary]::new()
            $OverLappedBoundary.BoundaryName = $Result.Name 
            $OverLappedBoundary.OverLappingBoundary = $Boundary.Name
            $OverLappedBoundary.BoundaryValue = $Result.Value 
            $OverLappedBoundary.OverLappingBoundaryValue = $Boundary.Value
            $OverLappingBoundaries += $OverLappedBoundary
        }
    }
}

If ($OverLappingBoundaries.Count -ge 1)
{
    # Email params
    $EmailParams = @{
        To         = 'myrecipients@mycompany.com'
        From       = 'MEMCMReports@mycompany.com'
        Smtpserver = 'mycompany-com.mail.protection.outlook.com'
        Port       = 25
        Subject    = "MEMCM Overlapping Boundaries Report ($($OverLappingBoundaries.Count))  |  $(Get-Date -Format dd-MMM-yyyy)"
    }

    # Prepare the HTML
    $Precontent = "<h3>IP range boundaries on the left are included in the boundaries on the right.</h3>"
    $HTML = $OverLappingBoundaries|
    ConvertTo-Html -Head $Style -PreContent $Precontent |
    Out-String

    # Send email
    Send-MailMessage @EmailParams -Body $html -BodyAsHtml 
}