Office365 deployment best practice

Question

We are in the process of migrating to Office365. As part of the process, the networked is evaluated and the first recommendation by Microsoft is to remove any proxies from the path between the user and Office365. The problem with this is that MS has a ton of IP Address ranges and URLs. 
 The primary guidelines are: 
 
 Use a proxy PAC files to send all the MS URLs direct. 
 Create policies on the firewall to allow all IP ranges and URLs 
 
 https://support.office.com/en-us/article/managing-office-365-endpoints-99cab9d4-ef59-4207-9f2b-3728eb46bf9a?ui=en-US&rs=en-US&ad=US 
 Really good overview of their philosophy from Ignite: 
 https://www.youtube.com/watch?v=19a8s90HboQ&feature=youtu.be 
 Here is the entire IP/URL List in XML format: https://support.content.office.net/en-us/static/O365IPAddresses.xml 
 The problem I see is managing the list of IP Addresses and URLs. The list is long and changes somewhat frequently, so it's not just a matter of doing it once, you have to maintain it. As far as I know, there is no Network object in the UTM that let's you drop a list of subnets. That wouldn't be bad. But it appears that each subnet has to be created as a network definition and them maybe added to a group. But some places in Sophos do not accept groups, so then each subnet would have to be dragged one at a time in the interface. Again tedious to implement and more tedious to maintain. 
 I could use the API, but that would have to be run against each UTM. This will take a bit of work to implement, but may be the best solution long term. 
 Has anyone discovered an easy solution to keeping this type of thing up to date?

GP Admin GP Admin · Answer

This is what's worked for us as of 5/20/19. I combined the logic from the scripts that update network definitions and URL scanning exceptions, and made some minor modifications to process the json formatted data and allow the script to run through our proxy. The variables holding the Sophos API URL and token etc just need to be populated. I've also modified the script to run without parameters.

<#

This script downloads the IP list from the Microsoft web service, adds/removes Sophos Network Objects and creates/modifies a Group of Networks and SSL scanning exceptions.
Requirements:
Sophos API Enabled
Local Sophos account with admin privelages configured with an API Key

Input Parameter: $UTM
Script expects a hashtable to be passed in the following format:
@{UTM = '';
URL = 'https://<sophos IP>:4444/api';
KEY = '';
DBG = $true/$false - activates pause in script}

$utmvalues = @{"UTM" = ""; "URL" = "https://<sophos IP>:4444/api"; "KEY" = ""}

#>

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# might be needed to resolve 'could not establish trust relationship for the SSL/TLS secure channel' errors when attempting to authenticate to the UTM

if (-not ([System.Management.Automation.PSTypeName]'ServerCertificateValidationCallback').Type)
{
$certCallback = @"
using System;
using System.Net;
using System.Net.Security;
using System.Security.Cryptography.X509Certificates;
public class ServerCertificateValidationCallback
{
public static void Ignore()
{
if(ServicePointManager.ServerCertificateValidationCallback ==null)
{
ServicePointManager.ServerCertificateValidationCallback +=
delegate
(
Object obj,
X509Certificate certificate,
X509Chain chain,
SslPolicyErrors errors
)
{
return true;
};
}
}
}
"@
Add-Type $certCallback
}
[ServerCertificateValidationCallback]::Ignore()

# run without params
# Param([parameter(Mandatory=$true)]$utm)

$ws = "https://endpoints.office.com" # webservice root URL
$datapath = "C:\users\<username>\desktop\scripts\sophos\endpoints_clientid_latestversion.txt" # path where client ID and latest version number will be stored
$exceptionUrlList = @() # Sophos Exception list
# Personalize the Tenant name.
$tenantName = '' # API Tenant ID
$comment = 'Microsoft URL | ' + (Get-Date).ToString("yyyy-MM-dd") + ' PS1'
# $apiURL = $utm.URL
$apiURL = ''
$exceptionUri = $apiURL + '/objects/http/exception/'
$networkURI = $apiURL + '/objects/network/network/'
$groupURI = $apiURL + '/objects/network/group/'
$resultCSV = 'C:\users\<username>\desktop\scripts\sophos\resultList.csv'
$exceptionUrls = @() # processed list of URLs for Sophos Formatting
$msGroup = @() #Sophos Network Group that will contain all of the network objects created
$utmNetList = @() #All Networks retrieved from the UTM
$msNetList = @() #UTM Networks filtered for MS-
$ipList = @() #IP Addresses downloaded from Microsoft XML
$ipNetList = @() #IP Addresses parsed into Network format
$resultList = @() #Dispositon of networks for notification - Personalize the mail server and recipient/sender information
$emailHeader = @{smtpserver = 'smtp.company.com';
subject = 'Microsoft IPv4 Address Update for the Sophos UTM';
to = '<user>@company.com';
from = '<user>@company.com'}

# Must have corresponding account configured with token on UTM
# $token = $utm.KEY

$token = ''
$tokenBase64 = [Convert]::ToBase64String([System.Text.Encoding]::Default.GetBytes("token:" + $token))

# Common headers required by Sophos API
# $headers = @{}

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"

$headers.add("Accept", "application/json")
$headers.add("Content-Type", "application/json")
$headers.add("Authorization",'Basic ' + $tokenBase64)

# Sets the TLS level to match Sophos
$AllProtocols = [System.Net.SecurityProtocolType]'Tls,Tls11,Tls12'
[System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols

# Retrieve the existing list of MS subnets from Sophos
write-host -ForegroundColor Cyan Retrieving the existing list of MS subnets from Sophos
$utmNetList = Invoke-RestMethod -Uri $networkURI -Method Get -Headers $headers
$msNetList = $utmNetList | where {$_.comment -like 'Microsoft URL*' -and $_.name -like 'MS-*'}

# Retrieve the existing list of HTTP Exceptions from Sophos
write-host -ForegroundColor Cyan Retrieving the existing list of HTTP Exceptions from Sophos
$utmExceptionList = Invoke-RestMethod -Uri $exceptionUri -Method Get -Headers $headers
$msExceptionList = $utmExceptionList | where {$_.comment -like 'Microsoft*PS1*' -and $_.name -like 'Microsoft Office365 URL*'}

# fetch client ID and version if data file exists; otherwise create new file
if (Test-Path $datapath) {
$content = Get-Content $datapath
$clientRequestId = $content[0]
$lastVersion = $content[1]
} else {
$clientRequestId = [GUID]::NewGuid().Guid
$lastVersion = "0000000000"
@($clientRequestId, $lastVersion) | Out-File $datapath
}

# authentication to the proxy server

$Wcl = new-object System.Net.WebClient
$Wcl.Headers.Add(“user-agent”, “PowerShell Script”)
$Wcl.Proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials

# call version method to check the latest version, and pull new data if version number is different
# tenant name unnecessary

$version = Invoke-RestMethod -Uri ($ws + "/version/Worldwide?clientRequestId=" + $clientRequestId)

if ($version.latest -ge $lastVersion) {
Write-Host -ForegroundColor Cyan "New version of Office 365 worldwide commercial service instance endpoints detected"

# write the new version number to the data file
@($clientRequestId, $version.latest) | Out-File $datapath

# invoke endpoints method to get the new data
$endpointSets = Invoke-RestMethod -Uri ($ws + "/endpoints/Worldwide?clientRequestId=" + $clientRequestId)

# filter results for Allow and Optimize endpoints, and transform these into custom objects with port and category

$flatUrls = $endpointSets | ForEach-Object {
$endpointSet = $_
$urls = $(if ($endpointSet.urls.Count -gt 0) { $endpointSet.urls } else { @() })
$urlCustomObjects = @()
if ($endpointSet.category -in ("Allow", "Optimize")) {
$urlCustomObjects = $urls | ForEach-Object {
[PSCustomObject]@{
category = $endpointSet.category;
url = $_;
tcpPorts = $endpointSet.tcpPorts;
udpPorts = $endpointSet.udpPorts;
}
}
}
$urlCustomObjects
}

$flatIps = $endpointSets | ForEach-Object {
$endpointSet = $_
$ips = $(if ($endpointSet.ips.Count -gt 0) { $endpointSet.ips } else { @() })
# IPv4 strings have dots while IPv6 strings have colons
$ip4s = $ips | Where-Object { $_ -like '*.*' }

$ipCustomObjects = @()
if ($endpointSet.category -in ("Allow", "Optimize")) {
$ipCustomObjects = $ip4s | ForEach-Object {
[PSCustomObject]@{
category = $endpointSet.category;
ip = $_;
tcpPorts = $endpointSet.tcpPorts;
udpPorts = $endpointSet.udpPorts;
}
}
}
$ipCustomObjects
}

Write-Output "IPV4 Firewall IP Address Ranges"
($flatIps.ip | Sort-Object -Unique) -join "," | Out-String

Write-Output "URLs for Proxy Server"
($flatUrls.url | Sort-Object -Unique) -join "," | Out-String

# Format IP List into Network hashtable

$ipList = ($flatIps.ip | Sort-Object -Unique) -join "," | Out-String
$iplist = $iplist -replace "`n|`r",""

# initialize ipNetList as an array

$ipNetList = @()

$ipList.split(',') | Foreach-object {
$ipaddress = $_.split('/')[0]
$netmask = $_.split('/')[1]
$name = 'MS-' + $_.split('/')[0]
$subnet = @{address = $ipaddress;
address6 = "";
comment = $comment;
interface = "";
name = $name;
netmask = $netmask;
netmask6 = "0";
resolved = $true;
resolved6 = $false}
$subnet = $subnet | convertto-json | convertfrom-json
$ipNetlist += $subnet

}

# Add new subnets to Sophos UTM

$resultList = @()

foreach ($ipNet in $ipNetList)
{
$action = ""
$ref = ""
$result = @()
if ($msNetList -match $ipNet.name)
{
Write-Host -ForegroundColor Yellow $ipNet.name already exists
$action = 'Exists'
$ref = ($msNetList | where {$_.name -eq $ipNet.name})._ref
}
else
{
Write-Host -foregroundcolor Yellow Need to create network object $ipNet.name on Sophos UTM
Start-Sleep -Seconds 3
$result = Invoke-RestMethod -Uri $networkURI -Method Post -Headers $headers -Body (ConvertTo-Json $ipNet)
if ($result.name -eq $ipNet.name)
{
Write-Host -ForegroundColor Green Subnet created successfully
$action = 'Added'
$ref = $result._ref
}
else
{
$action = 'AddFailed'
}
}

$resultList += @{action = $action; network = $ipNet.name; _ref = $ref}

#if ($utm.DBG){pause}
}

# Old Subnets to remove from Sophos UTM

foreach ($msNet in $msNetList)
{
$action = ""
if ($ipNetList -match $msNet.name)
{
Write-Host -ForegroundColor Yellow $msNet.name is still valid
}
else
{
Write-Host -ForegroundColor Red Need to remove network object $msNet.name from Sophos UTM
$action = 'Remove'
$resultList += @{action = $action; network = $msNet.name; _ref = $msNet._ref}
}
#if ($utm.DBG){pause}
}

# Update "Microsoft IPv4 Subnets" group if any subnets added or removed

if (($resultList | where {$_.action -eq "Added"}).count -gt 0 -or ($resultList | where {$_.action -eq "Remove"}).count -gt 0)
{
$result = @()
$msGrpMembers = @()
$grpBody = @{}
$networkGroups = Invoke-RestMethod -Uri $groupURI -Method Get -Headers $headers
$msGroup = $networkGroups | where {$_.name -eq 'Microsoft IPv4 Subnets'}
if ($msGroup.name -ne 'Microsoft IPv4 Subnets')
{
$grpMethod = 'Post'
$msGroupUri = $groupURI
}
else
{
$grpMethod = 'Patch'
$msGroupUri = $groupURI + $msGroup._ref
}
foreach ($result in $resultList)
{
if ($result.action -ne 'Remove')
{
$msGrpMembers += $result._ref
}
}
$grpBody = @{comment = $comment;
name = "Microsoft IPv4 Subnets";
members = $msGrpMembers}
Invoke-RestMethod -Uri $msGroupUri -Method $grpMethod -Headers $headers -Body (ConvertTo-Json $grpBody)
$resultList.ForEach({[PSCustomObject]$_}) | Export-Csv $resultCSV -Force -NoTypeInformation
$emailHeader.Add('Body', 'Result file attached')
$emailHeader.Add('Attachments', $resultCSV)
#if($utm.DBG){pause}
}

else

{
$emailHeader.Add('Body','No IP address changes made this run')
}

# Remove unused subnets from Sophos UTM

if (($resultList | where {$_.action -eq "Remove"}).count -gt 0)
{
$result = @()
$msGrpMembers = @()
$grpBody = @{}
$headers.add("X-Restd-Err-Ack", "all")
foreach ($result in $resultList)
{
if ($result.action -eq 'Remove')
{
Write-Host -ForegroundColor Red Deleting Network object $result.network
Start-Sleep -Seconds 3
$delNetURI = $networkURI + $result._ref
Invoke-RestMethod -Uri $delNetURI -Method Delete -Headers $headers -Body (ConvertTo-Json $ipNet)
}
}
}

# Sends e-mail with list of ranges and action taken

Send-MailMessage @emailHeader

# Modifies the URLs for Sophos and populates the $exceptionUrls array

foreach ($url in $flatUrls.url){
$newUrl = ''
if ($url -like '`*-*') {
$newUrl = ($url).Replace('.','\.').Replace('*-','^https?://companyname-') # Replace with specific company name - might use the TenantID variable
} elseif ($url -like '`*.*'){
$newUrl = ($url).Replace('.','\.').Replace('*\.','^https?://[^.]*\.')
} else {
$newUrl = '^https?://' + ($url -replace '\.','\.')
}
$exceptionUrls = $exceptionUrls += $newUrl
}

if ($msExceptionList.name.count -eq 0){
$exceptionMethod = 'Post'
$msExceptionUri = $exceptionUri
} elseif ($msExceptionList.name.count -eq 1){
$exceptionMethod = 'Patch'
$msExceptionUri = $exceptionUri + $msExceptionList._ref
}

<#Options for the skiplist
av -- anti-virus
cache -- Caching
certcheck -- Certificate Trust Check
certdate -- Certificate Date Check
check_max_download -- Block by download size
content_removal -- Content removal
contenttype_blacklist -- MIME type blocking
extensions -- Extension blocking
log_access -- Logging Accessed pages
log_blocked -- Logging Blocked pages
patience -- Do not display Download/Scan progress page
ssl_scanning -- SSL Scanning
url_filter -- URL Filter
user_auth -- Authentication
#>
$skipList = @('ssl_scanning')

# $msUrlList = ConvertTo-Json $exceptionUrls

$exceptionBody = @()

$exceptionBody = @{'aaa' = @();
'comment' = $comment;
'domains' = $exceptionUrls;
'endpoints_groups' = @();
'name' = 'Microsoft Office365 URLs';
'networks' = @();
'operator' = 'AND';
'skiplist' = $skipList;
'sp_categories' = @();
'status' = $true;
'tags' = @();
'user_agents' = @()
}

Invoke-RestMethod -Uri $msExceptionUri -Method $exceptionMethod -Headers $headers -Body (ConvertTo-Json $exceptionBody) # TODO Call Send-MailMessage with new endpoints data
}
else {
Write-Host -ForegroundColor Magenta "Office 365 worldwide commercial service instance endpoints are up-to-date"
}

Jonathan Testlab · Answer

Thanks for all the scripts and snippets shared in this thread! Unfortunately these require quite a lot of manual changes in the PowerShell scripts and rely on local files to keep track of changes. In order to automate the task of updating the network definitions and web protection exception I created a PowerShell module which is easy to install and execute. The module SophosEndpoints - https://www.powershellgallery.com/packages/SophosEndpoints is hosted on PowerShell Gallery, so if you have PowerShell 5, or the PowerShellGet module, just open an elevated PowerShell and run the following cmdlet to install the module: 
 Install-Module SophosEndpoints 
 To create or update the networks and exception in UTM run the following cmdlet in PowerShell: 
 Set-EndpointsInUtm - UtmApiUrl " sophos.testlab.live:4444/api " - UtmApiKey " kjAHGansdzyPdsYhmILKgOWsh " - TenantName testlab - LogFilePath " C:\Set-EndpointsInUtm.log " 
 You have to adapt the parameters according to your environment. 
 You can check out the source code on GitHub - https://github.com/testlablive/SophosEndpoints (subfolder SophosEndpoints). 
 If you want to automate this like I did, you can follow my instructions on my blog https://www.testlab.live/auto-update-sophos-utm-with-microsoft-endpoints