When you are running a Skype for Business Online environment, CQD allows you to gain insights into the quality of calls via metrics and absolute network measurements from calls meta data.

The context and the actual user experience is collected through the Rate My Call, the RMC data is not currently included in any existing monitoring report, but it has a separate monitoring report. Data is collected in SQL tables that can be accessed by running SQL queries (work on reports in progress).

While we are waiting for the Rate My call Reports from Microsoft there is a way to produce our customized reports by the tools available now from Power BI and in this post we will create these reports by Power BI and use the Cognitive Analysis from Azure to analyze the user sentiment.

This Report shows User Sentiment from RMC data and analyzed with Azure Cognitive services in Power BI

This Report shows the Percentage of Subnets (resolved from Tenant Building Data) where users had complained by rating.

*Since VPN Subnet in CQD shows as 1 IP per subnet its impossible to group them as one Region, in this post we will reverse-resolve them based on the Building data we have, so VPN IPs shows in their respective subnet name.

Requirements:

1- RMC is enabled and Users are giving feedback

2- Dump from the RMC Database (Explained below)

3- Power BI Desktop and Active account

4 - * (Optional) A Microsoft Azure account. Start a free trial or sign in.

5-* (Optional) A access key for Text Analytics. Sign up, then get your key.

• * Only required for the Text Analytics report you can still skip those and use the other reports.

The Reports that we are going to Produce are the following:

1- Reports on who rated the least Stars

2- Reports on who rated with most feed backs

3- Correlation between feedbacks, Stars and subnets.

4- you can be creative with any report from the data given from the RMC.

So lets begin with the RMC Data Dump, Thanks to Jason Shave who wrote this blog on how to extract the RMC data through his script (https://www.powershellgallery.com/packages/CxdCallData/1.1.6.1 )

This Script will dump all your RMC data in CSV files, i found the best way to utilize this script is to do an "initial Seeding/Dump" since the beginning of Rate My Call policy being applied to the users, that will take very long depending on your data, but in my case a 7,000 Users over 3 months took about 14Hrs to complete;

but then  to avoid that going forward you can edit the downloaded module to automatically fill in your credentials and run it as a schedule task every week and get the last 7 days data only which can be less than a few hrs every week,

When you install the CxdCallData it will deploy the files in the folder C:Program FilesWindowsPowerShellModulesCxdCallData1.1.6.0 you can then  edit the line

$global:credential = Get-Credential -Message "Authenticate to Skype for Business Online"

to :

$O365_pass = cat 'C:mypasswordfolderOffice365_securepass.txt' | convertto-securestring
$global:credential = new-object -typename System.Management.Automation.PSCredential -argumentlist "[email protected]",$O365_pass

so after that i created a script to create the list of users based on their RMC policy and collect them in a CSV file and later call that module and pass that file; and my script is then added to the task scheduler to run every 7 days

Import-Module LyncOnlineConnector
$O365_pass = cat 'C:Folder PathOffice365_securepass.txt' | convertto-securestring
$myO365cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "[email protected]",$O365_pass
$filename="C:Folder Path"+(Get-Date -format d).Replace("/","-")+"users.csv"
$CSSession = New-CsOnlineSession -Credential $myo365cred
Import-PSSession $CSSession -AllowClobber
Get-CsOnlineUser | ? {$_.Clientpolicy -match "RateMyCallPolicy"} | select-object UserPrincipalName | Export-Csv $filename
Remove-PSSession $CSSession

Get-CxdCallData -ReportSavePath c:SfBReports -NumberOfDaysToSearch 7 -CsvFileWithUsers $filename

Below is the Export of the Task Scheduler in XML

now as you see it collects all reports to c:SfBReports; you can only copy the SFB-UserFeedback-YYYY-MM-DD HH-MM-SS.CSV files to a folder lets call it c:SfBReportsRMC

now we will need to aggregate all the data in one file; so what we will do is using Excel queries run the following :

In the Advanced editor paste the following Query

let
Source = Folder.Files("C:FOLDER PATHReportRMC"),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Content", type binary}}),
Content = #"Changed Type"[Content],
#"Converted to Table" = Table.FromList(Content, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

Then do the following steps:

Select the last step from the Applied steps and expand the drop down button.

Click on OK

Select the DiagID Column and then from Home>Remove Rows>Remove Duplicates

Rename The query to RMC and then Close and Load

Now you will get a Concatenated Excel Sheet of all the RMC User feedback reports in CSV from the Specified folder; make sure you only have those reports and nothing else in the folder, you can keep adding the reports there and Refresh the Excel sheet query to aggregate all files and build the new records.

After we had all results aggregated we will need to add some information that will help us build our reports with relation to our Network.

first we will add the functions that can calculate subnets in the Excel sheet, start by pressing ALT+F11

then Insert new Module as Shown here:

Then paste the following functions

' Copyright 2010-2017 Thomas Rohmer-Kretz

' This program is free software: you can redistribute it and/or modify
' it under the terms of the GNU General Public License as published by
' the Free Software Foundation, either version 3 of the License, or
' (at your option) any later version.

' This program is distributed in the hope that it will be useful,
' but WITHOUT ANY WARRANTY; without even the implied warranty of
' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
' GNU General Public License for more details.

' You should have received a copy of the GNU General Public License
' along with this program. If not, see .

' http://trk.free.fr/ipcalc/

' Visual Basic for Excel

'==============================================
' IP v4
'==============================================

'----------------------------------------------
' IpIsValid
'----------------------------------------------
' Returns true if an ip address is formated exactly as it should be:
' no space, no extra zero, no incorrect value
Function IpIsValid(ByVal ip As String) As Boolean
 IpIsValid = (IpBinToStr(IpStrToBin(ip)) = ip)
End Function

'----------------------------------------------
' IpStrToBin
'----------------------------------------------
' Converts a text IP address to binary
' example:
' IpStrToBin("1.2.3.4") returns 16909060
Function IpStrToBin(ByVal ip As String) As Double
 Dim pos As Integer
 ip = ip + "."
 IpStrToBin = 0
 While ip  ""
 pos = InStr(ip, ".")
 IpStrToBin = IpStrToBin * 256 + Val(Left(ip, pos - 1))
 ip = Mid(ip, pos + 1)
 Wend
End Function

'----------------------------------------------
' IpBinToStr
'----------------------------------------------
' Converts a binary IP address to text
' example:
' IpBinToStr(16909060) returns "1.2.3.4"
Function IpBinToStr(ByVal ip As Double) As String
 Dim divEnt As Double
 Dim i As Integer
 i = 0
 IpBinToStr = ""
 While i 
 If IpBinToStr  "" Then IpBinToStr = "." + IpBinToStr
 divEnt = Int(ip / 256)
 IpBinToStr = Format(ip - (divEnt * 256)) + IpBinToStr
 ip = divEnt
 i = i + 1
 Wend
End Function

'----------------------------------------------
' IpSubnetToBin
'----------------------------------------------
' Converts a subnet to binary
' This function is similar to IpStrToBin but ignores the host part of the address
' example:
' IpSubnetToBin("1.2.3.4/24") returns 16909056
' IpSubnetToBin("1.2.3.0/24") returns 16909056
Function IpSubnetToBin(ByVal ip As String) As Double
 Dim l As Integer
 Dim pos As Integer
 Dim v As Integer
 l = IpSubnetParse(ip)
 ip = ip + "."
 IpSubnetToBin = 0
 While ip  ""
 pos = InStr(ip, ".")
 v = Val(Left(ip, pos - 1))
 If (l 
 v = 0
 ElseIf (l 
 v = v And ((2 ^ l - 1) * 2 ^ (8 - l))
 End If
 IpSubnetToBin = IpSubnetToBin * 256 + v
 ip = Mid(ip, pos + 1)
 l = l - 8
 Wend
End Function

'----------------------------------------------
' IpAdd
'----------------------------------------------
' example:
' IpAdd("192.168.1.1"; 4) returns "192.168.1.5"
' IpAdd("192.168.1.1"; 256) returns "192.168.2.1"
Function IpAdd(ByVal ip As String, offset As Double) As String
 IpAdd = IpBinToStr(IpStrToBin(ip) + offset)
End Function

'----------------------------------------------
' IpAnd
'----------------------------------------------
' bitwise AND
' example:
' IpAnd("192.168.1.1"; "255.255.255.0") returns "192.168.1.0"
Function IpAnd(ByVal ip1 As String, ByVal ip2 As String) As String
 ' compute bitwise AND from right to left
 Dim result As String
 While ((ip1  "") And (ip2  ""))
 Call IpBuild(IpParse(ip1) And IpParse(ip2), result)
 Wend
 IpAnd = result
End Function

'----------------------------------------------
' IpOr
'----------------------------------------------
' bitwise OR
' example:
' IpOr("192.168.1.1"; "0.0.0.255") returns "192.168.1.255"
Function IpOr(ByVal ip1 As String, ByVal ip2 As String) As String
 ' compute bitwise OR from right to left
 Dim result As String
 While ((ip1  "") And (ip2  ""))
 Call IpBuild(IpParse(ip1) Or IpParse(ip2), result)
 Wend
 IpOr = result
End Function

'----------------------------------------------
' IpXor
'----------------------------------------------
' bitwise XOR
' example:
' IpXor("192.168.1.1"; "0.0.0.255") returns "192.168.1.254"
Function IpXor(ByVal ip1 As String, ByVal ip2 As String) As String
 ' compute bitwise XOR from right to left
 Dim result As String
 While ((ip1  "") And (ip2  ""))
 Call IpBuild(IpParse(ip1) Xor IpParse(ip2), result)
 Wend
 IpXor = result
End Function

'----------------------------------------------
' IpAdd2
'----------------------------------------------
' another implementation of IpAdd which not use the binary representation
Function IpAdd2(ByVal ip As String, offset As Double) As String
 Dim result As String
 While (ip  "")
 offset = IpBuild(IpParse(ip) + offset, result)
 Wend
 IpAdd2 = result
End Function

'----------------------------------------------
' IpComp
'----------------------------------------------
' Compares the first 'n' bits of ip1 and ip2
' example:
' IpComp("10.0.0.0", "10.1.0.0", 9) returns TRUE
' IpComp("10.0.0.0", "10.1.0.0", 16) returns FALSE
Function IpComp(ByVal ip1 As String, ByVal ip2 As String, ByVal n As Integer) As Boolean
 Dim pos1 As Integer
 Dim pos2 As Integer
 Dim mask As Integer
 ip1 = ip1 + "."
 ip2 = ip2 + "."
 While (n > 0) And (ip1  "") And (ip2  "")
 pos1 = InStr(ip1, ".")
 pos2 = InStr(ip2, ".")
 If n >= 8 Then
 If pos1  pos2 Then
 IpComp = False
 Exit Function
 End If
 If Left(ip1, pos1)  Left(ip2, pos2) Then
 IpComp = False
 Exit Function
 End If
 Else
 mask = (2 ^ n - 1) * 2 ^ (8 - n)
 IpComp = ((Val(Left(ip1, pos1 - 1)) And mask) = (Val(Left(ip2, pos2 - 1)) And mask))
 Exit Function
 End If
 n = n - 8
 ip1 = Mid(ip1, pos1 + 1)
 ip2 = Mid(ip2, pos2 + 1)
 Wend
 IpComp = True
End Function

'----------------------------------------------
' IpGetByte
'----------------------------------------------
' get one byte from an ip address given its position
' example:
' IpGetByte("192.168.1.1"; 1) returns 192
Function IpGetByte(ByVal ip As String, pos As Integer) As Integer
 pos = 4 - pos
 For i = 0 To pos
 IpGetByte = IpParse(ip)
 Next
End Function

'----------------------------------------------
' IpSetByte
'----------------------------------------------
' set one byte in an ip address given its position and value
' example:
' IpSetByte("192.168.1.1"; 4; 20) returns "192.168.1.20"
Function IpSetByte(ByVal ip As String, pos As Integer, newvalue As Integer) As String
 Dim result As String
 Dim byteval As Double
 i = 4
 While (ip  "")
 byteval = IpParse(ip)
 If (i = pos) Then byteval = newvalue
 Call IpBuild(byteval, result)
 i = i - 1
 Wend
 IpSetByte = result
End Function

'----------------------------------------------
' IpMask
'----------------------------------------------
' returns an IP netmask from a subnet
' both notations are accepted
' example:
' IpMask("192.168.1.1/24") returns "255.255.255.0"
' IpMask("192.168.1.1 255.255.255.0") returns "255.255.255.0"
Function IpMask(ByVal ip As String) As String
 IpMask = IpBinToStr(IpMaskBin(ip))
End Function

'----------------------------------------------
' IpWildMask
'----------------------------------------------
' returns an IP Wildcard (inverse) mask from a subnet
' both notations are accepted
' example:
' IpWildMask("192.168.1.1/24") returns "0.0.0.255"
' IpWildMask("192.168.1.1 255.255.255.0") returns "0.0.0.255"
Function IpWildMask(ByVal ip As String) As String
 IpWildMask = IpBinToStr(((2 ^ 32) - 1) - IpMaskBin(ip))
End Function

'----------------------------------------------
' IpInvertMask
'----------------------------------------------
' returns an IP Wildcard (inverse) mask from a subnet mask
' or a subnet mask from a wildcard mask
' example:
' IpInvertMask("255.255.255.0") returns "0.0.0.255"
' IpInvertMask("0.0.0.255") returns "255.255.255.0"
Function IpInvertMask(ByVal mask As String) As String
 IpInvertMask = IpBinToStr(((2 ^ 32) - 1) - IpStrToBin(mask))
End Function

'----------------------------------------------
' IpMaskLen
'----------------------------------------------
' returns prefix length from a mask given by a string notation (xx.xx.xx.xx)
' example:
' IpMaskLen("255.255.255.0") returns 24 which is the number of bits of the subnetwork prefix
Function IpMaskLen(ByVal ipmaskstr As String) As Integer
 Dim notMask As Double
 notMask = 2 ^ 32 - 1 - IpStrToBin(ipmaskstr)
 zeroBits = 0
 Do While notMask  0
 notMask = Int(notMask / 2)
 zeroBits = zeroBits + 1
 Loop
 IpMaskLen = 32 - zeroBits
End Function

'----------------------------------------------
' IpWithoutMask
'----------------------------------------------
' removes the netmask notation at the end of the IP
' example:
' IpWithoutMask("192.168.1.1/24") returns "192.168.1.1"
' IpWithoutMask("192.168.1.1 255.255.255.0") returns "192.168.1.1"
Function IpWithoutMask(ByVal ip As String) As String
 Dim p As Integer
 p = InStr(ip, "/")
 If (p = 0) Then
 p = InStr(ip, " ")
 End If
 If (p = 0) Then
 IpWithoutMask = ip
 Else
 IpWithoutMask = Left(ip, p - 1)
 End If
End Function

'----------------------------------------------
' IpSubnetLen
'----------------------------------------------
' get the mask len from a subnet
' example:
' IpSubnetLen("192.168.1.1/24") returns 24
' IpSubnetLen("192.168.1.1 255.255.255.0") returns 24
Function IpSubnetLen(ByVal ip As String) As Integer
 Dim p As Integer
 p = InStr(ip, "/")
 If (p = 0) Then
 p = InStr(ip, " ")
 If (p = 0) Then
 IpSubnetLen = 32
 Else
 IpSubnetLen = IpMaskLen(Mid(ip, p + 1))
 End If
 Else
 IpSubnetLen = Val(Mid(ip, p + 1))
 End If
End Function

'----------------------------------------------
' IpSubnetParse
'----------------------------------------------
' Get the mask len from a subnet and remove the mask from the address
' The ip parameter is modified and the subnet mask is removed
' example:
' IpSubnetLen("192.168.1.1/24") returns 24 and ip is changed to "192.168.1.1"
' IpSubnetLen("192.168.1.1 255.255.255.0") returns 24 and ip is changed to "192.168.1.1"
Function IpSubnetParse(ByRef ip As String) As Integer
 Dim p As Integer
 p = InStr(ip, "/")
 If (p = 0) Then
 p = InStr(ip, " ")
 If (p = 0) Then
 IpSubnetParse = 32
 Else
 IpSubnetParse = IpMaskLen(Mid(ip, p + 1))
 ip = Left(ip, p - 1)
 End If
 Else
 IpSubnetParse = Val(Mid(ip, p + 1))
 ip = Left(ip, p - 1)
 End If
End Function

'----------------------------------------------
' IpSubnetSize
'----------------------------------------------
' returns the number of addresses in a subnet
' example:
' IpSubnetSize("192.168.1.32/29") returns 8
' IpSubnetSize("192.168.1.0 255.255.255.0") returns 256
Function IpSubnetSize(ByVal subnet As String) As Double
 IpSubnetSize = 2 ^ (32 - IpSubnetLen(subnet))
End Function

'----------------------------------------------
' IpClearHostBits
'----------------------------------------------
' set to zero the bits in the host part of an address
' example:
' IpClearHostBits("192.168.1.1/24") returns "192.168.1.0/24"
' IpClearHostBits("192.168.1.193 255.255.255.128") returns "192.168.1.128 255.255.255.128"
Function IpClearHostBits(ByVal net As String) As String
 Dim ip As String
 ip = IpWithoutMask(net)
 IpClearHostBits = IpAnd(ip, IpMask(net)) + Mid(net, Len(ip) + 1)
End Function

'----------------------------------------------
' IpIsInSubnet
'----------------------------------------------
' Returns TRUE if "ip" is in "subnet"
' example:
' IpIsInSubnet("192.168.1.35"; "192.168.1.32/29") returns TRUE
' IpIsInSubnet("192.168.1.35"; "192.168.1.32 255.255.255.248") returns TRUE
' IpIsInSubnet("192.168.1.41"; "192.168.1.32/29") returns FALSE
Function IpIsInSubnet(ByVal ip As String, ByVal subnet As String) As Boolean
 Dim l As Integer
 l = IpSubnetParse(subnet)
 IpIsInSubnet = IpComp(ip, subnet, l)
End Function

'----------------------------------------------
' IpSubnetMatch
'----------------------------------------------
' Tries to match an IP address or a subnet against a list of subnets in the
' left-most column of table_array and returns the row number
' 'ip' is the value to search for in the subnets in the first column of
' the table_array
' 'table_array' is one or more columns of data
' 'fast' indicates the search mode : BestMatch or Fast mode
' fast = 0 (default value)
' This will work on any subnet list. If the search value matches more
' than one subnet, the smallest subnet will be returned (best match)
' fast = 1
' The subnet list MUST be sorted in ascending order and MUST NOT contain
' overlapping subnets. This mode performs a dichotomic search and runs
' much faster with large subnet lists.
' The function returns 0 if the IP address is not matched.
Function IpSubnetMatch(ByVal ip As String, table_array As Range, Optional fast As Boolean = False) As Integer
 Dim i As Integer
 IpSubnetMatch = 0
 If fast Then
 Dim a As Integer
 Dim b As Integer
 Dim ip_bin As Double
 a = 1
 b = table_array.Rows.Count
 ip_bin = IpSubnetToBin(ip)
 Do
 i = (a + b + 0.5) / 2
 If ip_bin 
 b = i - 1
 Else
 a = i
 End If
 Loop While a 
 If IpSubnetIsInSubnet(ip, table_array.Cells(a, 1)) Then
 IpSubnetMatch = a
 End If
 Else
 Dim previousMatchLen As Integer
 Dim searchLen As Integer
 Dim subnet As String
 Dim subnetLen As Integer
 searchLen = IpSubnetParse(ip)
 previousMatchLen = 0
 For i = 1 To table_array.Rows.Count
 subnet = table_array.Cells(i, 1)
 subnetLen = IpSubnetParse(subnet)
 If subnetLen > previousMatchLen Then
 If searchLen >= subnetLen Then
 If IpComp(ip, subnet, subnetLen) Then
 previousMatchLen = subnetLen
 IpSubnetMatch = i
 End If
 End If
 End If
 Next i
 End If
End Function

'----------------------------------------------
' IpSubnetVLookup
'----------------------------------------------
' Tries to match an IP address or a subnet against a list of subnets in the
' left-most column of table_array and returns the value in the same row based
' on the index_number
' 'ip' is the value to search for in the subnets in the first column of
' the table_array
' 'table_array' is one or more columns of data
' 'index_number' is the column number in table_array from which the matching
' value must be returned. The first column which contains subnets is 1.
' 'fast' indicates the search mode : BestMatch or Fast mode
' fast = 0 (default value)
' This will work on any subnet list. If the search value matches more
' than one subnet, the smallest subnet will be returned (best match)
' fast = 1
' The subnet list MUST be sorted in ascending order and MUST NOT contain
' overlapping subnets. This mode performs a dichotomic search and runs
' much faster with large subnet lists.
' Note: add 0.0.0.0/0 in the array if you want the function to return a
' default value (best match mode only)
Function IpSubnetVLookup(ByVal ip As String, table_array As Range, index_number As Integer, Optional fast As Boolean = False) As String
 Dim i As Integer
 i = IpSubnetMatch(ip, table_array, fast)
 If i = 0 Then
 IpSubnetVLookup = "Not Found"
 Else
 IpSubnetVLookup = table_array.Cells(i, index_number)
 End If
End Function

'----------------------------------------------
' IpSubnetVLookupAreas
'----------------------------------------------
' Same as IpSubnetVLookup except that table_array parameter can be a
' named area containing multiple tables. Use it if you want to search in
' more than one table.
' Doesn't have the 'fast' option.
Function IpSubnetVLookupAreas(ByVal ip As String, table_array As Range, index_number As Integer) As String
 Dim previousMatch As String
 previousMatch = "0.0.0.0/0"
 IpSubnetVLookupAreas = "Not Found"
 For a = 1 To table_array.Areas.Count
 For i = 1 To table_array.Areas(a).Rows.Count
 Dim subnet As String
 subnet = table_array.Areas(a).Cells(i, 1)
 If IpIsInSubnet(ip, subnet) And (IpSubnetLen(subnet) > IpSubnetLen(previousMatch)) Then
 previousMatch = subnet
 IpSubnetVLookupAreas = table_array.Areas(a).Cells(i, index_number)
 End If
 Next i
 Next a
End Function

'----------------------------------------------
' IpSubnetIsInSubnet
'----------------------------------------------
' Returns TRUE if "subnet1" is in "subnet2"
' example:
' IpSubnetIsInSubnet("192.168.1.35/30"; "192.168.1.32/29") returns TRUE
' IpSubnetIsInSubnet("192.168.1.41/30"; "192.168.1.32/29") returns FALSE
' IpSubnetIsInSubnet("192.168.1.35/28"; "192.168.1.32/29") returns FALSE
' IpSubnetIsInSubnet("192.168.0.128 255.255.255.128"; "192.168.0.0 255.255.255.0") returns TRUE
Function IpSubnetIsInSubnet(ByVal subnet1 As String, ByVal subnet2 As String) As Boolean
 Dim l1 As Integer
 Dim l2 As Integer
 l1 = IpSubnetParse(subnet1)
 l2 = IpSubnetParse(subnet2)
 If l1 
 IpSubnetIsInSubnet = False
 Else
 IpSubnetIsInSubnet = IpComp(subnet1, subnet2, l2)
 End If
End Function

'----------------------------------------------
' IpFindOverlappingSubnets
'----------------------------------------------
' this function must be used in an array formula
' it will find in the list of subnets which subnets overlap
' 'SubnetsArray' is single column array containing a list of subnets, the
' list may be sorted or not
' the return value is also a array of the same size
' if the subnet on line x is included in a larger subnet from another line,
' this function returns an array in which line x contains the value of the
' larger subnet
' if the subnet on line x is distinct from any other subnet in the array,
' then this function returns on line x an empty cell
' if there are no overlapping subnets in the input array, the returned array
' is empty
Function IpFindOverlappingSubnets(subnets_array As Range) As Variant
 Dim result_array() As Variant
 ReDim result_array(1 To subnets_array.Rows.Count, 1 To 1)
 For i = 1 To subnets_array.Rows.Count
 result_array(i, 1) = ""
 For j = 1 To subnets_array.Rows.Count
 If (i  j) And IpSubnetIsInSubnet(subnets_array.Cells(i, 1), subnets_array.Cells(j, 1)) Then
 result_array(i, 1) = subnets_array.Cells(j, 1)
 Exit For
 End If
 Next j
 Next i
 IpFindOverlappingSubnets = result_array
End Function

'----------------------------------------------
' IpSortArray
'----------------------------------------------
' this function must be used in an array formula
' 'ip_array' is a single column array containing ip addresses
' the return value is also a array of the same size containing the same
' addresses sorted in ascending or descending order
' 'descending' is an optional parameter, if set to True the adresses are
' sorted in descending order
Function IpSortArray(ip_array As Range, Optional descending As Boolean = False) As Variant
 Dim s As Integer
 Dim t As Integer
 t = 0
 s = ip_array.Rows.Count
 Dim list() As Double
 ReDim list(1 To s)
 ' copy the IP list as binary values
 For i = 1 To s
 If (ip_array.Cells(i, 1)  0) Then
 t = t + 1
 list(t) = IpStrToBin(ip_array.Cells(i, 1))
 End If
 Next i
 ' sort the list with bubble sort
 For i = t - 1 To 1 Step -1
 For j = 1 To i
 If ((list(j) > list(j + 1)) Xor descending) Then
 Dim swap As Double
 swap = list(j)
 list(j) = list(j + 1)
 list(j + 1) = swap
 End If
 Next j
 Next i
 ' copy the sorted list as strings
 Dim resultArray() As Variant
 ReDim resultArray(1 To s, 1 To 1)
 For i = 1 To t
 resultArray(i, 1) = IpBinToStr(list(i))
 Next i
 IpSortArray = resultArray
End Function

'----------------------------------------------
' IpSubnetSortArray
'----------------------------------------------
' this function must be used in an array formula
' 'ip_array' is a single column array containing ip subnets in "prefix/len"
' or "prefix mask" notation
' the return value is also an array of the same size containing the same
' subnets sorted in ascending or descending order
' 'descending' is an optional parameter, if set to True the subnets are
' sorted in descending order
Function IpSubnetSortArray(ip_array As Range, Optional descending As Boolean = False) As Variant
 Dim s As Integer
 Dim t As Integer
 t = 0
 s = ip_array.Rows.Count
 Dim list() As String
 ReDim list(1 To s)
 ' copy the IP list as binary values
 For i = 1 To s
 If (ip_array.Cells(i, 1)  0) Then
 t = t + 1
 list(t) = ip_array.Cells(i, 1)
 End If
 Next i
 ' sort the list with bubble sort
 For i = t - 1 To 1 Step -1
 For j = 1 To i
 Dim m, n As Double
 m = IpStrToBin(list(j))
 n = IpStrToBin(list(j + 1))
 If (((m > n) Or ((m = n) And (IpMaskBin(list(j)) 
 Dim swap As String
 swap = list(j)
 list(j) = list(j + 1)
 list(j + 1) = swap
 End If
 Next j
 Next i
 ' copy the sorted list as strings
 Dim resultArray() As Variant
 ReDim resultArray(1 To s, 1 To 1)
 For i = 1 To t
 resultArray(i, 1) = list(i)
 Next i
 IpSubnetSortArray = resultArray
End Function

'----------------------------------------------
' IpParseRoute
'----------------------------------------------
' this function is used by IpSubnetSortJoinArray to extract the subnet
' and next hop in route
' the supported formats are
' 10.0.0.0 255.255.255.0 1.2.3.4
' 10.0.0.0/24 1.2.3.4
' the next hop can be any character sequence, and not only an IP
Function IpParseRoute(ByVal route As String, ByRef nexthop As String)
 slash = InStr(route, "/")
 sp = InStr(route, " ")
 If ((slash = 0) And (sp > 0)) Then
 temp = Mid(route, sp + 1)
 sp = InStr(sp + 1, route, " ")
 End If
 If (sp = 0) Then
 IpParseRoute = route
 nexthop = ""
 Else
 IpParseRoute = Left(route, sp - 1)
 nexthop = Mid(route, sp + 1)
 End If
End Function

'----------------------------------------------