Function Test-SameSubnet {
param (
[parameter(Mandatory=$true)]
[Net.IPAddress]
$ip1,

[parameter(Mandatory=$true)]
[Net.IPAddress]
$ip2,

[parameter()]
[alias("SubnetMask")]
[Net.IPAddress]
$mask ="255.255.255.0"
)

if (($ip1.address -band $mask.address) -eq ($ip2.address -band $mask.address)) {$true}
else {$false}

}

Function get-school {
Param (
    [parameter(mandatory=$true)]

    $IP)

        # Check against the IP list (& get working directory - should work with PS2.0 onwards)
        if ($IP -eq '10.69.47.')
        {
            write-host "here"
        }
        else
        {#write-host $IP
        }
        $myfile = 'C:\Users\westadmin\Desktop\temp\CLEO_IP_subnets.xml'
        [xml]$xml = Get-Content $myfile
            #  The following line could do with being updated to use the subnet mask in order to match whether it is within the range.
            #  Use the top function of this file, with a check-subnet 192.168.0.23 192.168.0.0/22 format.
                $child = $xml.selectnodes("/SiteDetails/Site/Network") | where-object {$_.defaultip -eq $IP}

            # If $child is blank, then no hit.  Need to check non/24 networks to scan this.  Search on preceding ranges?
        if (!($child))
            {
$a = 0
$b = 0
   $newip = $null
    $splitip=$ip.split(".")
    $3rd = $splitip[2] -1
 while ($loop -ne $false)
                {
                        # Decrement the last octet by one.


                     $newIP = $splitip[0]+"."+$splitip[1]+"."+$3rd+"."
                    #write-host "New default is" $newip
                    $child = $xml.selectnodes("/SiteDetails/Site/Network") | where-object {$_.defaultip -eq $newip}
                    if ($child)
                        {$loop = $false
                        write-host "Matched" $child.parentnode.id

                        } else {$loop =$true

                        $a++
                        $3rd--
                        if ($a -eq 4){$loop = $false}
                        }
                }
            }
        #set variables
        if (!($child)){$now= "No match, for $IP" ; $now | out-file C:\users\westadmin\Desktop\unmatched.txt  -NoClobber -Append}
        $a = $child.parentnode.id
        $b = $child.name
        $c = $child.parentnode.name
        $d = $child.supported
      #  $b = $child.parentnode.name

        $combined = $a+"-"+$b
        $combined
        $c
        $d

}
add-pssnapin sqlserverprovidersnapin100 -ErrorAction silentlycontinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction silentlycontinue
import-module SQLPS
$dataSource = ".\SophosEC"
$database = "Sophos551"
$sqlCommand = "SELECT IPAddressText, name FROM dbo.ComputersAndDeletedComputers"
write-host "Running SQL..."
$data = Invoke-Sqlcmd -ServerInstance $datasource -Database $database -Query $sqlcommand

$myarray = @()
$x = 0
$i = 0

foreach ($thing in $data)
    {
    if ($thing.IPAddressText -as [ipaddress])
        {
            $ipsplit = $thing.IPAddressText -split "\d{1,3}$"
            $stringIP = [string]$IpSplit
            $return = get-school $stringIP.trim()
            $type=$null

            # Identify P2P or SIMS likely server
            if ($thing.name -like '*server')
                {$type="P2P"}
            if ($thing.name -like '*2k12mis*' -or $thing.name -like '*2k8mis*'){$type="MIS"}
            $myobj = New-Object -TypeName PSObject
             Add-Member -InputObject $myobj -MemberType 'NoteProperty' -Name 'SchoolID' -Value $return[0]
             Add-Member -InputObject $myobj -MemberType 'NoteProperty' -Name 'SchoolName' -Value $return[1]
             Add-Member -InputObject $myobj -MemberType 'NoteProperty' -Name 'Supported' -Value $return[3]
             IF(!([string]::IsNullOrWhiteSpace($type))){Add-Member -InputObject $myobj -MemberType 'NoteProperty' -Name 'Type' -Value $type}

            $myarray +=$myobj
        }
    }
 Write-Host "populate Table"

# Get number of networks from XML - and number of Sites in total

$file = gc 'C:\Users\westadmin\Desktop\CLEO_IP_subnets.xml'
foreach ($a in $file) {if ($a -like '*network*'){$networks = $networks+1}}
foreach ($a in $file) {if ($a -like '*Site *'){$sites = $sites+1}}


$results = $myarray | Group-Object -Property 'SchoolID' | Select-Object name,count, type |Sort-Object name
$results1 = $myarray | Group-Object -Property 'SchoolID' | Select-Object name,count, type |Sort-Object count -Descending

$row=@("")
$row+="<tr><td>SchoolSubnet</td><td>Count</td></tr>"
# Dates are automagically parsed into datetime format
foreach ($item in $results)
    {
        $row+= "<tr><td>"+$item.name+"</td><td>"+$item.count+"</td></tr>"
    }

$row1=@("")
$row1+="<tr><td>SchoolSubnet</td><td>Count</td></tr>"
# Dates are automagically parsed into datetime format
foreach ($item in $results1)
    {
        $row1+= "<tr><td>"+$item.name+"</td><td>"+$item.count+"</td></tr>"
    }


 $body = " Here's the latest stats on the "+$myarray.count+" machines currently categorized by Sophos by subnet.    `n `n Sorted by schoolnumber <table border=1>$row</table> `n And sorted by number of endpoints....<table border=1>$row1</table> <p> This was generated automagically from the EDU-PRD-Sophos server"

$recipients = @("ChrisA <Chris.Adams@btlancashire.co.uk>", "ChrisC <Chris.carr@btlancashire.co.uk>", "AshG <Ash.Green@btlancashire.co.uk>")

write-host "Sending message"
Send-MailMessage -From "NewSophos <EDU-PRD-Sophos@btlancashire.co.uk>" -To $recipients -Subject "Subnets in sophos" -Body $body -Priority High -SmtpServer "smtp.education.btlsl.co.uk" -BodyAsHtml

$myrow=@("")
$myrow+="<tr><td>SchoolSubnet</td><td>P2P/MIS hit</td></tr>"
# Dates are automagically parsed into datetime format
foreach ($bit in $Myarray)
    {
        IF(!([string]::IsNullOrWhiteSpace($bit.type))) {$myrow+= "<tr><td>"+$bit.SchoolID+"</td><td>"+$bit.type+"</td></tr>"}
    }
 $body = " Here's the latest stats on the "+$myrow.count+" P2P/MIS machines currently categorized by Sophos.  There are $networks Networks at $sites Sites.  `n `n <table border=1>$myrow</table> <p> This was generated automagically from the EDU-PRD-Sophos server"

$recipients = @("ChrisA <Chris.Adams@btlancashire.co.uk>", "ChrisC <Chris.carr@btlancashire.co.uk>", "AshG <Ash.Green@btlancashire.co.uk>")

Send-MailMessage -From "NewSophos <EDU-PRD-Sophos@btlancashire.co.uk>" -To $recipients -Subject "P2P & MIS in Sophos" -Body $body -Priority High -SmtpServer "smtp.education.btlsl.co.uk" -BodyAsHtml