Office365 deployment best practice

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:

  1. Use a proxy PAC files to send all the MS URLs direct.
  2. 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?

  • In reply to harrisonpensa:

    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

  • In reply to TimBoggs:

    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

  • In reply to HansSchenkelaars:

    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.

    Param([parameter(Mandatory=$true)]$script)
    $utmList = @(
        @{UTM = 'Sophos1';
          URL = 'sophos1.corp.com:4444/api'
          KEY = 'apikey1'},
        @{UTM = 'Sophos2';
          URL = 'sophos2.corp.com:4444/api'
          KEY = 'apikey2'},
        @{UTM = 'Sophos3';
          URL = 'sophos3.corp.com:4444/api'
          KEY = 'apikey3'})
    foreach ($utm in $utmList)
    {
        #Write-Host $utm.URL
        & $script $utm
    }
     
    You would then call it with:
     
    masterscript.ps1 -script runscript.ps1
  • In reply to TimBoggs:

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

  • In reply to HansSchenkelaars:

    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.

    $AllProtocols = [System.Net.SecurityProtocolType]'Tls,Tls11,Tls12'
    [System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols
    $utm = @{UTM = 'Sophos';
             URL = 'https://10.8.4.1:4444/api' #for https, need fqdn and matching cert on UTM
             KEY = 'LeqBiAUukVMlZguiJsoHKwuLHRybVYZs'}
          
    $apiURL = $utm.URL
    $networkURI = $apiURL + '/objects/network/network/'
    $token = $utm.KEY
    $tokenBase64 = [Convert]::ToBase64String([System.Text.Encoding]::Default.GetBytes("token:" + $token))
    $headers = @{}
    $headers.add("Authorization",'Basic ' + $tokenBase64)
    $headers.add("Content-Type", "application/json")
    $headers.add("Accept", "application/json")
    #This will retrieve all your existing network objects
    $networkHosts = Invoke-RestMethod -Uri $networkURI -Method Get -Headers $headers
    #You can modify the match statement to filter on your naming convention or just run $networkHost from the command line to see what your retrieved.
    $networkHosts | where {$_.name -match 'MS-'} | sort name | ft name,address
  • In reply to TimBoggs:

    Hi Tim,

     

    Thx for your thoughts. I'm almost certain that it has to do with the certificate on the UTM it is a self-Signed. So it gives an "error" when connection to the management interface. This will be the same for the API. Will look into this en fix this and the n try again.

    Have a nice weekend.

    Hans Schenkelaars

  • In reply to HansSchenkelaars:

    Hi Tim,

     

    getting one step closer...

    I uploaded a signed certificate and changed my Local hosts file so i'm able to ping to my UTM "utm.kalorama.nl" 

    I also created the datapath on my system 

    $datapath = "C:\scripts\sophos\endpoints_clientid_latestversion.txt" # path where client ID and latest version number will be stored

     

    Your test script for communications also works. I searched for the letter 'a'

     

    PS D:\> .\test.ps1

    name address
    ---- -------
    Game_huisje 192.168.0.0
    Interval martijn 10.8.4.0
    netwerk - pap&amp;mam 192.168.200.0
    Netwerk Martijn 192.168.1.0
    SUMInternal 192.168.10.0
    Techdata Demo Lan 192.168.10.0
    VLAN 5 192.168.5.0
    Waalboog-LAN 172.21.1.0


    PS D:\>

     

    So communications are working but the main scripts still give me a error.

     

     

    PS D:\> .\masterscript.ps1 -script .\runscript.ps1
    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], WebExc
    eption
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
    Office 365 worldwide commercial service instance endpoints are up-to-date
    PS D:\>

     

    Any idea what, hopefully, the last step will be ?

     

    Thx in advance  :-)

     

     

    These are my scripts..

     

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

     

     

    # 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 = 'https://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"
    }

  • In reply to HansSchenkelaars:

    PS. Also the "endpoints_clientid_latestversion.txt" file is created...

     

    Content of the file :

    cb42e74b-8fac-4464-9417-84c336677745
    0000000000

     

  • In reply to HansSchenkelaars:

    So it's not liking this:

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

    Put a break after this line and when the script halts, check the values of $clientRequestId and $tenantName. If both are correct, add a line above this with something like

    $uri = $ws + "/endpoints/O365Worldwide?clientRequestId=" + $clientRequestId + "&TenantName=" + $tenantName

    Then run the script and check what gets populated in $uri. If it look like a properly formatted uri, try the full string manually.

  • In reply to TimBoggs:

    Hi Tim,

     

    It's been a while, had a vacation.... But still working on a working script.

    I had to change the "O365Worlwide" to "Worldwide" it seems that the URI is working now and the "endpoints_clientid_latestversion.txt" shows this output :

     

    cb42e74b-8fac-4464-9417-84c336677745
    2018080200  --> Seems the URI is working

     

    But when running the changed script I get an error regarding an "empty target category" any idea how to fix this as I'm not a hero in scripting  :-)

     

    Regards,

    Hans Schenkelaars

     

     

    PS D:\> .\masterscript.ps1 -script .\runscript2.ps1
    New version of Office 365 worldwide commercial service instance endpoints detected
    IPV4 Firewall IP Address Ranges


    URLs for Proxy Server


    Invoke-RestMethod : [
    {
    "name": "The web filtering exception may not have an empty target category, user/group, endpoint group, source netw
    ork, target domain, user agent list or tag",
    "rights": "",
    "attrs": [],
    "Oattrs": null,
    "objname": "",
    "del_object": "",
    "ref": "REF_HttExcMicroOfficUrls",
    "msgtype": "HTTP_EXCEPTION_EMPTY_MATCH",
    "format": "The web filtering exception may not have an empty target category, user/group, endpoint group, source ne
    twork, target domain, user agent list or tag",
    "class": "http",
    "type": "exception",
    "perms": "",
    "never_hide": 0,
    "fatal": 1
    }
    ]
    At D:\runscript2.ps1:223 char:5
    + Invoke-RestMethod -Uri $msExceptionUri -Method $exceptionMethod - ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebExc
    eption
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
    PS D:\>

     

    My Current Scrips :

     

     

    Masterscript.ps1

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

     

     

     

    Runscript2.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 = 'https://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/Worldwide?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/Worldwide?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"
    }

  • Hi everyone,

    the solution with XML File is not working anymore. Anyone could adapt this solution with JSON?

    <update>
    Office 365 network IP Addresses and URLs are no longer available in XML format. You should transition to accessing the data in JSON format as described at http://aka.ms/ipurlblog. This was first announced on 2 April 2018 and the XML file was last updated on 22 September 2018.
    </update>
     
    Best regards
    Stephan
  • In reply to StephanG:

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

  • In reply to GP Admin GP Admin:

    this is all for UTM, which i think is the predecessor to XG firewall. 

     

    When i go to create an API key using the XG documentation, i am left with a like 1000 character ppk file, not these small API keys that you are using.

    Also it appears that the latest version of the script has key definitions at the top commented out. (URL, KEY , etc...)

     

    Does this procedure work for an XG firewall?

    is the $token the shorter "SSH2 PUBLIC KEY" that i pasted into the firewall? or is it the longer private key?

     

    i dont have a helper script. just one script (GP admin may 20th version). I populated the variables $datapath (ln 62) $apiURL (ln 68), $TenantName (ln 65), $token (ln 90)

    Another thing to note is that i had to change the "fancy" quotes from fancy to standard, or the script errors out on line 132.

     

    i still get the following errors on manual run on the console. I am pretty sure my API key is working, because when i use the private PPK file in putty to connect to the firewall it connects fine (after using the username admin):

     

    Retrieving the existing list of MS subnets from Sophos
    Invoke-RestMethod :
    Error:404 Page not found
    Check the entered URL.
    At C:\Users\administrator\Desktop\Updateo365IPsSophos.ps1:109 char:15
    + ... tmNetList = Invoke-RestMethod -Uri $networkURI -Method Get -Headers $ ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebExc
    eption
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

     

    Need to create network object MS-104.146.128.0 on Sophos UTM
    Invoke-RestMethod :
    Error:404 Page not found
    Check the entered URL.
    At C:\Users\administrator\Desktop\Updateo365IPsSophos.ps1:247 char:11
    + $result = Invoke-RestMethod -Uri $networkURI -Method Post -Headers $h ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebExc
    eption
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

  • In reply to givemecontrol:

    Using Tims ace work in this thread as a base, we did have to make a few changes to get this working on our systems (windows 8.1 PS to UTM9.5). I post it here to help anyone else who finds this thread.


     

    <#

    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 = 'xxxx.xxxx.xxx';
    URL = 'xxxxx.xxxxx.xxx:4444/api';
    KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;
    DBG = $true/$false - activates pause in script}

    $utmvalues = @{"UTM" = ""; "URL" = ""; "KEY" = ""}
    #>


    @{UTM = 'xxxxxxxxx.xxxxx.xxx';
    URL = 'xxxxx.xxxxx.xxx:4444/.../
    KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
    DBG = $false}


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

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

    $ws = "https://endpoints.office.com" # webservice root URL
    $datapath = "C:\sophosscript\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 = "xxxxx.xxxxx.xxx:4444/api"
    $exceptionUri = $apiURL + '/objects/http/exception/'
    $networkURI = $apiURL + '/objects/network/network/'
    $groupURI = $apiURL + '/objects/network/group/'
    $resultCSV = 'C:\sophosscript\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 = 'xxx.xxxxxx.xxx';
    subject = "Microsoft IPv4 Address Update for the Sophos UTM";
    to = "xxx@xxxx.xxx";
    from = "xxx@xxxxxx.xxx"}

    # 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)
    $headers.add("Authorization", "Basic bG9jYXBpMTAwOmhqZDg0bnhkcDk4aGpvaWJo")

    # 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

    echo "Network URI is"
    echo $networkURI
    $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
    }


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

    It does generate an error at the end of the process, but it still does 99% of the work to get these O365 details into the UTM:

    _locked :
    _ref : REF_HttExcMicroOfficUrls
    _type : http/exception
    aaa : {}
    comment : Microsoft URL | 2019-09-16 PS1
    domains : {^https?://outlook\.office\.com, ^https?://outlook\.office365\.com, ^https?://smtp\.office365\.com,
    ^https?://[^.]*\.outlook\.office\.com...}
    endpoints_groups : {}
    name : Microsoft Office365 URLs
    networks : {}
    operator : AND
    skiplist : {ssl_scanning}
    sp_categories : {}
    status : True
    tags : {}
    user_agents : {}