Outlook on the web – Conditional Access

, , ,

Some companies will block access to Outlook on the web entirely because they don’t want users to be able to download their company data externally. This new feature strikes a middle ground, so users can still access Outlook on the web,  but admins can use conditional access to restrict downloads from Outlook on the web on personal or unmanaged devices.

What is Conditional Access?

Conditional access lets you define different security measures which take effect depending on how users are trying to access your company data. For example a risky sign in according to Azure Active Directory might prompt for MFA, while a sign in from inside your company network on a trusted device won’t. An unmanaged or non-compliant device might not be able to access certain apps, while compliant devices can.

How to set up Conditional Access for Outlook on the web

Add the policy via Azure Active Directory Conditional Access

In this example, we are setting up a conditional access policy for non-compliant devices which prevents users from being able to download attachments via the browser.

  1. Log in to portal.azure.com and open Azure Active Directory
  2. Click Conditional Access and create a new policy
  3. Under Users and groups, choose people or groups to apply the policy to.
  4. Click Cloud apps, choose Select apps, then search for and select Office 365 Exchange OnlineSelect Exchange Online Cloud App For Conditional Access
  5. Under Session, select Use app enforced restrictionsUse App Enforced Restrictions On Conditional Access

Configure the OWAMailboxPolicy via Powershell

  1. Connect to Exchange Online via Powershell
  2. Run the following cmdlet to get the name(s) of your current OWA Mailbox Policies.
    Get-OwaMailboxPolicy | ft Name

    Get-OwaMailboxPolicy Powershell for Conditional Access

  3. Run the following cmdlet to set the Conditional Access policy option on your OWA Mailbox policy to ReadOnly.
    Set-OwaMailboxPolicy -Identity OwaMailboxPolicy-Default -ConditionalAccessPolicy ReadOnly

    Setting OWAMailboxPolicy ViaPowerShell for Conditional Access

Valid values for the -ConditionalAccessPolicy parameter are:

  • Off: No conditional access policy is applied to Outlook on the web. This is the default value.
  • ReadOnly: Users can’t download attachments to their local computer, and can’t enable Offline Mode on non-compliant computers. They can still view attachments in the browser.
  • ReadOnlyPlusAttachmentsBlocked: All restrictions from ReadOnly apply, but users can’t view attachments in the browser.

Wait a few hours for the policy to apply. Once it takes effect, the previously selected users on non-compliant devices will not be able to download attachments via Outlook on the web.

What is the user experience?

The ReadOnly policy will ensure that users on non-compliant devices can’t download email attachments through Outlook on the web to their local device. They can only access them via the file viewers in the browser.

ReadOnly Outlook On The Web Conditional Access

If you use the ReadOnlyPlusAttachmentsBlocked value, users will not be able to access attachments via the browser at all.

What license do I need for Conditional Access for Outlook on the web?

Conditional Access requires a subscription with Azure AD P1 or P2.

Privileged access management in Office 365

, , ,

A compromised administrator account or an admin becoming a disgruntled ex-employee is a source of serious risk to a business. This is because traditionally admins can do whatever they want, whenever they want. To address this issue, Microsoft have developed Privileged Access Management.

What is Privileged Access Management?

Privileged Access Management works on the principle of zero standing access. That means that admins don’t have the ability to perform potentially damaging actions all of the time.

When they need to perform a task that may expose sensitive data or has potential to cause a lot of damage, they will be given just enough access to complete the task. And even then, only for a specific time and only following an audited approval process.

You can define which tasks require a privileged access request via the admin portal.

Create Privileged Access Policy

When admins want to perform one of these tasks, they can raise their requests for access via the portal or via Powershell.

A sample Powershell request to perform tasks requiring privileged access approval looks like this:

New-ElevatedAccessRequest -Task 'Exchange\New-JournalRule' -Reason 'Setting Journal per request.' -DurationHours 4

Privileged Access PowerShell RequestRequests can be automatically or manually approved, and requestors are notified of the approval outcome via email. All privileged access requests and approval process information is recorded for internal reviews and auditors.Privileged Access Request Email

Privileged Access Management License requirements

Privileged access management requires Microsoft 365 E5, Office 365 E5 or the standalone Advanced Compliance SKU.

 

Microsoft Secure Score support for new controls

, ,

Microsoft Secure Score has added new controls to support Microsoft Cloud App security and Azure Active Directory.

What is Microsoft Secure Score?

Microsoft Secure Score

Microsoft Secure Score is a solution that rates how well you’re leveraging security controls for Office 365, Microsoft 365 and Windows 10.

You can check your secure score, and see how you compare against similar businesses at https://securescore.microsoft.com.

New Azure Active Directory Secure Score controls

The new Azure Active Directory controls relate to how well your securing identities in your organization.

Enabling self-service password reset to empower users and reduce help desk costs

You can login to Azure AD to enable self service password reset for all, or just selected, users. You can choose the authentication measures (eg. phone number and alternate email) that users can use to reset their passwords. The policy can require that users register these details on next login, and also define a time period for users to reconfirm their info.

Require just in time access for global administrators using Privileged Identity Management

Privileged Identity Management works on the principal of zero standing access. In practice it means that by default, admins don’t have the ability to perform actions which expose sensitive data, or potentially cause harm. When an admin needs to perform one of these types of actions, they follow a set approval process and provide a justification. This process is audited, and upon approval, the admin is only granted access for a limited period of time. Privileged Identity Management can be enabled in the admin portal, provided you have a plan which includes Azure AD P2.

Turning on password hash sync

If you’re running a hybrid organisation, you can setup password hash sync. This will ensure that users can have the same password for Office 365 and Azure AD services that they use on-premise.

Enable user risk policies

Companies with Azure AD P2 can enable policies that can block access or prompt a user for MFA when a risky sign-in is detected. A risky sign in could be a login from an unexpected location or from a device infected with malware.

Some other important Azure AD controls include:

Require MFA for admins (and also users)

At Microsoft Ignite this year it was reported that only 2% of all admins in Office 365/Azure AD had multifactor authentication enabled. This control is scored quite high as multi-factor authentication makes your accounts 99.9% less likely to be compromised.

Every Office 365/Azure AD tenant gets a free conditional access baseline policy which requires MFA for all privileged roles in Office 365 and Azure AD. This policy will soon be enabled by default, however you can login here and require it be enabled immediately.

Disable stale accounts

Microsoft recommends that you disable any accounts that haven’t been used for the last 30 days. While there may be legitimate circumstances where an account is unused for 30 days, these accounts can also be targets for attackers who are looking to find ways to access your data without being noticed. See here for a list of inactive users in your organisation.

Have less than 5 global admins

You should designate less than 5 global admins in your organisation, even if they are all protected by MFA. The more admin users you have, the more likely it is that one of them is breached or ends up in the hands of a malicious insider. Admin roles in Office 365 should be assigned with the least privilege required for the admin to perform their tasks. Microsoft recommends that you do have at least 2 global admins however, to ensure you can recover from a breached account or rogue insider.

Don’t expire passwords

Setting passwords to expire encourages bad security practices when users store them unsafely or set insecure passwords with patterns. It’s best practice to require users to set stronger passwords which never expire.

What is Microsoft Cloud App Security

Microsoft Cloud App Security gives you a framework to secure your Microsoft and non-Microsoft cloud apps. You can use it to setup policies which alert on suspicious logins or behaviours are across apps like Office 365, Dropbox, Box, Salesforce and many more.

Microsoft Cloud App Security is available in Microsoft 365 E5 and in Office 365 E5 (as Office 365 Cloud App Security). We recommend you purchase it stand alone if you don’t have an E5 plan.

Microsoft Cloud App Security

Here are the new Secure Score controls for Cloud App Security:

Reviewing permissions and blocking risky OAuth applications

You can visit the App Permissions page for third party apps in Cloud App Security to see which permissions have been granted to access your company’s Office 365 data. Here, you can revoke permissions and prevent users from authorising these apps to access company info.

Reviewing anomaly detection policies

Anomaly detection policies use machine learning to detect suspicious activities amongst your users. They help you understand if users are logging in from locations that they normally don’t log in from, using anonymous IP addresses, and have multiple failed login attempts. Review them here.

Discover risky and non-compliant Shadow IT applications

Upload your firewall and proxy logs and use the cloud discovery dashboard to discover which applications are in use within your company. Cloud App Security has a rating system that can help determine the risk level of each application. Create a report here.

Creating custom activity policies to discover risky behaviour

In Cloud App Security you can create custom policies as well as take advantage of some of the built-in defaults. These policies can detect and alert when there are suspicious activities like mass downloads or deletions across your Microsoft and third party cloud apps.

Automating video creation with Azure Functions, Azure Media Services and Microsoft Flow

,

So this is my first video post about a Microsoft 365 roadmap update.

If you follow me on LinkedIn, you might have noticed I’ve been doing a bunch of different updates lately for the Microsoft 365 roadmap. I do this because it’s fun to see all the things that are changing and being added to the platform.

The way that those updates work is that I have an Azure function checking the roadmap API every few hours and comparing it against a version I have in a Cosmos DB database. When it finds a new or a changed feature on the roadmap, it creates a picture using an API from Imgix and starts a Microsoft Flow approval process asking for my notes. Once approved, the image and those notes are pushed on to Buffer which posts the update on my social media.

I wanted to see if I could do the same thing with video so I’ve extended that solution a bit.

Now, when I’m prompted to add notes to a roadmap update I’m also prompted to add a video to a newly generated OneDrive folder. When I add the video of me discussing the update and approve the Microsoft Flow request, another Azure Function takes the video from OneDrive and sends that over to Azure Media Services. It’s then encoded to a smaller size and  automatically transcribed. It then sends me the generated subtitles, which I can correct on my phone and then approve. Once corrected, the subtitles and the encoded video are sent over to a service called Cloudinary, which has a cool video editing API.

I’ve made a bunch of different transitions and animated logos depending on which service the update is tagged with, so that each video is a little bit customised. Finally, another function makes the video via the Cloudinary API.

The cool thing about this solution is that it’s written entirely in Powershell. I’m using Azure Functions here because they make it easy to build these automated solutions using a language that I’m familiar with. So the end result is I can create a nicely formatted social media video with hardcoded subtitles from my phone. See an example of this above.

The other thing that I can do with my phone, is sign in using the Microsoft Authenticator app with passwordless sign in. Which is what this update’s about.

It’s actually really easy to setup, you can follow the instructions on this post and help your users reach a more secure, password-less future.

Connect Gravity Forms to Microsoft Flow

Connect Gravity Forms to Microsoft Flow with Azure Functions

, ,

Connect Gravity Forms to Microsoft Flow

We use Gravity Forms on our website and it works pretty well. Whenever a form is completed, we receive an email – except sometimes we don’t. Just recently, I missed a few enquiries because of a configuration change on our site.

To stop this from happening, I went looking for alternative notification options for Gravity Forms that didn’t just rely on an email making it from our website to my inbox. I remembered that Zapier had a connector, however I was disappointed to discover that it only works on Developer licenses which cost $199 USD a year, and we’re running a $39 single site license.

Luckily Gravity Forms has some easy to follow API documentation that allow us to connect directly to our site’s forms and entries via REST methods.

This solution demonstrates how to build an Azure Function app in C# that retrieves the entries from a Gravity Form and sends them to Microsoft Flow. A Microsoft Flow checks each entry against a SharePoint list and if it doesn’t exist, it adds it.

The benefits of this solution is that it’s completely serverless and almost free (depending on the App Service plan). Also since it’s in Microsoft Flow, you can do anything you want with the form entries. You could create a task in Planner, add a message to Microsoft Teams channel, or pipe them directly into Dynamics 365 or Mailchimp.

The first step is to enable access to your Gravity Forms via the API.

Enable the Gravity Forms API and retrieve the form info

  1. Sign into your site’s WordPress Admin Panel and visit the Settings section of Gravity Forms
  2. Enable the API. Retrieve and make a note of the Public API Key and Private API Key.Enable Gravity Form sAPI
  3. Set the Impersonate account user. Your Function App will have the same form access permissions as the user that you choose here
  4. Visit the form that you’d like to retrieve the entries for and make a note of the Form ID (eg. 1)Retrieve Field Info From Form
  5. Make a note of all the fields in the form. We’ll be adding these as columns to a SharePoint List.
  6. It’s also worth making a note of each field’s corresponding ID (eg. 1.3, 2, 3 etc) since the JSON representation of each field uses this and not the field name.Get Field IDs From Gravity Forms

Create a SharePoint List to receive the form data

  1. Sign into your SharePoint site with your Office 365 Account.
  2. Visit Site Contents and create a new SharePoint list with an appropriate name.Create SharePoint List Under Site Contents
  3. You can rename the Title column to something else if Title isn’t appropriate. I changed mine to First Name.Rename Title Column In SharePoint
  4. Create columns to match the field names in your Gravity Forms form. Here’s the field names and types that we’re using:Column Details For Gravity Forms

Create a Function App in Visual Studio 2017

In previous tutorials, we’ve created Azure Functions directly in the browser. This time we’ll be using Visual Studio to test and deploy our functions.

Open Visual Studio 2017 and make sure that it’s up to at least version 15.3. You’ll need to ensure that Azure Development tooling is installed, and that you can create Azure Function Apps. See here for a list of prerequisites.

  1. Go to File, New Project, Visual C#, Cloud, Azure Functions then create a Function App and give it a name.Create New Azure Function App

If Visual Studio is completely up to date, you’ve installed all the prerequisites, but you still can’t see an option to create Azure Functions, you may need to go to Tools > Extensions and Updates > Updates > Visual Studio Marketplace and install the Azure Functions and Web Jobs Tools update.Update Azure Functions

  1. An Azure Function app is pretty much an Azure Web App, and each Function App can contain multiple functions. To add a Function to our Function App, right click on your project in the Solution Explorer, choose Add, New Item.Add New Item To Project In Visual Studio
  2. Then select Azure Function and give your function a name – I’ve called this one GravityForms_Enquiries. Click Add.Add New Azure Function To Project
  3. Choose Timer trigger. You’ll also want to specify how often you’d like the function app to run using CRON scheduling. The default value means that your function will execute every 5 minutes. In our published function, we’re going to check for form entries every 4 hours. While we’re debugging, we’re checking every minute – just until we’re ready to publish.Create Timer Triggered CSharp Function
  4. Your Function should look like thisCreatedAzure Function In Visual Studio
  5. Copy and paste the following code into your function. Replace the string placeholders in the RunAsync method with your own values, and make sure that you update your namespace and function app name (if you didn’t choose GravityForms_Enquiries too).
using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web;
using System.Security.Cryptography;
using System.Net.Http.Headers;
using System.Text;

namespace GCITSFunctions
{
    public static class GravityForms_Enquiries
    {
        [FunctionName("GravityForms_Enquiries")]
        public static void Run([TimerTrigger("0 0 */4 * * *")]TimerInfo myTimer, TraceWriter log)
        {
            //Change the Timer Trigger above to "0 * * * * *" when debugging to avoid waiting too long for it to execute.

            log.Info($"C# Timer trigger function executed at: {DateTime.Now}");
            
            string content = RunAsync().Result;

            // Remove the comments from the below four lines once you've retrieved the HTTP POST URL from Microsoft Flow and added it in.
            //HttpClient client = new HttpClient();
            //HttpContent jsoncontent = new StringContent(content, Encoding.UTF8, "application/json");
            //string flowRequest = "<Enter flow HTTP POST URL HERE>";
            //var result = client.PostAsync(flowRequest, jsoncontent).Result;
        }

        
        static async Task<string> RunAsync()
        {
            HttpClient client = new HttpClient();
            // Add the public and private keys for Gravity Forms
            string publicKey = "<Enter Gravity Forms Public API Key>";
            string privateKey = "<Enter Gravity Forms Private API Key>";
            string method = "GET";
            // Specify the form ID of the form you're retrieving entries for
            string formId = "1";
            string route = string.Format("forms/{0}/entries", formId);
            /* Paging specifies the number of entries that will be retrieved from your form in this call, eg. 1000. You can make this higher or lower if you like. 
            It will retrieve the most recent entries first. */
            string paging = "&paging[page_size]=1000";
            string expires = Security.UtcTimestamp(new TimeSpan(0, 1, 0)).ToString();
            string signature = GenerateSignature(publicKey, privateKey, method, route);
            /* Replace gcits.com with your own domain name. If the call doesn't work initially, you may need to make sure that 'pretty' permalinks are enabled on your site.
            See here for more information: https://www.gravityhelp.com/documentation/article/web-api/ */
            string url = string.Format("//gcits.com/gravityformsapi/{0}?api_key={1}&signature={2}&expires={3}{4}", route, publicKey, signature, expires, paging);
            client.BaseAddress = new Uri(url);
            client.DefaultRequestHeaders.Accept.Clear();
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            var response = await client.GetAsync(client.BaseAddress);

            string content = response.Content.ReadAsStringAsync().Result;
            return content;

        }

        public static string GenerateSignature(string publicKey, string privateKey, string method, string route)
        {
            string expires = Security.UtcTimestamp(new TimeSpan(0, 1, 0)).ToString();
            string stringToSign = string.Format("{0}:{1}:{2}:{3}", publicKey, method, route, expires);
            var sig = Security.Sign(stringToSign, privateKey);
            return (sig);
        }


    }

    public class Security
    {

        public static string UrlEncodeTo64(byte[] bytesToEncode)
        {
            string returnValue
                = System.Convert.ToBase64String(bytesToEncode);

            return HttpUtility.UrlEncode(returnValue);
        }

        public static string Sign(string value, string key)
        {
            using (var hmac = new HMACSHA1(Encoding.ASCII.GetBytes(key)))
            {
                return UrlEncodeTo64(hmac.ComputeHash(Encoding.ASCII.GetBytes(value)));
            }
        }

        public static int UtcTimestamp(TimeSpan timeSpanToAdd)
        {
            TimeSpan ts = (DateTime.UtcNow.Add(timeSpanToAdd) - new DateTime(1970, 1, 1, 0, 0, 0));
            int expires_int = (int)ts.TotalSeconds;
            return expires_int;
        }
    }
}

  1. Update the TimerTrigger to ‘0 * * * * *’ while we debugUpdate Timer Trigger For Debugging
  2. Add a reference to your function for System.Web by right clicking on your project and choosing Add, Reference. Add Reference To Azure Function App
  3. Scroll down to System.Web, check the box and click OK.Choose System.Web Reference
  4. Next we need to add a connection string for an Azure Storage account into the local.settings.json file. You can retrieve a connection string from an existing storage account via the Azure Portal, or by downloading Azure Storage Explorer from www.storageexplorer.com, signing in and copying the Connection String from the bottom left properties section. I recommend downloading Storage Explorer anyway since it’s a handy tool for working with Azure Storage accounts. If you don’t have a storage account, you’ll need to make one.Copy Connection String From Azure Storage Explorer
  5. Once you’ve got the Connection string, paste it in the AzureWebJobsStorage value of the local.settings.json file.Add Azure Storage Connection String

Get your Gravity Form entries as JSON

In order for us to use your form entries in Microsoft Flow, we’ll need to show Flow what your form entries look like in JSON format. To do this, we’ll use Fiddler.

  1. Download Fiddler from www.telerik.com/fiddler and install and run it.
  2. Fiddler allows you to analyse your computer’s internet traffic, as well as a bunch of other things. You might see a lot of activity from irrelevant processes, you can right click and filter these processes out.Filter Processes In Fiddler
  3. Once your Fiddler stream is a little less busy, we’ll run the function app. Return to Visual Studio and press F5.
  4. You’ll see the Azure Functions Core Tools window appear. This is a local version of the Azure Functions runtime that allows you to debug Azure Functions on your own computer before you deploy them.Start Azure Functions Core Tools
  5. Wait for your Azure function to execute. It should display some text that looks like this:Run Azure Function And Confirm
  6. Now switch over to Fiddler and locate the call that it just made to your website. If all goes well, you should see a row with a result of 200 to your domain. Locate Call To Gravity Forms Endpoint In Fiddler
  7. Click this row, and choose to decode it on the bottom right.Decode Fiddler Response Body
  8. Select the Raw tab, and triple click the JSON result at the bottom to select it all, then copy this into Notepad for later. This is the JSON representation of your Gravity Forms form entries that we can use in Microsoft Flow.Copy JSON Payload From Fiddler Raw Tab

Create a Microsoft Flow to receive the Gravity Forms entries

  1. Visit flow.microsoft.com and sign in with your Office 365 account.
  2. Create a new Blank flow, give it a name and start it with a Request Trigger. Then click Use sample payload to generate schemaStart Microsoft Flow With Request Trigger
  3. Paste the JSON payload that we saved from Fiddler and click Done.Paste JSON Payload In Microsoft Flow
  4. Add an action step so that we can save the flow and retrieve the HTTP POST URL. In this example I added a Notification action.Add Sample Action To Save Flow
  5. Click Create Flow, then copy the URL that was created next to HTTP POST URL.Copy HTTP POST URL From Microsoft Flow
  6. Switch back over to Visual Studio 2017 and paste the HTTP POST URL in the placeholder for the flowRequest string variable. Next uncomment out the last four lines of the Run method.Update Azure Function With Flow Request URL
  7. Run the Function again to confirm that it’s sending the JSON payload to Microsoft Flow. You should see a row in Fiddler that looks like this:Confirm Function App Can Reach Microsoft Flow
  8. Inspecting the call on the top right under the Raw tab shows that it sent the JSON payload:Inspect Function Call To Microsoft Flow
  9. When you return to Microsoft Flow, you should see a recent successful Flow run.Confirm Flow Received Function Call And Ran
  10. Open the flow run to see that the payload was received by the Request step.Results Of HTTP Request Call

Use Microsoft Flow to add the entries to a SharePoint list

  1. Remove the action below the Request trigger and add an Apply to each step. Add the entries output from the popout menu to the ‘Select an output’ field. Then add a SharePoint – Get Items action into the Apply to each step and select or enter your SharePoint site, then choose the SharePoint list you created earlier.Get Items From SharePoint In Apply To Each
  2. Click Show advanced options and add GFID eq ‘id’ into the Filter Query field. Where ‘id’ is the id output from the Request trigger. Be sure to include the single quotes. This step checks the SharePoint List for existing entries with the same Gravity Forms entry ID.Filter Get Items By Gravity Forms ID
  3. Next add a Condition step, and click Edit in advanced mode. Copy and paste the following into this field:
    @empty(body('Get_items')?['value'])

    Check If Returned Items Are Empty

  4. This checks whether any items were returned from SharePoint that match that Gravity Forms ID. If there weren’t any, we’ll create one.
  5. Your flow layout should now look like this:Structure Of Completed Microsoft Flow
  6. In the Yes section, add a SharePoint – Create Item action. Select or enter your SharePoint site, and choose the relevant SharePoint list. Refer to your notes on which fields match which field IDs, then drag the form values into the corresponding SharePoint fields.If Yes Create SharePoint Item In Microsoft Flow
  7. I also added an Office 365 – Send an email action below this one so I get an extra email notification. You may want to wait until you’ve already imported all existing form entries before you add this one. If you’ve got hundreds of entries that haven’t been added to SharePoint, you’ll get hundreds of emails.Send Email Notification With Gravity Forms Entry
  8. Click Update Flow, return to Visual Studio 2017 and run your Function app again (press F5).
  9. Once it successfully completes, close the Azure Functions Core Tools window and head back over to Microsoft Flow to see it process. It should display the following when it’s done:Wait For Flow To Run
  10. Next, visit your SharePoint list. You should now have the data from all Website Enquiry form entries in a single location. This data can now be used for all sorts of Microsoft Flows and business processes.Form Entries In SharePoint List

Publish your Function App to Azure

To make sure that your form data stays up to date, we need to publish our Function App to Azure.

  1. Switch to Visual Studio, and update the timer on your function app to ‘0 0 */4 * * *‘ to make sure it doesn’t keep running each minute in the cloud
  2. Now, right click on your project name and click PublishPublish Azure Function App
  3. Click Azure Function App, and choose Create New. (If you already have an existing Azure Function App, you can Select Existing, and specify the function app you’d like to deploy to.)Publish To New Azure Function App
  4. Since we’re creating a new Azure Function App we need to specify some details. As mentioned earlier, Function Apps are just Azure Web Apps. To deploy them, we need to create or choose an App Service.
  5. Give your Function App an App Name, select your Azure subscription, choose or create a Resource Group, App Service Plan and Storage Account.Create Azure App Service
  6. When creating an App Service plan, you can choose from the following sizes. Pricing varies depending on the underlying VM size, however the Consumption plan costs pretty much nothing. Choosing Consumption doesn’t give you a dedicated VM, and function runs are limited to 5 minutes duration. This applies to all functions within your Function App.Choose Azure App Service Plan
  7. Once you’re happy with your settings, click OK, then Create, and wait for your App Service to deploy.Wait For Function App To Deploy
  8. When it finishes, click Publish. Your function app is now deploying to Azure.Click Publish To Publish Function To Azure
  9. Sign in to https://portal.azure.com to see it in action under Web Apps. By default, your functions are in Read Only mode, and you won’t be able to view or edit the underlying C# code.Open Function App In Azure Portal
  10. To keep track of your function’s activity, you can see function runs in the Monitor Section.See Run History Of Azure Functions
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

Banner Logo

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.

Square Logo

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 someone@example.com and is shown in the user ID input field. You can replace this with someone@yourdomain.com 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.