This blog
is an expansion on the previous Part 1 and Part 2 posts found here and here. The process
of setting up the Function App for this part 3 section is the same as was
documented in Part 1 and 2. I suggest you head over to the first two parts and give them a good read before moving onto this post.
In part 3
we will be adding to the Function App so it can save data over time that we can use to graph and manipulate in Power Bi. To do this we will expand the application
to do the following:
- Save all port test results to Azure Storage tables for analysis.
- Use Power Bi to connect to Azure Storage Tables and create nice graphs showing the status of Edge servers over a period of time.
Just like
in Part 2 we will use the Azure Storage Tables module for Powershell to allow
our application to keep both a short term memory for logging errors as well as
a long term memory for logging all port testing attempts.
Step 1
To begin with follow
Steps 1 through 5 of Part 2.
Step 2 – Download a copy of the script
You can
grab a copy of the script I wrote for part 3 from here:
Step 3 - Update variables
Update the Storage Account details in the Powershell Script.
Update the Storage Account details in the Powershell Script.
IMPORTANT: These settings are slightly different than in Part 2. In this
case we have 2 different storage tables: one of them stores the current state of edge
servers (same as part 2) and the other one stores all attempts into a separate table (which we will use for analysis in Power Bi):
#AZURE
STORAGE VARIABLES######
#SETTINGS
ARE FOUND UNDER PLATFORM FEATURES TAB -> PROPERTIES
$subscriptionName = "Visual
Studio Premium with MSDN" #SUBSCRIPTION NAME
$resourceGroup = "EdgePortTester-Part003" #RESOURCE
GROUP
$storageAccount = "edgeporttesterp8dbf" #STORAGE ACCOUNT
NAME
$tableName = "EdgeTesterTablePart3Current" #CHOOSE A
NAME 1
$tableName2 = "EdgeTesterTablePart3Results" #CHOOSE A
NAME 2
$partitionKey = "EdgeTesterStoragePart3Current" #CHOOSE A
NAME 1
$partitionKey2 = "EdgeTesterStoragePart3Results" #CHOOSE A
NAME 2
$storageAccountKey = "7asdkjhasd7KHDKJHAS0dsflasdnnlasd099asdpncsdlknclLJSDLjbadksdjbfa9su9duhoasivRqXA615jQ==" #STORAGE
ACCOUNT > ACCESS KEYS
#AZURE
STORAGE VARIABLE END######
Don’t
forget to fill in your Mail Jet email account
information (as you did in Part 1) and add your Skype for Business Edge server's details. See Part 1
for more details. Enter your
Mail Jet API Key (Username) and Secret Key (Password) and paste them into the
following section of the script:
#MAIL
JET USERNAME/PASSWORD#######
$emailUsername = "kjh3k23h4kjhkj37573f8f020879dff7"
$emailPassword = "9898f98fhdjkkdjh46cd418100075a3b"
#EMAIL
ADDRESS TO SEND ERRORS FROM
$SENDEREMAIL = "YourRealEmailAddress@domain.com"
#EMAIL
ADDRESS TO SEND ERRORS TO
$RECIPIENTEMAIL = "YourRealEmailAddress@domain.com"
##################################
Edit the
Skype for Business Edge server details as required. These are entered as an
array of hash tables. The sections highlighted in yellow can be changed. In
this case the application is monitoring 2 Edge servers, one in Melbourne and
one in Sydney.
Location
|
ServerName
|
ServerRole
|
DestinationPort
|
Protocol
|
Melbourne
|
147.70.50.10
|
Federation
|
5061
|
TCP
|
Melbourne
|
147.70.50.10
|
Access Edge
|
443
|
TCP
|
Melbourne
|
147.70.50.11
|
Web
Conferencing
|
443
|
TCP
|
Melbourne
|
147.70.50.12
|
AV Edge
|
443
|
TCP
|
Sydney
|
147.70.60.20
|
Federation
|
5061
|
TCP
|
Sydney
|
147.70.60.20
|
Access Edge
|
443
|
TCP
|
Sydney
|
147.70.60.21
|
Web
Conferencing
|
443
|
TCP
|
Sydney
|
147.70.60.22
|
AV Edge
|
443
|
TCP
|
Note: The script only supports testing TCP ports at this time.
#SETUP
EACH SERVER
$Records = @(@{"Location" =
"Melbourne"; "ServerName"
= "147.70.50.10";
"ServerRole" = "Federation"; "DestinationPort" = "5061"; "Protocol" =
"TCP"})
$Records += @(@{"Location" =
"Melbourne"; "ServerName"
= "147.70.50.10";
"ServerRole" = "Access Edge";
"DestinationPort" = "443"; "Protocol" =
"TCP"})
$Records += @(@{"Location" =
"Melbourne"; "ServerName"
= "147.70.50.11";
"ServerRole" = "Web Conferencing";
"DestinationPort" = "443"; "Protocol" =
"TCP"})
$Records += @(@{"Location" =
"Melbourne"; "ServerName"
= "147.70.50.12";
"ServerRole" = "AV Edge"; "DestinationPort" = "443"; "Protocol" =
"TCP"})
$Records += @(@{"Location" =
"Sydney"; "ServerName"
= "147.70.60.20";
"ServerRole" = "Federation"; "DestinationPort" = "5061"; "Protocol" =
"TCP"})
$Records += @(@{"Location" =
"Sydney"; "ServerName"
= "147.70.60.20";
"ServerRole" = "Access Edge";
"DestinationPort" = "443"; "Protocol" =
"TCP"})
$Records += @(@{"Location" =
"Sydney"; "ServerName"
= "147.70.60.21";
"ServerRole" = "Web Conferencing";
"DestinationPort" = "443"; "Protocol" =
"TCP"})
$Records += @(@{"Location" =
"Sydney"; "ServerName"
= "147.70.60.22";
"ServerRole" = "AV Edge"; "DestinationPort" = "443"; "Protocol" =
"TCP"})
Step 4 – Parameter Tweaking
This
version of the script like part 2 has a few settings that you can tweak. These are how many
failures on each port is required before an email gets sent ($RequiredNumberOfFailuresBeforeEmail).
There is also a setting for consolidating multiple errors or recoveries into a
single email ($consolidateEmailsOnError and $consolidateEmailsOnError). Set these
as you like:
#This
is the number of required port check failures before an email is sent out
$RequiredNumberOfFailuresBeforeEmail = 3
#Send
1 email rather than one per record
$consolidateEmailsOnError = $true
$consolidateEmailsOnRecover = $true
Step 5 - Download Azure Storage Explorer
Now let
your Function Application run for a while and gather some data. At any time you
can look into your Function Apps Table Storage using Azure Storage Explorer.
This application will show you all of the rows in your storage tables and allow
you to see and edit as you see fit. You can download your free copy from here:
Once you
have logged into your Azure Account within Azure Storage Explorer you can dig into your storage tables by selecting Storage Accounts > (Storage Resource Group Name) > Tables to see
your table data. Note, there will be no table or data until you actually start running the Function App.
Step 6 - Download Power Bi
Now download a
copy of Power BI for desktop:
Install the downloaded Power Bi on your PC.
Step 7 - Open Power Bi
Open Power
Bi Desktop and you will be greeted with a splash screen and dialog. Click on
the “Get Data” button:
Step 8 - Import Data
The Get
Data dialog will then be displayed. Select “Azure Table Storage” from the list
and click the “Connect” button:
Step 8 - Account URL dialog
Power Bi
will now request an Account Name or URL to connect to:
Step 9 - Find Account URL
The account
name for the dialog above can be found in the Azure Portal under the storage
account Overview > Tables section:
The “Table
service endpoint” is the value you will need to fill in the dialog with:
Step 10 - Paste in URL
Enter the Table service endpoint into the “Azure Table Storage” dialog and click “OK”:
Step 11 - Enter Account Key
You will
now be asked to enter your “Account Key”:
This can be
found under the Storage Account > Access Keys section in the Azure Portal:
Enter the
Account Key and click “Connect”:
Step 12 - Load Data
Power Bi
will now connect to your Table Storage and list up all of the Tables in there.
Select the Results table and click “Load”:
Step 13 - Not all data is displayed
Power Bi
will now download your data into the application. You may notice though that
all of the columns that you can see in Azure Storage Explorer will not be
displayed:
Step 14 - Edit Query
To be able
to see all of the columns you need to do a little extra work. On the right hand
side of the screen, Right Click on Table name at the top of the top of the
column names listed and select “Edit Query”:
Step 15 - Expand content column
You will
now see an extended view of the data that includes a “Content” Column:
On the top
right of the Column click on the double arrow “expand” button:
You will
now get a full list of all of the additional columns available that are stored
in Table Storage as a Json blob. Tick the Columns that you want to include in
your graphs and data analysis and click OK:
Step 16 - All columns are now available
You will
now see the extra data columns:
Click the “Close and Apply” button from the
Home Tab:
The full array of data is now available for you to do as you please with:
Step 18 - Make charts
Back on the
"Report" tab you can now put together some nice looking graphs of your data. Here
is an example of a Pie Chart and a Bar Chart showing information about the
number of errors for each role:
To create
these graphs you use the following settings:
From here you
can play with the data in Power Bi and make whatever graph you like (I included location in the data so you can even plot your Edge servers on a map). This is
what makes Power Bi so powerful!