Skype for Business Rate My Call with Power BI Analysis
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 Files\WindowsPowerShell\Modules\CxdCallData\1.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:\mypasswordfolder\Office365_securepass.txt' | convertto-securestring
$global:credential = new-object -typename System.Management.Automation.PSCredential -argumentlist "USERNAME@TENANT.onmicrosoft.com",$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 Path\Office365_securepass.txt' | convertto-securestring
$myO365cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "USERNAME@TENANT.onmicrosoft.com",$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:\SfB\Reports -NumberOfDaysToSearch 7 -CsvFileWithUsers $filename
Below is the Export of the Task Scheduler in XML
now as you see it collects all reports to c:\SfB\Reports; you can only copy the SFB-UserFeedback-YYYY-MM-DD HH-MM-SS.CSV files to a folder lets call it c:\SfB\Reports\RMC
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 PATH\Report\RMC"),
#"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 <https://www.gnu.org/licenses/>.
' https://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 < 4
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 <= 0) Then
v = 0
ElseIf (l < 8) Then
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 < IpSubnetToBin(table_array.Cells(i, 1)) Then
b = i - 1
Else
a = i
End If
Loop While a < b
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 < l2 Then
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)) < IpMaskBin(list(j + 1))))) Xor descending) Then
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
'----------------------------------------------
' IpSubnetSortJoinArray
'----------------------------------------------
' this function can sort and summarize subnets or ip routes
' it 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 order
' any consecutive subnets of the same size will be summarized when it is
' possible
' each line may contain any character sequence after the subnet, such as
' a next hop or any parameter of an ip route
' in this case, only subnets with the same parameters will be summarized
Function IpSubnetSortJoinArray(ip_array As Range) As Variant
Dim s As Integer
Dim t As Integer
Dim a As String
Dim b As String
Dim nexthop1 As String
Dim nexthop2 As String
t = 0
s = ip_array.Rows.Count
Dim list() As String
ReDim list(1 To s)
' copy subnet list
For i = 1 To s
If (ip_array.Cells(i, 1) <> 0) Then
t = t + 1
' just use the networks as provide:
'list(t) = ip_array.Cells(i, 1)
' or clean up the host part in each subnet:
a = IpParseRoute(ip_array.Cells(i, 1), nexthop1)
list(t) = IpClearHostBits(a) + " " + nexthop1
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
a = IpParseRoute(list(j), nexthop1)
b = IpParseRoute(list(j + 1), nexthop2)
m = IpStrToBin(IpWithoutMask(a))
n = IpStrToBin(IpWithoutMask(b))
If ((m > n) Or ((m = n) And (IpMaskBin(a) < IpMaskBin(b)))) Then
Dim swap As String
swap = list(j)
list(j) = list(j + 1)
list(j + 1) = swap
End If
Next j
Next i
' try to join subnets
i = 1
While (i < t)
remove_next = False
a = IpParseRoute(list(i), nexthop1)
b = IpParseRoute(list(i + 1), nexthop2)
If (IpSubnetIsInSubnet(a, b) And (nexthop1 = nexthop2)) Then
list(i) = list(i + 1)
remove_next = True
ElseIf (IpSubnetIsInSubnet(b, a) And (nexthop1 = nexthop2)) Then
remove_next = True
ElseIf ((IpSubnetLen(a) = IpSubnetLen(b)) And (nexthop1 = nexthop2)) Then
' create a subnet with the same notation
bigsubnet = Replace(IpWithoutMask(a) + "/" + Str(IpSubnetLen(a) - 1), " ", "")
If (InStr(a, "/") = 0) Then
bigsubnet = IpWithoutMask(a) & " " & IpMask(bigsubnet)
Else
End If
If (IpSubnetIsInSubnet(b, bigsubnet)) Then
' OK these subnets can be joined
list(i) = bigsubnet & " " & nexthop1
remove_next = True
End If
End If
If (remove_next) Then
' remove list(i+1) and make the list one element shorter
For j = i + 1 To t - 1
list(j) = list(j + 1)
Next j
t = t - 1
' step back and try again because list(i) may be joined with list(i-1)
If (i > 1) Then i = i - 1
Else
i = i + 1
End If
Wend
' 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
IpSubnetSortJoinArray = resultArray
End Function
'----------------------------------------------
' IpDivideSubnet
'----------------------------------------------
' divide a network in smaller subnets
' "n" is the value that will be added to the subnet length
' "SubnetSeqNbr" is the index of the smaller subnet to return
' example:
' IpDivideSubnet("1.2.3.0/24"; 2; 0) returns "1.2.3.0/26"
' IpDivideSubnet("1.2.3.0/24"; 2; 1) returns "1.2.3.64/26"
Function IpDivideSubnet(ByVal subnet As String, n As Integer, index As Integer)
Dim ip As String
Dim slen As Integer
ip = IpAnd(IpWithoutMask(subnet), IpMask(subnet))
slen = IpSubnetLen(subnet) + n
If (slen > 32) Then
IpDivideSubnet = "ERR subnet lenght > 32"
Exit Function
End If
If (index >= 2 ^ n) Then
IpDivideSubnet = "ERR index out of range"
Exit Function
End If
ip = IpBinToStr(IpStrToBin(ip) + (2 ^ (32 - slen)) * index)
IpDivideSubnet = Replace(ip + "/" + Str(slen), " ", "")
End Function
'----------------------------------------------
' IpIsPrivate
'----------------------------------------------
' returns TRUE if "ip" is in one of the private IP address ranges
' example:
' IpIsPrivate("192.168.1.35") returns TRUE
' IpIsPrivate("209.85.148.104") returns FALSE
Function IpIsPrivate(ByVal ip As String) As Boolean
IpIsPrivate = (IpIsInSubnet(ip, "10.0.0.0/8") Or IpIsInSubnet(ip, "172.16.0.0/12") Or IpIsInSubnet(ip, "192.168.0.0/16"))
End Function
'----------------------------------------------
' IpRangeToCIDR
'----------------------------------------------
' returns a network or a list of networks given the first and the
' last address of an IP range
' if this function is used in a array formula, it may return more
' than one network
' example:
' IpRangeToCIDR("10.0.0.1","10.0.0.254") returns 10.0.0.0/24
' IpRangeToCIDR("10.0.0.1","10.0.1.63") returns the array : 10.0.0.0/24 10.0.1.0/26
' note:
' 10.0.0.0 or 10.0.0.1 as the first address returns the same result
' 10.0.0.254 or 10.0.0.255 (broadcast) as the last address returns the same result
Function IpRangeToCIDR(ByVal firstAddr As String, ByVal lastAddr As String) As Variant
firstAddr = IpAnd(firstAddr, "255.255.255.254") ' set the last bit to zero
lastAddr = IpOr(lastAddr, "0.0.0.1") ' set the last bit to one
Dim list() As String
n = 0
Do
l = 0
Do ' find the largest network which first address is firstAddr and which last address is not higher than lastAddr
' build a network of length l
' if it does not comply the above conditions, try with a smaller network
l = l + 1
net = firstAddr & "/" & l
ip1 = IpAnd(firstAddr, IpMask(net)) ' first @ of this network
ip2 = IpOr(firstAddr, IpWildMask(net)) ' last @ of this network
net = ip1 & "/" & l ' rebuild the network with the first address
diff = IpDiff(ip2, lastAddr) ' difference between the last @ of this network and the lastAddr we need to reach
Loop While (l < 32) And ((ip1 <> firstAddr) Or (diff > 0))
n = n + 1
ReDim Preserve list(1 To n)
list(n) = net
firstAddr = IpAdd(ip2, 1)
Loop While (diff < 0) ' if we haven't reached the lastAddr, loop to build another network
Dim resultArray() As Variant
ReDim resultArray(1 To n + 1, 1 To 1)
For i = 1 To n
resultArray(i, 1) = list(i)
Next i
IpRangeToCIDR = resultArray
End Function
'----------------------------------------------
' IpSubtractSubnets
'----------------------------------------------
' Remove subnets from a list of subnets
' this function must be used in an array formula
' 'input_array' is a list of assigned subnets
' 'subtract_array' is a list of used subnets
' the result is a list of unused subnets
Function IpSubtractSubnets(input_array As Range, subtract_array As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim s As Integer
s = 0
Dim list() As String
ReDim list(1 To input_array.Rows.Count)
' copy subnet list
For i = 1 To input_array.Rows.Count
If (input_array.Cells(i, 1) <> 0) Then
s = s + 1
list(s) = input_array.Cells(i, 1)
End If
Next i
For i = 1 To subtract_array.Rows.Count
' try to remove each network in subtract_array from the list in input_array
subtractNet = subtract_array.Cells(i, 1)
If subtractNet <> 0 Then
' try to remove each network in subtract_array from each network in input_array
j = 1
Do
net = list(j)
' is the network to remove equal or larger ?
If IpSubnetIsInSubnet(net, subtractNet) Then ' remove the network from input_array
For k = j To s - 1
list(k) = list(k + 1)
Next k
s = s - 1
' is the network to remove smaller ?
ElseIf IpSubnetIsInSubnet(subtractNet, net) Then ' split this network in input_array
' insert a line in the result array
s = s + 1
ReDim Preserve list(1 To s)
For k = s To j + 2 Step -1
list(k) = list(k - 1)
Next k
' create 2 smaller subnets
list(j + 1) = IpDivideSubnet(list(j), 1, 1)
list(j) = IpDivideSubnet(list(j), 1, 0)
Else
' nothing to do, skip to next network in input_array
j = j + 1
End If
Loop While j <= s
End If
Next i
Dim resultArray() As Variant
ReDim resultArray(1 To s + 1, 1 To 1)
For i = 1 To s
resultArray(i, 1) = list(i)
Next i
IpSubtractSubnets = resultArray
End Function
'----------------------------------------------
' IpDiff
'----------------------------------------------
' difference between 2 IP addresses
' example:
' IpDiff("192.168.1.7"; "192.168.1.1") returns 6
Function IpDiff(ByVal ip1 As String, ByVal ip2 As String) As Double
Dim mult As Double
mult = 1
IpDiff = 0
While ((ip1 <> "") Or (ip2 <> ""))
IpDiff = IpDiff + mult * (IpParse(ip1) - IpParse(ip2))
mult = mult * 256
Wend
End Function
'----------------------------------------------
' IpParse
'----------------------------------------------
' Parses an IP address by iteration from right to left
' Removes one byte from the right of "ip" and returns it as an integer
' example:
' if ip="192.168.1.32"
' IpParse(ip) returns 32 and ip="192.168.1" when the function returns
Function IpParse(ByRef ip As String) As Integer
Dim pos As Integer
pos = InStrRev(ip, ".")
If pos = 0 Then
IpParse = Val(ip)
ip = ""
Else
IpParse = Val(Mid(ip, pos + 1))
ip = Left(ip, pos - 1)
End If
End Function
'----------------------------------------------
' IpBuild
'----------------------------------------------
' Builds an IP address by iteration from right to left
' Adds "ip_byte" to the left the "ip"
' If "ip_byte" is greater than 255, only the lower 8 bits are added to "ip"
' and the remaining bits are returned to be used on the next IpBuild call
' example 1:
' if ip="168.1.1"
' IpBuild(192, ip) returns 0 and ip="192.168.1.1"
' example 2:
' if ip="1"
' IpBuild(258, ip) returns 1 and ip="2.1"
Function IpBuild(ip_byte As Double, ByRef ip As String) As Double
If ip <> "" Then ip = "." + ip
ip = Format(ip_byte And 255) + ip
IpBuild = ip_byte \ 256
End Function
'----------------------------------------------
' IpMaskBin
'----------------------------------------------
' returns binary IP mask from an address with / notation (xx.xx.xx.xx/yy)
' example:
' IpMask("192.168.1.1/24") returns 4294967040 which is the binary
' representation of "255.255.255.0"
Function IpMaskBin(ByVal ip As String) As Double
Dim bits As Integer
bits = IpSubnetLen(ip)
IpMaskBin = (2 ^ bits - 1) * 2 ^ (32 - bits)
End Function
Save the Macro and close the editor and then save the Excel sheet as Macro Enabled format.
now we have the functions that can reverse lookup an IP to a Subnet , but we need to import the data to the Excel sheet and then do more steps..
Now we will download the building data and open an empty sheet in the same Excel file that we have created and then Rename the Sheet to Subnets
Then on the first Column A write the following Formula in the first cell:
=IF(ISBLANK('C:\tmp\[BuildingDATAFILE.csv]BuildingDATAFILE'!$A2),"",CONCATENATE('C:\tmp\[BuildingDATAFILE.csv]BuildingDATAFILE'!$A:$A,"/",'C:\tmp\[BuildingDATAFILE.csv]BuildingDATAFILE'!$C:$C))
Replace BuildingDATAFILE with your Building data file name and path.
Fill down the rest of the column with this formula:
- Select and copy the cell containing the formula and press CTRL+SHIFT+DOWN to select the rest of the column
- Fill down by pressing CTRL+D
- Use CTRL+UP to return up
Now you will get the Subnet and Mask in Column A; we have to sort them
In the same sheet select Column B
type the formula, =iPSubnetsortArray(A1:A5)
press Ctrl+Shift+Enter instead of Enter
Now we have them in sorted order in Column B; then on Column C we will add a new Formula to do vlookup for the Subnet name from the data building file.
in Column C type the following Formula:
=VLOOKUP(B1,'C:\tmp\[BuildingDATAFILE.csv]BuildingDATAFILE'!$A$1:$B$5,2,FALSE) 
now we got the subnet sorted and with their corresponding names.
lets go back to the first sheet and add a coulmn called From Subnet and To Subnet corresponding to the to and from IP address
then add the formula respectively to reverse lookup the IPs
=IpSubnetVLookup([@FromIPAddr],Subnets!$A$1:$B$5,3)
=IpSubnetVLookup([@ToIPAddr],Subnets!$A$1:$B$5,3) 
Finally as we got all the information we need we will open Power BI Desktop to import this information
Open a new Query
then Click Advanced Editor and enter the following query
let
Source = Excel.Workbook(File.Contents("C:\tmp\Book1.xlsm"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Removed Duplicates" = Table.Distinct(#"Promoted Headers", {"DialogId"}),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"
Click Close and Apply
Then all of the Excel information will be imported into Power BI we will start some interesting reports:
first we will need the From Subnet in percentage as well as the To Subnet
Then we will add the Ratings and the Count of Ratings only
as you can see if you clicked on these ratings it will show you the percentage of the fro and to subnet names that the users are reporting Very bad, fair, good or excellent from.
you can add as well the users by rating count which will show you the users who most rated poor , very bad, so you can address their issues.
Now moving to the next part where we Analyse the user sentiment, Thanks to DataCHant blog for helping me out in this task \
i am copying most of his post blog here as i had to do the exact steps with minor changes
Before we start Sentiment Analytics
Prerequisites
To do this tutorial, you need:
- Microsoft Power BI Desktop. Download at no charge.
- A Microsoft Azure account. Start a free trial or sign in.
- A access key for Text Analytics. Sign up, then get your key.
- Customer comments. Get our example data or use your own.
Preparing the API queries - A parameter and a query function
In this section we'll prepare the advanced Power Query code that we'll need for the Key Phrase API.
In the Query Editor's Home tab, click Manager Parameters, then select New Parameter.
In the Parameters dialog, set APIKeyas Name and paste the API Key that you obtained from Microsoft Cognitive Services into the text box Current Value.
When you are done, click OK.
In the Query Editor's Home tab, click the drop down menu below the icon of New Source and select Blank Query. Still in Home tab, click Advanced Query and paste the following code:
(Source) =>
let
JsonRecords = Text.FromBinary(Json.FromValue(Source)),
JsonRequest = "{""documents"": " & JsonRecords & "}",
JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
Response =
Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/keyPhrases?",
[
Headers = [#"Ocp-Apim-Subscription-Key"= APIKey,
#"Content-Type"="application/json", Accept="application/json"],
Content=JsonContent
]),
JsonResponse = Json.Document(Response,1252)
in
JsonResponse
Click Done in the Advanced Editor, and rename the new query to GetKeyPhrases.
Note: Now in Queries pane, you will notice that the new query GetKeyPhrases has an fx icon. The query above is not a normal query. It is a query function that receives a table as an input, converts it into a JSON format, sends it to Microsoft Cognitive Services and returns the JSON response.
Right click on the Sheet1 in the left Queries pane, and click Reference.
Rename the query Sheet1(2) to Sentiment Results. To rename the query, right click on it and click Rename.
Note: In the next step we will select the columns in our data that contain the actual text for analysis and a unique ID for each text. The unique ID is required by Microsoft Cognitive Services API. The unique ID will help us to map the Sentiment scores in the response to the relevant text. In our data, we use the diagIDs as unique IDs.
In Home tab of Query Editor, click Choose Columns, unselect all columns, then select DiagIDand FeedbackText and click OK.
Now, let's rename the columns. DiagID should be renamed to id and FeedbackText should be renamed to text. This step is critical. Don't miss it. The Sentiment Analysis API requires these names.
Note: If you need to perform analysis on text in French, Spanish or Portuguese, you can create another step here and define a custom column whose name is languageand is value is "fr", "es" or "pt" (For French, Spanish or Portuguese). Since English is the default language in the API, we skip this step.
In the next step, we will remove rows with empty text messages. There is no point in sending such rows for Sentiment Analysis, and the service will return errors if we try to.
Click in the filter icon of the column text, and then click Remove Empty.
Rename the column message to text. This is crucial. Later on we convert the table into a JSON format with the name text.
In Add Column tab, click Custom Column.
Set language as New column name, and = "en" as Custom column formula. Then click OK.
Note: The Text Analytics Key Phrases API support the following languages: English (en), German (de), Spanish (es) and Japanese (ja). You can use any of these codes if your text is in those languages.
Group data to sub-tables of 1000 messages each
Microsoft Cognitive Services allow us to send 1000 different messages on each API call as part of the Key Phrases API. In this part, we'll group the table into sub-tables of 1000 rows.
In Add Column click Index Column.
Select the new column, Index, and in Transform tab, click Standard, then select Integer-Divide.
In Integer-Divide dialog, set 1000as Value and click OK.
Now the values in column Indexare all 0 in the first 1000 rows, all 1 in the next 1000 rows, and so forth.
Select the column Index, and click Group By in Transform tab .
In the Group By dialog, ensure that Indexis selected in the drop down menu Group By.
Set sub-table as the text in New column name.
Select All Rows as Operation, and click OK.
After the grouping step, we have a new column sub-table that contains our tables in bulks of 1000 rows each (excluding the last row that has the remainders).
We can delete the column Index, and click Invoke Custom Function in Add Column tab.
In Invoke Custom Function dialog, set GetKeyPhrasesas Function Query and select sub-tableas the column. When you are done, click OK.
Delete the column sub-table, and click the expand button in the left side of the header GetKeyPhrases.
Ensure all columns are selected in the expand pane, uncheck Use original column name as prefix, and click OK.
Delete the column errors.
Click on the expand button in the header of column documents, select all columns and click OK.
Click on the expand button in the header of column KeyPhrases.
Finally, we have a new column with a key phrase for each message id. You should note that we will usually have multiple key phrases per message, so our table is now expended to multiple rows per message id.
For a final cosmetic touch, let's capitalize each word in column KeyPhrases:
Select the column KeyPhrases, right click on its header, select Transform and then select Capitalize Each Word.
We have completed the extraction of key messages. It's time to move to the modeling and visualization.
In the Query Editor window, click Close & Apply.
Feeding Key Phrases to a WordCloud Custom Visual
We are almost done. It's time to move to the visualization part. We'll now learn how to create a simple WordCloud visual with our key phrases.
After completing the last two parts, we now have two tables FeedbackText and KeyPhrases. We can create a relationship between column idin both tables (if Power BI Desktop didn't detect them automatically). To do it, go to Relationships view, and drag and drop the column Diagidfrom FeedbackText to KeyPhrasesas shown here.
Download WordCloud (from here), and import it to your report .
Click the Report view, and add a WordCloud to your report. Drag and drop the column KeyPhrasesto Category and count of KeyPhrases to Value .
In Format tab in Visualizations, turn on Stop Words and Turn off Rotate Text.
Under section General, turn off Word-breaking.
You can now resize the WordCloud visual, and see the size of the key phrases is defined by the number of posts.