Office365 deployment best practice

I will certainly check that out. I do find application control to be very obscure and have had issues trying to make it work correctly and it is not very well documented or at least I have not been able to find good documentation. I will look through your threads to see what you have done. If it is that simple I will be impressed!

Thanks!

Application Control can "allow" applications, but does it reduce the UTM overhead for the traffic? Per Microsoft, for best performance, Office Apps like Skype/Teams/OneDrive/SharePoint/Exchange need direct access to the resource without any inspection, QOS or other rules hindering performance. Office is to be considered a trusted source just like internal servers when connecting to your resources over the internet.

Most of this effort for me has been to maximize performance of Office products.

Application Control has become almost unusable for me on the SG430, it takes minutes to pull up the list and sometimes longer to filter. Plus, it doesn't always have what I need or doesn't work in all instances.

Exact same thought on the application control option for me. Does it impact further or does it reduce inspection overall? I have not been able to find any granular documentation on how it is implemented and whether or not it is updated on a regular basis as part of the signature updates. If it is only updated via firmware updates then those are becoming fewer and farther between of late. It would be great to know this info and would in fact be a good selling point for Sophos UTM devices. There is also a chance it is never or very randomly updated. 

I am on an SG320 and it is very sluggish on the management screens too and getting worse with each upgrade. I also have had to do a lot of tweaks to keep my CPU spikes to an acceptable level so there could definitely be an impact from O365 rule sets no matter how they are added/implemented.

My 5 year maintenance/updates agreement is up on my SG320 so I am just in the process of evaluating whether to upgrade to a new Sophos device or should I bail and go with something different. The past decade with Astaro/Sophos has been a bit of a love/hate relationship and mainly around circumstances just like this. 

I just tried to add Office with Application Control, and the list came up but when I tried to filter on "Office", it show it had 8 results, but after 10 minutes it still hadn't finished trying to display them. Unusable.

This is what scares me. I get higher rates of CPU usage as is currently. This spike will only last a few minutes and settle down and traffic seems to flow through ok but adding another application control or 600 network definitions could break it.

Guys, in AppCtrl, an "Allow" rule should be considered as an "Exception" for a subsequent "Block" rule.  It's really only used to block applications.  I don't have many active AppCtrl rules at any client site.

Cheers - Bob

Hi Tim,

I'm struggling with your script,

Where to put the following things

        UTM = '10.8.4.1';

        URL = '10.8.4.1:4444/api;

        KEY = 'LeqBiAUukVMlZgfdJsoHKwuLHRybVYZs';

When I start yout script it asks :  Supply values for the following parameters: utm

So I put in

 10.8.4.1:4444/api

10.8.4.1:4444

10.8.4.1

https://10.8.4.1:4444

Nothing works. I get a message like.

PS C:\WINDOWS\system32> #Retrieve the existing list of MS subnets from Sophos

PS C:\WINDOWS\system32> $utmNetList = Invoke-RestMethod -Uri $networkURI -Method Get -Headers $headers

Invoke-RestMethod : Invalid URI: The hostname could not be parsed.

At line:1 char:15

+ ... tmNetList = Invoke-RestMethod -Uri $networkURI -Method Get -Headers $ ...

+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Invoke-RestMethod], UriFormatException

    + FullyQualifiedErrorId : System.UriFormatException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

PS C:\WINDOWS\system32> $msNetList = $utmNetList | where {$_.name -like 'MS-*/*' -and $_.comment -like 'Microsoft*PS1'}

Hope you can give me some guidance.

Best Regards,

Hans Schenkelaars

You have to define the values as a hashtable then pass it to the script
$utm = @{UTM = 'SophosDeviceName';
                 URL = 'SophosDeviceName.company.com:4444/api;
                KEY = 'apiKeyFromSophosDevice'}

I designed this to have a calling script with an array of hashtables for all of my UTM's so I am only storing my api keys in one file. There are other scripts I run to update various UTM configurations.

Hi Tim,

Thx for your quick response..

I created the 2 files masterscript.ps1 and runscript.ps1. Had to make some changes to PowerShell to allow running scripts but that works.\

When running the script I got an error regarding plain http traffic, so I changed the url in the masterscript to https://10.8.4.1:4444

.\masterscript.ps1 -script .\runscript.ps1
Invoke-RestMethod : The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
At D:\runscript.ps1:59 char:15
+ ... tmNetList = Invoke-RestMethod -Uri $networkURI -Method Get -Headers $ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

Invoke-RestMethod : The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
At D:\runscript.ps1:63 char:21
+ ... ptionList = Invoke-RestMethod -Uri $exceptionUri -Method Get -Headers ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

Out-File : Could not find a part of the path 'C:\scripts\sophos\endpoints_clientid_latestversion.txt'.
At D:\runscript.ps1:75 char:41
+ @($clientRequestId, $lastVersion) | Out-File $datapath
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OpenError: (:) [Out-File], DirectoryNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

Invoke-RestMethod : Invalid Instance string specified or ClientRequestId not specified in the Url.
At D:\runscript.ps1:79 char:12
+ $version = Invoke-RestMethod -Uri ($ws + "/version/O365Worldwide?clie ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
Office 365 worldwide commercial service instance endpoints are up-to-date

After Changing This I get an error regarding SSL/TLS.

.\masterscript.ps1 -script .\runscript.ps1
Invoke-RestMethod : The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
At D:\runscript.ps1:59 char:15
+ ... tmNetList = Invoke-RestMethod -Uri $networkURI -Method Get -Headers $ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

Invoke-RestMethod : The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
At D:\runscript.ps1:63 char:21
+ ... ptionList = Invoke-RestMethod -Uri $exceptionUri -Method Get -Headers ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

Out-File : Could not find a part of the path 'C:\scripts\sophos\endpoints_clientid_latestversion.txt'.
At D:\runscript.ps1:75 char:41
+ @($clientRequestId, $lastVersion) | Out-File $datapath
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OpenError: (:) [Out-File], DirectoryNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

Invoke-RestMethod : Invalid Instance string specified or ClientRequestId not specified in the Url.
At D:\runscript.ps1:79 char:12
+ $version = Invoke-RestMethod -Uri ($ws + "/version/O365Worldwide?clie ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
Office 365 worldwide commercial service instance endpoints are up-to-date

Maybe it's a small setting because I used both of your examples and I'm not sure what to change. Do you have an idea ?

We really need this to work hope you can help..  :-)

My Current Masterscript.ps1

Param([parameter(Mandatory=$true)]$script)
$utmList = @(
@{UTM = '10.8.4.1';
URL = 'https://10.8.4.1:4444/api'
KEY = 'LeqBiAUukVMlZguiJsoHKwuLHRybVYZs'})
foreach ($utm in $utmList)
{
#Write-Host $utm.URL
& $script $utm
}

My Current Runscript.ps1

# 2018-04-3 TJB

<#
This script downloads the xml IP list from Microsoft, adds/removes Sophos Network Objects and creates/modifies a Group of Networks.
Requirements:
Sophos API Enabled
Local Sophos account with admin privelages configures with an API Key

Input Parameter: $UTM
Script expects a hashtable to be passed in the following format:
@{UTM = '10.8.4.1';
URL = '10.8.4.1:4444/api;
KEY = 'LeqBiAUukVMlZguiJsoHKwuLHRybVYZs';
DBG = $true/$false - activates pause in script}
#>
Param([parameter(Mandatory=$true)]$utm)

$ws = "https://endpoints.office.com" # webservice root URL
$datapath = "C:\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 = 'tenantID' # API Tenant ID
$comment = 'Microsoft URL | ' + (Get-Date).ToString("yyyy-MM-dd") + ' PS1'
$apiURL = $utm.URL
$exceptionUri = $apiURL + '/objects/http/exception/'
$networkURI = $apiURL + '/objects/network/network/'
$groupURI = $apiURL + '/objects/network/group/'
$resultCSV = 'C:\scripts\sophos\resultList.csv'
$exceptionUrls = @() # processed list of URLs for Sophos Formatting
$msGroup = @() #Sophos Network Group that will contain all of the network object 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 = 'mailserver';
subject = 'Microosft IPv4 Address Update for ' + $utm.UTM;
to = 'h.schenkelaars@hcc-online.nl';
from = 'utm@hcc-online.nl'}

#Must have corresponding account configured with token on UTM
$token = $utm.KEY
$tokenBase64 = [Convert]::ToBase64String([System.Text.Encoding]::Default.GetBytes("token:" + $token))

#Common headers required by Sophos API
$headers = @{}
$headers.add("Authorization",'Basic ' + $tokenBase64)
$headers.add("Content-Type", "application/json")
$headers.add("Accept", "application/json")

#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
$utmNetList = Invoke-RestMethod -Uri $networkURI -Method Get -Headers $headers
$msNetList = $utmNetList | where {$_.name -like 'MS-*/*' -and $_.comment -like 'Microsoft*PS1'}

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

# 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
}

# call version method to check the latest version, and pull new data if version number is different
$version = Invoke-RestMethod -Uri ($ws + "/version/O365Worldwide?clientRequestId=" + $clientRequestId)

if ($version.latest -ge $lastVersion) {
Write-Host "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/O365Worldwide?clientRequestId=" + $clientRequestId + "&TenantName=" + $tenantName)

# filter results for Allow and Optimize endpoints, and transform these into custom objects with port and category
$flatUrls = $endpointSets | ForEach-Object {
$endpointSet = $_
$allowUrls = $(if ($endpointSet.allowUrls.Count -gt 0) { $endpointSet.allowUrls } else { @() })
$optimizeUrls = $(if ($endpointSet.optimizeUrls.Count -gt 0) { $endpointSet.optimizeUrls } else { @() })

$allowUrlCustomObjects = $allowUrls | ForEach-Object {
[PSCustomObject]@{
category = "Allow";
url = $_;
# Allow URLs should permit traffic across both Allow and Optimize ports
tcpPorts = (($endpointSet.allowTcpPorts, $endpointSet.optimizeTcpPorts) | Where-Object { $_ -ne $null }) -join ",";
udpPorts = (($endpointSet.allowUdpPorts, $endpointSet.optimizeUdpPorts) | Where-Object { $_ -ne $null }) -join ",";
}
}
$optimizeUrlCustomObjects = $optimizeUrls | ForEach-Object {
[PSCustomObject]@{
category = "Optimize";
url = $_;
tcpPorts = $endpointSet.optimizeTcpPorts;
udpPorts = $endpointSet.optimizeUdpPorts;
}
}
$allowUrlCustomObjects, $optimizeUrlCustomObjects
}

$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 '*.*' }

$allowIpCustomObjects = @()
if ($endpointSet.allowTcpPorts -or $endpointSet.allowUdpPorts) {
$allowIpCustomObjects = $ip4s | ForEach-Object {
[PSCustomObject]@{
category = "Allow";
ip = $_;
tcpPorts = $endpointSet.allowTcpPorts;
udpPorts = $endpointSet.allowUdpPorts;
}
}
}
$optimizeIpCustomObjects = @()
if ($endpointSet.optimizeTcpPorts -or $endpointSet.optimizeUdpPorts) {
$optimizeIpCustomObjects = $ip4s | ForEach-Object {
[PSCustomObject]@{
category = "Optimize";
ip = $_;
tcpPorts = $endpointSet.optimizeTcpPorts;
udpPorts = $endpointSet.optimizeUdpPorts;
}
}
}
$allowIpCustomObjects, $optimizeIpCustomObjects
}

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

# 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 could 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 = @('av',
'content_removal',
'user_auth',
'extensions',
'url_filter',
'contenttype_blacklist',
'cache',
'log_access',
'log_blocked',
'ssl_scanning',
'check_max_download',
'patience'
)

#$msUrlList = ConvertTo-Json $exceptionUrls

$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 "Office 365 worldwide commercial service instance endpoints are up-to-date"
}

Couple of thoughts:

1. You are using an IP Address to access the device with HTTPS. If you access the server this way in a browser, do you get certificate errors? I have a domain issued cert on my Sophos devices and access it using the FQDN. When I try to access it via IP address it fails with the same error. I don't know if the API allows http, but you could try.

2. You can play with the TLS levels in the script in this line -  $AllProtocols = [System.Net.SecurityProtocolType]'Tls,Tls11,Tls12'

3. You might want to change this path statement, it doesn't appear to be correct - $datapath = "C:\scripts\sophos\endpoints_clientid_latestversion.txt" # path where client ID and latest version number will be stored

Since most of the issues appear to revolve around communication with the Sophos, we can simplify the script so you can load it in the Powershell ISE and test basic communication. If the following doesn't work you will need to troubleshoot your API config. All it does is retrieve your current list of network and stores it in the $networkHosts variable.