CM, IP-ranges and unknown networks

Posted by nickekallen on in ConfigMgr, Script

A follow-up of a previous post relating to matching ConfigMgr IP-range boundaries to known networks. The essence is to send an email (scheduled at your own interval) that notifies if there are any clients on unknown networks inventoried.

Prerequisites

  • Configuration Manager is required to have IP-ranges for boundaries
  • We assume that boundaries are /24, or 255.255.255.0 and IPv4
  • Clients need to collect and report Network Data
  • The user running the script needs to be able to connect and read data from Configuration Manager database
  • SMTP-server to send an email

What do we do?

  • Gather all active IP-range boundaries from database
  • Gather reported networks that matches the DNS-Suffix defined, IPv4 and sum up # of devices within /24-networks

Loads of assumptions….  incase you need to tweak it this is how we gather from the database.

Client networks – alter for $netquery. Remember to replace $dnsdomain

select 
SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) + '.1' As IP,
COUNT(*) as Devices
 from v_Network_DATA_Serialized as ip 
where ip.IPAddress0 IS NOT NULL and 
ip.IPSubnet0 != '64'  and 
ip.IPSubnet0 != '128' and 
ip.IPSubnet0 = '255.255.255.0' and 
ip.DNSDomain0 IS NOT NULL and 
ip.DNSDomain0 = '$dnsdomain' and 
ip.TimeStamp > DATEADD(day, -10, GETDATE()) 
GROUP BY  SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) 
ORDER BY Devices DESC

Boundaries – alter for $query

select 
bound.DisplayName, 
SUBSTRING(bound.value,1,CHARINDEX('-',bound.value) -1) AS LEFTHALF,
SUBSTRING(bound.value,CHARINDEX('-',bound.value) +1 ,100) AS RIGHTHALF 
from vSMS_Boundary as bound 
where 
bound.BoundaryType = '3' and 
bound.GroupCount > 0

Output – email

An email is sent with the following information after running the script

Network – network (always ends with a .1)
Devices – number of devices
DNSDomain – DNS-suffix
IPSubnet – network mask
DefaultGateway – default gateway
DHCPServer – DHCP-server

Parameters

Before running the actual script the following is required to be updated. DNSDomain – what DNS-suffix your clients are reporting as.

#Database params
$ErrorActionPreference = "silentlycontinue"
#Database-server
$datasource = "DBSERVER"
#Database
$database = "CM_DATABASE"
#DNS-Domain
$dnsdomain = 'dns.suffix.se'

#Email params
$EmailParams = @{
To         = 'email'
From       = 'email'
Smtpserver = 'smtp.company.se'
Subject    = "ConfigMgr Client Unknown Networks -  $(Get-Date -Format dd-MMM-yyyy)"
}

Script

#========================================================================
# Created with: Powershell ISE
# Created on:   2017-08-13
# Created by:   NiKa
# Organization:
# Filename:     CM_BoundaryCheck.ps1
#========================================================================

function IsIpAddressInRange {
param(
[string] $ipAddress,
[string] $fromAddress,
[string] $toAddress
)

$ip = [system.net.ipaddress]::Parse($ipAddress).GetAddressBytes()
[array]::Reverse($ip)
$ip = [system.BitConverter]::ToUInt32($ip, 0)

$from = [system.net.ipaddress]::Parse($fromAddress).GetAddressBytes()
[array]::Reverse($from)
$from = [system.BitConverter]::ToUInt32($from, 0)

$to = [system.net.ipaddress]::Parse($toAddress).GetAddressBytes()
[array]::Reverse($to)
$to = [system.BitConverter]::ToUInt32($to, 0)

$from -le $ip -and $ip -le $to
}

###### Parameters #################

#Database params
$ErrorActionPreference = "silentlycontinue"
#Database-server
$datasource = "DBSERVER"
#Database
$database = "CM_DATABASE"
#DNS-Domain
$dnsdomain = 'dns.suffix.se'

#Email params
$EmailParams = @{
To         = 'email'
From       = 'email'
Smtpserver = 'smtp.company.se'
Subject    = "ConfigMgr Client Unknown Networks -  $(Get-Date -Format dd-MMM-yyyy)"
}
###### Parameters #################

#### Retrieve client networks
$netquery = "select SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) + '.1' As IP, COUNT(*) as Devices from v_Network_DATA_Serialized as ip where ip.IPAddress0 IS NOT NULL and ip.IPSubnet0 != '64'  and ip.IPSubnet0 != '128' and ip.IPSubnet0 = '255.255.255.0' and ip.DNSDomain0 IS NOT NULL and ip.DNSDomain0 = '$dnsdomain' and ip.TimeStamp > DATEADD(day, -10, GETDATE()) GROUP BY  SUBSTRING(ip.IPAddress0, 1, LEN(ip.IPAddress0) - CHARINDEX('.',REVERSE(ip.IPAddress0))) ORDER BY Devices DESC"

$networks= Invoke-Sqlcmd -Query $netquery -server $datasource -Database $database

#### Retrieve boundaries
$query = "select bound.DisplayName, SUBSTRING(bound.value,1,CHARINDEX('-',bound.value) -1) AS LEFTHALF,SUBSTRING(bound.value,CHARINDEX('-',bound.value) +1 ,100) AS RIGHTHALF from vSMS_Boundary as bound where bound.BoundaryType = '3' and bound.GroupCount > 0"

$iprange = Invoke-Sqlcmd -Query $query -server $datasource -Database $database

#### Check if IP-address are within boundaries
$report=@()
foreach ($net in $networks) {
if (!($net.ip -eq '192.168.1.1' -or $net.ip -eq '0.0.0.1'  -or $net.ip -eq '10.10.0.1' -or $net.ip -eq '172.16.0.1' -or $net.ip -eq '169.254.43.1' -or $net.ip -eq '169.254.36.1' -or $net.ip -eq '192.168.0.1' -or $net.ip -eq '10.0.100.1')) {
$i = 0
$J = $iprange.count
$boundaryfound = $false
do {
#$iprange[$i].displayname
if (IsIpAddressInRange $net.ip $iprange[$i].LEFTHALF $iprange[$i].RIGHTHALF)
{

$boundaryfound = $true
}
$i++
} until ($i -gt $j)
if ($boundaryfound -eq $false)
{
#write-host "Network: $($net.ip) - Devices: $($net.Devices)"

#Retrieve information about network
$devquery = "select distinct DNSDomain0,IPSubnet0,DefaultIPGateway0,DHCPServer0 from v_Network_DATA_Serialized as ip
where ip.IPAddress0 IS NOT NULL
and ip.IPSubnet0 != '64'
and ip.TimeStamp > DATEADD(day, -10, GETDATE())
and ip.IPaddress0 like '$($($net.ip) -replace ".$")%'"
$devices= Invoke-Sqlcmd -Query $devquery -server $datasource -Database $database
$report += New-Object psobject -Property @{Network=$($net.ip);Devices=$($net.devices);DNSDomain=$($devices.DNSDomain0);IPSubnet=$($devices.IPSubnet0);DefaultGateway=$($devices.DefaultIPGateway0);DHCPServer=$($devices.DHCPServer0)}
}
}
}

if ($report -ne $null) {

#$report

#Generate email

$style = @"
<style>
body {
color:#333333;
font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;}
}
h1 {
text-align:center;
}
h2 {
border-top:1px solid #666666;
}
table {
border-collapse: collapse;
font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;
}
th {
font-size: 10pt;
text-align: left;
padding-top: 5px;
padding-bottom: 4px;
background-color: #1FE093;
color: #ffffff;
}
td {
font-size: 8pt;
border: 1px solid #1FE093;
padding: 3px 7px 2px 7px;
}
</style>

"@

$Properties = @(
'Network',
'Devices',
'DNSDomain',
'IPSubnet',
'DefaultGateway',
'DHCPServer'
)

$body = $report |
Select-Object -Property $Properties|
ConvertTo-Html -Head $style -Body "
<H3>Devices from unknown networks ($($results.Count))</H3>

" |
Out-String

Send-MailMessage @EmailParams -Body $Body -BodyAsHtml

}

Leave a Reply

Your email address will not be published. Required fields are marked *