Having boundaries that overlap with other boundaries can be a royal pain in MEMCM if those boundaries are in different locations – unless identified and sorted out you can potentially have clients getting content from undesired sources – across the continent or even across the world in some cases!
I prepared the following script that could be run as a scheduled task to send an email report on any IP range boundaries that overlap with other boundaries. The boundaries on the left side of the table have IP ranges that are covered by (included in) the boundaries on the right side.
The account that runs the script will need read access to the MEMCM database and the permission to relay mail in Exchange online.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # 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 | |
| } |
