Connect Azure Functions To Office 365
,

Connect an Azure Function to Office 365

In the past couple of weeks I’ve uploaded a few scripts to help manage Office 365 customer environments in bulk via delegated administration. These scripts work well for us, though they only work when they’re initiated by a delegated administrator here. Sure, we could set them up on a server as a scheduled task, though in the interest of keeping things in the cloud, we’re moving them to Azure Functions.

If you’re interested, the scripts I’ve posted so far regarding Delegated Administration are here:

What are Azure Functions?

The Azure Functions service is Microsoft’s Function as a Service offering (FaaS). It’s similar to Hook.io, Google Cloud Functions or AWS Lambda if you’ve used any of those. Basically it lets you run standalone scripts or functions of a program in the cloud. One of Azure Functions’ benefits is that you don’t have to look after the underlying infrastructure, you can just add in your code and you’re pretty much done. You can start an Azure function using a HTTP or Azure Storage Queue trigger, or just set it to run on a timer. Azure Functions can run a variety of languages, though in this scenario, we’ll convert a simple Office 365 PowerShell script into a timer trigger function that runs each weekday.

Consumption Plan vs App Service Plan

Azure Functions Consumption Plan vs App Service PlanFor the number of functions we’ll be running, Azure functions are pretty much free with a Consumption Plan. This plan gives you a grant of 1 million executions and 400,000 GB-s of bandwidth, which we’ll be well under. However, Azure functions can also run on top of a paid Azure App Service Plan – which we’ll be taking advantage of.

Why pay for an Azure App Service Plan to run Azure Functions?

One of the limitations of the (almost) free version of Azure Functions is that it’s executions have a 5 minute limit, after which time they are terminated automatically. Apparently this is because the underlying virtual machines that run the functions are regularly recycled. Since some of our scripts have the potential to run longer than five minutes, we need to provision a small Azure App Service resource and then run our Azure functions on top of this. The VM that runs our App service runs continuously and will support long running functions

Here’s what we want to achieve:

  1. Set up an Azure Function App running on an App Service Plan
  2. Connect an Azure Function to Office 365
  3. Modify an existing PowerShell script to run on an Azure function

In another post we’ll look at connecting Azure Functions to Azure Storage to use in reporting via Power BI, and triggers for Microsoft Flow.

How to set up a new Azure Function App

  1. Log on to https://portal.azure.com using an account with an active Azure subscription.
  2. Click the Green + button on the left menu, search for Functions, then click Function AppSearch For Azure Functions And Click Create
  3. Click Create on the bottom right
  4. Complete the required fields for the Function AppComplete Fields To Create Azure Function App
  5. Choose to create a new Resource Group and Storage Account. For the Hosting Plan option, choose App Service Plan, then select an existing subscription or create a new one. In my case, I chose an S1 Plan, which is probably overkill. You’ll be able to get by with something much smaller.Create A New App Service Plan For Azure Functions
  6. Once you’ve completed the required fields, click Create and wait for it to complete deploymentWait For Azure Function App To Complete Deployment
  7. After it’s finished deploying, open your function app and click the + button to create a new function.Create A New Function Within Azure Functions
  8. Choose Custom function at the bottomChoose To Create A New Custom Function
  9. On the dropdown on the right, choose PowerShellSelect PowerShell From Azure Functions Drop Down
  10. Choose TimerTigger-PowerShell and enter a name for your Azure Function.Create Timer Trigger PowerShell Azure Function
  11. For the Schedule, enter a cron expression. There used to be documentation at the bottom of the page on how to format these, though at the time of writing it hasn’t appeared. For a function that runs Monday to Friday at 9:30 AM GMT time, enter the following:
    0 30 9 * * 1-5

    Define Schedule For Azure Function

  12. Click Create, you’ll be greeted with an almost blank screen where you can start to enter your PowerShell script. Before we do this, we’ll set up the Azure function to connect to Office 365, and secure your credentials within the function app.

Set up your Azure Function to connect to Office 365

In this step, we’ll be doing the following:

Define and retrieve your FTP Details

The FTP Details of the Azure Function are needed to upload resources that the Azure Function requires to connect to Office 365.

Download, then upload the MSOnline PowerShell Module via FTP

Azure Functions have a lot of PowerShell Modules installed by default, though they don’t have the MSOnline module that lets us connect to Office 365. We’ll need to download the module on our local computer, then upload it into the Azure function. This method was borrowed from this article by Alexandre Verkinderen.

Secure your Office 365 Credentials within the Function App

Right now, Azure Functions don’t integrate with the Azure Key Vault service. While we can store credentials within the function, these credentials are stored in plain text where anyone with access to the function can view them. This method was borrowed from this article by Tao Yang.

How to define and retrieve the FTP credentials for your Azure function app

  1. Click on the name of your function on the left menu.Click Azure Function Settings To Retrieve FTP Details
  2. Click Platform Features at the top, then click Deployment CredentialsOpen Platform Features
  3. Define a username and password for your FTP CredentialsSet Deployment Credentials For FTP Access
  4. Next under General Settings, click Properties.Open Properties Under General Settings
  5. Copy the FTP Host Name and make a note of it. You’ll need it to connect to the function’s storage via FTP and upload the MSOnline ModuleCopy FTP Host Name And User Details For FTP Deployment

Download, then upload the MSOnline PowerShell Module via FTP

  1. Open PowerShell on your computer, then run the following command. Make sure there’s a folder called ‘temp’ in your C:\ drive.
    Save-Module msonline -Repository PSGallery -Path "C:\temp"

    Save MSOnline Module For Office365 PowerShell On Local PC

  2. Wait for it to download, then make sure it exists within C:\tempWait For MSOnline Module To Download
  3. Open Windows Explorer, and connect to your function via FTP using the FTP Hostname and credentials we retrieved earlier.Connect To Your Azure App Service Via FTP Credentials
  4. Navigate to site/wwwroot/YourFunctionName then create a new folder called binCreate Bin Directory Under Azure Function
  5. Open the bin directory, and upload the MSOnline folder from your C:\Temp DirectoryUpload MSOnline PowerShell Module To Bin Directory In Azure Function

Secure your Office 365 Credentials within the Azure Function App

  1. On your computer, open PowerShell again and run the following commands. When you’re asked for your password, enter the password for the delegated admin account that you’ll use to manage your customers Office 365 environments. Make sure you press Enter again to run the final command to output the EncryptedPassword.txt file.
    $AESKey = New-Object Byte[] 32
     $Path = "C:\Temp\PassEncryptKey.key"
     $EncryptedPasswordPath = "C:\Temp\EncryptedPassword.txt"
     [Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($AESKey)
     Set-Content $Path $AESKey
     $Password = Read-Host "Please enter the password"
     $secPw = ConvertTo-SecureString -AsPlainText $Password -Force
     $AESKey = Get-content $Path
     $Encryptedpassword = $secPw | ConvertFrom-SecureString -Key $AESKey
     $Encryptedpassword | Out-File -filepath $EncryptedPasswordPath

    Run PowerShell Script To Secure Password
    This will create two files on in your C:\temp folder. An EncryptedPassword text file and a PassEncryptKey file. Be sure to delete the EncryptedPassword file once we’re done.Locate Secure Password And Key In Temp Folder

  2. Return to the FTP connection and create a directory called keys under the bin directory
  3. Upload the PassEncryptKey file into the keys directory.Upload PassEncryptKey To Azure Function Via FTP
  4. Return to your Azure Function Platform Settings, then open Application Settings.
  5. Under Application Settings, create two new Key-Value pairs. One called user, which contains the username of your delegated admin account, and another called password, which contains the contents of your EncryptedPassword.txt file. Once you’ve added this, be sure to delete the EncryptedPassword.txt file from your computer.
  6. Before you leave Application settings, update the Platform from 32 bit to 64 bit.Update Azure Function Platform To 64 Bit
  7. Wait for the settings to apply, then return to the Develop Section of your Azure FunctionWait For Azure Function Web App Settings To Apply

Modify your Office 365 PowerShell script for Azure Functions

  1. Update the variables at the top of the script to ensure they match the function name, Module Name and Module Version.For your existing scripts, you may need to update your Write-Host references to Write-Output.This sample script is a modified version of this one. It will set the default password expiration policy for all of your customers’ domains to never expire.You can use this one or create your own script under the # Start Script comment
    Write-Output "PowerShell Timer trigger function executed at:$(get-date)";
    
    $FunctionName = 'SetPasswordExpirationPolicy'
    $ModuleName = 'MSOnline'
    $ModuleVersion = '1.1.166.0'
    $username = $Env:user
    $pw = $Env:password
    #import PS module
    $PSModulePath = "D:\home\site\wwwroot\$FunctionName\bin\$ModuleName\$ModuleVersion\$ModuleName.psd1"
    $res = "D:\home\site\wwwroot\$FunctionName\bin"
    
    Import-module $PSModulePath
    
    # Build Credentials
    $keypath = "D:\home\site\wwwroot\$FunctionName\bin\keys\PassEncryptKey.key"
    $secpassword = $pw | ConvertTo-SecureString -Key (Get-Content $keypath)
    $credential = New-Object System.Management.Automation.PSCredential ($username, $secpassword)
    
    # Connect to MSOnline
    
    Connect-MsolService -Credential $credential
    
    # Start Script
    
    $Customers = Get-MsolPartnerContract -All
    $PartnerInfo = Get-MsolCompanyInformation
    
    Write-Output "Found $($Customers.Count) customers for $($PartnerInfo.DisplayName)"
    
    
    foreach ($Customer in $Customers) { 
    
    	Write-Output "-----------------------------------------------"
    	Write-Output " "
    	Write-Output "Checking the Password Expiration Policy on each domain for $($Customer.Name)"
    	Write-Output " "
    
    	$domains = Get-MsolDomain -TenantId $Customer.TenantId | Where-Object {$_.Status -eq "Verified"}
    
    	foreach($domain in $domains){
     
    		$domainStatus = Get-MsolPasswordPolicy -TenantId $Customer.TenantId -DomainName $domain.Name
    
    		if($domainStatus.ValidityPeriod -eq 2147483647){
    
    			Write-Output "Password Expiration Policy is set for $($domain.name) already"
    
    			$PasswordsWillExpire = $false
    
    			$MsolPasswordPolicyInfo = @{
    
    				TenantId = $Customer.TenantId
    				CompanyName = $Customer.Name
    				DomainName = $domain.Name
    				ValidityPeriod = $domainStatus.ValidityPeriod
    				NotificationDays = $domainStatus.NotificationDays
    				PasswordsWillExpire = $PasswordsWillExpire
    			}
    
    		}
    
    
    
    		if($domainStatus.ValidityPeriod -ne 2147483647){
    
    			Write-Output "Setting the Password Expiration Policy on $($domain.Name) for $($Customer.Name):"
    			Write-Output " "
    
    			Set-MsolPasswordPolicy -TenantId $Customer.TenantId -DomainName $domain.Name -ValidityPeriod 2147483647 -NotificationDays 30
    
    			$PasswordPolicyResult = Get-MsolPasswordPolicy -TenantId $Customer.TenantId -DomainName $domain.Name
    
    			if($PasswordPolicyResult.ValidityPeriod -eq 2147483647){
    
    				$PasswordsWillExpire = $false
    				Write-Output "Password policy change confirmed working"
    			}
    
    			if($PasswordPolicyResult.ValidityPeriod -ne 2147483647){
    
    				$PasswordsWillExpire = $true
    				Write-Output "Password policy change not confirmed yet, you may need to run this again."
    			}
    
    			$MsolPasswordPolicyInfo = @{
    
    				TenantId = $Customer.TenantId
    				CompanyName = $Customer.Name
    				DomainName = $domain.Name
    				ValidityPeriod = $PasswordPolicyResult.ValidityPeriod
    				NotificationDays = $PasswordPolicyResult.NotificationDays
    				PasswordsWillExpire = $PasswordsWillExpire
    
    			}
    
    		}
    	}
    }
    
  2. Click Run to manually start the script. You should see following output under LogsAzure Functions Output Log
, ,

How to add branding to Office 365 login screens

You can customise your Office 365 login screens via a service called Azure Active Directory (or Azure AD).

Microsoft Azure continues to transition to the new portal at portal.azure.com, and Azure AD is one of the last services to make the leap. Now that it’s in Preview on the new portal, we’ve made an updated video on how to easily brand your Office 365 login screens.

 

, , ,

Forward email form entries into SharePoint Lists

EmailToSharePointA common requirement for our customers is to forward emails to SharePoint Online lists. This email data usually comes from website forms or enquiry pages, though there’s no out-of-the-box way to extract the form data from an email, and upload it to separate columns in SharePoint list.

Previously I was using Cloud2050 Email Sync, though it relied on software installed on a PC to work, and only worked while that PC was operational and Outlook was open.

Here’s a solution that operates completely in the cloud using Outlook Rules, MailParser.io and Microsoft Azure Logic Apps.

The solution looks like this:

  1. Office 365 forwards email from your website’s form to your mailparser.io address via an Outlook Rule or Exchange Transport Rule.
  2. MailParser.io receives the email, extracts the form data and sends it to an Azure logic app using a Generic HTTP Webhook.
  3. Your Azure Logic App receives the form data, connects to SharePoint Online and adds the form data into the appropriate SharePoint list columns.

Prerequisites:

  • Sign up for MailParser.io – a free 30 day trial is available
  • Sign up for Microsoft Azure – use your Office 365 account, a free 30 day trial is available
  • A SharePoint List set up with the fields required for your form

Setting up MailParser

  1. Once you’ve signed up for mailparser.io, sign in and click Create New InboxCreate New Inbox In Mailparser.io
  2. Give it a name and add some notes:Name Mailparser Inbox
  3. You’ll be given an email address to forward your form emails to. Keep track of this address, as you’ll need it to receive the emails you send from Outlook or Exchange mail rules. Forward a couple of sample form emails to the address to get started.Get Mailparser Email
  4. Once your emails are received, you can set up your Parsing Rules:Add Mail Parsing Rules
  5. Usually, the mailparser will be able to automatically identify the field names and values from your forwarded email. If it doesn’t, click Try Something Else to give it some help, otherwise click OK, start with this.Automatic Mail Parsing Rule Set Up
  6. Now, we start setting up our Generic Webhook. Click Webhook Integrations in on the left menu, then click Add New Integration.
    Click Webhook Integrations
  7. Click Generic Webhook.Click Generic Webhook
  8. Give it a descriptive name and type in a sample URL (I used http://google.com) into the Target URL field. We need to use a sample first so that we can copy the webhook’s JSON payload. We then use this JSON payload to help generate the actual TargetURL from Azure Logic Apps in the next steps.Save And Test Webhook With Sample URL
  9. Next, click Save and test.
  10. Then Send test data. We expect this to fail, though it will give us the JSON payload.Send Test Data With Sample URL
  11. Copy the text from Body Payload into Notepad or Visual Studio Code.Sample URL Fails, Get Body Payload

Set up the Azure Logic App

  1. Log onto Azure at portal.azure.com. If you don’t already have a subscription, you can sign up using your Office 365 account.
  2. Click New, search for Logic App, and click Logic AppSearch For Logic App
  3. Click CreateCreate Logic App
  4. Complete the fields, placing the Azure Logic App in the region of your choice. You can name the Resource group whatever you like, or use an existing one. Click Create.Enter Logic App Details
  5. Click Edit to start editing your logic app.Edit Logic App
  6. Search for Request and click the Request TriggerCreate Request Trigger
  7. Now you can use your copied JSON Body Payload from MailParser.io as a reference for your Request Body JSON Schema.You’ll need to define the data type for each Key-Value Pair in your JSON payload. This allows you to use the separate fields in your Azure Logic App, and add the field data into the appropriate SharePoint columns.The syntax of the Request Body JSON Schema is as follows:
{
    "type": "object", 
    "properties": {
        "name": {
            "type" : " string"
            },
        "email": {
            "type" : " string"
            }
    },  
    "required":["name", "email"]
} 

You can use Visual Studio Code, Notepad++ or Notepad to edit this schema so that it describes your JSON Payload.

Replace the properties values with the name of the keys in your JSON payload. Not all fields need to be added to the required array, only the ones that you need to create a valid SharePoint list entry.

In my case, this JSON body becomes the following JSON Schema.JSON Body In Visual Studio Code
JSON Request Body Schema

  1. Paste the Schema into the Request Body Schema and click Save.Save Request To Get POST URL
  2. You will then receive the URL that you can use in Mailparser.io to send your requests:
  3. Next click + New step.Add New Step To Logic App
  4. Type SharePoint and click SharePoint – Create item.Create SharePoint List Item
  5. You may need to add a Connection to SharePoint Online. If you’re prompted, add a connection using an Office 365 account that has permission to write to the required SharePoint list. If you don’t have a SharePoint list available to accept the data, you’ll need to set one up now before proceeding.
  6. Next enter your site URL. The List Name drop down will be populated with the available lists. You should also see that the Outputs from the Request step are available to use.Enter SharePoint Site And List Details
  7. The list columns that can accept strings, as well as a few other column types will be available for you to modify. Click in each relevant column and select the relevant output.Add Outputs To SharePoint List
  8. Once you’re finished, go back to the Request Step in your Logic App and copy the URL from the Request stepCopy Request URL
  9. Return to MailParser.io, go back to Webhook integrations, and click Edit.Edit Webhook Integration
  10. Paste the URL from your Logic App Request step into the Target URL.Update Webhook Target URL
  11. Click Save and test.
  12. Click Send test data.Test Custom Webhook
  13. You should receive a response code of 202 to confirm it was sent successfully.Confirm Webhook Works
  14. You can now check Azure Logic Apps to confirm that it ran correctly.Logic App Runs Correctly
  15. You should also see the new entry in your SharePoint Online list.New Item In SharePoint

Setting up the Outlook Rule

Once you’ve confirmed it’s working, you can set up your mail rules in Outlook or via Exchange to automatically forward emails to your mailparser.io email address.

  1. Right click on an email sent via your web form. Click Rules, then Create rule.Right Click Rules Create Rule
  2. Choose a condition that matches all emails sent via your form, eg. Subject. Then click Advanced Options…Tick Subject Click Advanced Options
  3. Click Next.Click Next On Outlook Rule Wizard
  4. Tick forward it to people or public group, then click people or public group.Forward To People Or Public Group
  5. Enter the email address from Mailparser.io, click OK, then click Next twice.Paste Email From Mail Parser
  6. Turn on the rule, and choose whether you want to run it on mail already in the same folder.Turn On Outlook Rule

And that’s it. From now on, any mail sent by your website’s form will be automatically forwarded into mailparser.io, broken up into the relevant fields, and added to SharePoint Online. You can also use Azure Logic Apps to automate a bunch of other business processes. Check out the documentation here.

Similar services to Azure Logic Apps include Microsoft Flow, Zapier and IFTTT.

,

How to add your branding to Office 365 login screens

Custom Office 365 Login Screen Branding

You can add your own branding to your users Office 365 login screens via the Azure admin portal. The level of customisation that you get depends on the version of Azure Active Directory that you’ve signed up for.

What’s Azure Active Directory?

Azure Active Directory might not sound familiar to you, though it underpins every Office 365 organisation. All of your Office 365 users are stored in your Azure Active Directory, which is included in Office 365.

How to set up the custom branding for Office 365 login screens

  1. Login to Office 365 as an administrator at https://portal.office.com
  2. Click the App Launcher/Waffle button on the top left of the screenOpen Office 365 App Launcher To Start SetupOfBranding
  3. Click the Admin tileOpen Office 365 Admin Center To Setup Branding
  4. On the left menu, scroll down to Azure ADOpen Azure AD To Customise Office 365 Login Branding
  5. You may need to sign up for an Azure subscription, though you won’t have to put any credit card details in yet.
  6. Click Active Directory on the left menu once you’ve signed up.Select Azure Active Directory
  7. Click the Active Directory that has the same name as your Office 365 subscription, you will likely only have one listed here. Ours is called Ozbizweb Group.Open Azure Active Directory
  8. Click Configure, then click Customize BrandingConfigure Azure Active Directory And Customise Branding

The Customize Default Branding section gives you a few options to customise your Office 365 login experience.Customise Office 365 Default Branding

Size: 280 x 60 (Max is 300 x 60)

The Banner logo is the logo that appears above the Office 365 login fields. It supports JPEG, though a PNG image with a transparent background is recommended.

Size: 240 x 240

The square logo is used to represent user accounts in your organisation, on Azure AD web UI and in Windows 10. You should also add this one as a PNG with a transparent background, though JPEG is also supported.

Square Logo, Dark theme

Size: 240 x 240

You can upload a separate image for this one if the previous square logo won’t look good on dark backgrounds. Again, PNG with a transparent is recommended, though JPEG is supported.

User ID Placeholder

Typically, this is [email protected] and is shown in the user ID input field. You can replace this with [email protected] if you like. Though you should leave it blank if you’re making apps using Azure AD that will support external users.

Sign In Page Text Heading

This heading will appear above the customised sign in page text which can appear at the bottom of the Office 365 Sign in screen.

Sign In Page Text Body

This can be a short message at the bottom of the Office 365 login screen that can give more information to the users. You can use this to display welcome text, information about password resets, or contacting the helpdesk. This one can’t be longer than 500 characters (250-300 characters recommended).

Click the arrow to proceed to the next screen. This is where you can add the background image that will appear when a user enters their username.Customise Second Page OfOffice 365 Default Branding

Sign In Page Illustration

Size: 1420 x 1200

The message from Microsoft here is to use an abstract illustration or picture. Since the image gets resized and cropped, avoid using rasterized text and keep the “interesting” part of the illustration in the top-left corner. It can be a JPEG, GIF or PNG and should be about 300kb in size. Max size is 500kb.

Sign In Background Colour

Certain users may connect to Office 365 on low bandwidth connections, so you can customise the background colour of the Sign In Page Illustration section which will appear while the image loads. The default is the Office 365 orange/red, though this can be set to a hexadecimal colour code that suits your brand eg #73A2D1

Show or Hide Keep me signed in or KMSI

If you don’t want your users to choose to stay signed into devices, you can hide the ‘Keep me signed in’ checkbox here.

Post Logout Link Label

This is the link text that will appear when your users log out of Office 365. eg. Return to Ozbizweb Group website.

Post Logout Link URL

This is the URL that you’d like the Post Logout Link Label to redirect your users too. Eg. http://www.ozbizweb.com.au

Once you’ve applied your changes, click the Tick icon and wait for the changes to be applied.

Changes Are Saved For Office 365 Branding

 

You can test them out in an In-Private window (CTRL-Shift-P in Edge/Internet Explorer, CTRL-Shift-N in Google Chrome). Just enter your email address, and you should see some of the changes are taking effect. Note that it may take up to an hour to see your branding changes.

The free tier of Azure Active Directory doesn’t appear to display all the customised branding elements. Though some of them, like the Banner Logo and custom background colour before the Sign In Illustration loads, do appear.

Some Office 365 Branding Changes Available For Free

Want to see all your Office 365 login screen branding?

Displaying all of your branding changes is one of the many benefits of Azure Active Directory Premium, and the good news is that you can try it out for free. If you decide that you don’t want to go ahead with Azure Active Directory Premium for all your users, and just want it for the branding, you can assign it to just one user. This seems to enable all the custom branding elements for all users in your organisation.

Here’s how to activate an Azure Active Directory Premium Trial

  1. In the Azure portal, click the quick start cloud icon.Open Quick Start In Azure Active Directory
  2. Scroll down to Get Azure AD Premium and click Try it now.Try Azure Active Directory Premium
  3. Click Activate Trial and the bottom of the screen.Activate Trial For Azure Active Directory Premium
  4. Click the tick to get started. This trial will not auto-renew, and you won’t be charged after the 30 days are up.Activate Azure Active Directory Premium Trial
  5. Wait for the trial to start.Starting Azure Active Directory Premium Trial
  6. Make sure the Azure Active Directory Premium plan is selected and click Assign at the bottom of the screen.Assign Azure Active Directory Premium Licenses
  7. You will be presented with a list of users in your organisation, mouse over the users and click the + icon to select them for license assignment.Choose Users For Azure Active Directory Premium
  8. Click the tick icon and wait for the License plan operation to complete.Licenses Assigned For Azure Active Directory Premium
  9. Open an In-Private window, navigate to https://portal.office.com and enter a user’s email address. You should see all the branding changes you’ve implemented are now active!Office 365 Branding Changes Are Applied
, ,

Phone Call Business Intelligence with Power BI and FoneBox

Microsoft Power BI lets you build dashboards and interactive reports from your existing data. It can connect to pretty much any data source and also comes with a bunch of built in connectors to services like Google Analytics, Facebook, Quickbooks Online etc.

We’ve been working on a range of Power BI connectors for our accommodation provider clients, providing them with updating business intelligence dashboards to monitor their Xero Accounting, NewBook booking data, Office 365 activity, MailChimp, Facebook, and now their phone calls.

Since phone numbers are unique, this data can be matched against existing customer data in Power BI, so our clients can find out which customers are calling, how often, and for how long.

This is a sample dashboard I put together this morning using the phone data we’re importing for a Queensland tourist park. The data comes from FoneDynamics, an online call tracking and analytics service based in Australia. Power BI Dashboard Phone Calls

Clicking on dashboard elements opens the interactive Power BI reports. From here, you can drill down into the data and see how the elements relate to one another. In this example, we’ve selected ‘No’ under the Repeat callers chart. This gives us some pretty detailed information about the first time callers into the tourist park. This could include how long they spent on the phone, how many first time callers went unanswered, how long it took to answer these calls etc.Phone Call Business Intelligence Dashboard

Since we’re using Power BI, we can open and interact with these reports on any device. Here’s the same dashboard running on an iPhone, where you can open the charts and set alerts on important data. For example, get notified when the missed calls for the month exceeds a certain number.FoneBox Power BI Dashboard

The tech stuff

For this solution, we had to pull the data from a collection of CSV files stored on an FTP site. Power BI will connect to CSV files out of the box, though not via FTP and it won’t join multiple CSVs into a single dataset.CSVs For Import Into Power BI

I wrote a console app last night to connect to the FTP site, pick up each CSV, collect the phone records from it and upload them into an Azure Storage Table.Extract Phone Data for Business Intelligence On PowerBI

Here’s the data in an Azure Storage Table.

Power BI Phone Data On Azure Storage

Once the data for the previous day’s phone records is uploaded into Azure Table Storage, I set up an Azure Web Job to run the process once a day.Azure Web Jobs To Power BI

Now that we have a regular, up to date source of phone data, we can connect to it using Power BI Desktop app. We can use this app to create our reports before publishing them to Power BI Online.Connect To Azure Table Storage Via PowerBI

If you’d like to learn more about setting up Business Intelligence dashboards for your data, send an email with what you have in mind and we might be able to make it happen.

,

How to Restore an Azure Virtual Machine from Backup

Once you’ve setup Azure Backup to protect your Azure Virtual Machine (see this guide for more info), the process to restore it is quite simple. I recommend running test restores on a regular schedule to make sure it’s working correctly.

To restore an instance of your Azure Virtual Machine follow this quick guide.

  1. Log into https://manage.windowsazure.com
  2. In the left side menu, select “Recovery Services” Restore Azure Virtual Machine from Azure Backup
  3. Click to open the backup vault you created for the virtual machine.
  4. Click the “Protected items” tab across the top. Open Protected Items Under Azure Recovery Services
  5. Click “Restore” down the bottom to restore the selected virtual machine.Restoring Azure Virtual Machine Under Azure Recovery Services
  6. Select a recovery point and click next.
  7. Fill out the restore instance details. You will need to give it a unique name, but make sure you select the appropriate cloud service, storage account, and virtual network if you’re following this in a disaster recovery scenario.Choose Azure Virtual Machine Restore Instance
  8. Click the tick in the bottom right to start the restore process
  9. Once the restore is completed you can download the new RDP connect file and test.
,

Protect an Azure Virtual Machine with Azure Backup

 

If you’re running Azure Virtual Machines in production, you’ll probably want to protect them with Azure Backup. The good news is Azure provides a simple way to protect an entire virtual machine, so you can easily restore it if things go wrong.

Protecting Virtual Machine instances differs from the typical Azure Backup client that is usually installed on client PCs and On-premises servers.

Here’s how to set Azure Backup for an Azure Virtual Machine:

  1. Log into https://manage.windowsazure.com
  2. Open Recovery ServicesBackup Azure VirtualMachine With Recovery Services
  3. Click NewNew Azure Backup Vault
  4. Choose Backup Vault, Quick Create, give it a name and choose to place it in the same region as the virtual machine you’ll be protecting. Quick Create Azure Backup Vault In Azure Recovery Services
  5. The backup vault will appear in Recovery ServicesAzure Recovery Services To Backup Azure Virtual Machines
  6. Click on the vault and scroll down to Protect Azure Virtual Machines. Backup Azure Virtual Machine
  7. Click Discover Virtual Machines. It will take a view minutes. Once discovered, you’ll be notified that virtual machines were found in the same region. Register Azure Virtual Machine
  8. Click Register and choose the Virtual Machine that you want to protect.Registering Azure Server For Protection
  9. Wait for the Virtual Machine status to change to RegisteredRegistering Azure VirtualMachines For ProtectionRegistered Azure VirtualMachine For Protection
  1. Click ProtectProtect Azure Virtual Machine
  2. Choose the virtual machine you just registered.Selec tAzure Virtual Machine For Protection
  3. Choose the Default Policy Settings or configure your own and click Finish

 

,

Migrating files into Microsoft Azure Virtual Machines

We use Microsoft’s AzCopy to move large amounts of data from external sources into Microsoft Azure Storage.

The typical scenario for us is a customer who is moving onto an Azure Virtual Machine, and wants their data stored on that machine.

AzCopy is a versatile command line utility that allows you to move files from another PC or Server into Azure Storage, and then into your Azure virtual machine.

When migrating data to an Azure VM, the solution looks like this:Azcopy Process to Move Data Into Azure

To get started, you need to install AzCopy from here.
Next, create an Azure Storage Account. You can do this in either the old portal or the new portal.
Once you’ve created a storage account, you’ll need to create a storage container. Here is a quick guide for the old portal and the new portal.

Now that you’ve installed AzCopy, created a storage account and a container, you can put together your AzCopy commands. Typically you’ll create two commands, one that uploads your data into Azure Storage, and the other downloads your data from Azure Storage into your Azure Virtual Machine.

Here’s an example that will move local data from E:\NAS into Azure Storage, and then download it to E:\NAS on the destination virtual machine:

To Azure Blob Storage

AzCopy /Source:E:\NAS /Dest:https://<storageaccountname>.blob.core.windows.net/<containername> /DestKey:<LongStorageAccountKey> /S /V:C:\temp\NASDrive.log

From Azure Blob Storage

AzCopy /Source:https://<storageaccountname>.blob.core.windows.net/<containername> /Dest:E:\NAS /SourceKey:<LongStorageAccountKey> /S /V:C:\temp\NASDrive.log

These commands will also create a log file under C:\temp\NASDrive.log

Running the AzCopy commands

Running AzCopy To Migrate Data Into Azure

Open Command Prompt and navigate to the location where AzCopy was installed. Typically this is under “C:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy”

Paste in your first command and run it to start the upload. In the meantime, you can install AzCopy on the destination server.

Once the upload is complete, go to the destination server and run the second command to download the data from Azure Blob Storage.

If you experience errors

AzCopy Warning After Data Migration Into Azure

Occasionally you may experience errors in the AzCopy transfer (usually the upload), where certain files will fail. The solution for this is usually to append the /NC: parameter and run the command again. The /NC: parameter limits the number of concurrent connections to Azure storage. I usually set it to /NC:5, where 5 is the maximum number of concurrent files that will be uploaded. The upper limit of concurrent connections is 512.

Updated command for uploading to Azure Blob Storage

AzCopy /Source:E:\NAS /Dest:https://<storageaccountname>.blob.core.windows.net/<containername> /DestKey:<LongStorageAccountKey> /S /V:C:\temp\NASDrive.log /NC:5

If you’re rerunning the command, you will be asked whether you want to skip files that already exist. Choose to skip All.

Here’s the results of the second transfer.Azcopy Summary For Azure Data Migration You may notice that the number of files transferred in this image is different to the number of files that failed in the previous image. In this case, we removed some unnecessary files before restarting the upload.

AzCopy Documentation

For more information on AzCopy, see the documentation here.

 

,

How to create an Azure Storage Container in the old portal

Here’s how to create an Azure Storage Account Container in the old Microsoft Azure portal at https://manage.windowsazure.com, assuming you already have an Azure subscription and existing Azure Storage account.

  1. You will need an existing storage account. See this guide for how to create one.
  2. Login to https://manage.windowsazure.com
  3. Open your existing storage account.Open Azure Storage Account
  4. Click Containers then click Create a container:Create Azure Storage Container
  5. Choose a Name and Access type for it:New Azure Storage Container
  6. Click the tick and wait for the container to be created. It should just take a few seconds.Created Azure Storage Container
  7. You can now refer to this container from other services using https://<yourstorageaccountname>.blob.core.windows.net/<yourcontainername> 

    Depending on the Access type you chose (eg. private), you may need your storage account name and primary key to access this container. See the end of this guide for how to retrieve these.

 

,

How to create an Azure Storage Container in the new portal

Here’s how to create an Azure Storage Account Container, assuming you already have an Azure subscription and existing Azure Storage account.

  1. Firstly you’ll need a storage account. See this guide for how to create one.
  2. Open your storage account by signing into https://portal.azure.com
  3. Click BlobsOpen Azure Storage Account Blobs
  4. Click Containers in the Blob service blade.Azure Storage Container Creation
  5. Create the new container by clicking ‘+ Container’ then choosing a Name and Access type for it:Add Azure Storage Container
  6. You can now refer to this container from other services using https://<yourstorageaccountname>.blob.core.windows.net/<yourcontainername>
    Depending on the Access type you chose, you may need your storage account name and primary key. See the end of this guide for how to retrieve these.