Monday, 2 August 2021

Bring external data into Microsoft 365 using Graph Connectors - a ServiceNow example

Microsoft 365 has become the centre of the digital universe for many organisations, and it makes sense to explore the integration of other platforms and data sources so that the employee experience is simpler and less fragmented. A great example is search - perhaps most of the organisation's documents are now in Microsoft 365, but when technical knowledge base articles are stored in ServiceNow, client data in Salesforce and there's still a file share of archived (but still useful) content, the net result can be a lot of context switching and time lost to searching. No wonder those McKinsey and IDC research papers suggest an average knowledge worker spends 20-30% of their time simply looking for information. 

The benefits of providing a consolidated search experience often increase as each additional location is integrated - great things can happen when data in a certain system is made more accessible or put in the line of sight of new audiences. The sales and marketing teams might spend all day in CRM, but when client data is put in front of other groups in the company that can help others make better decisions too. In Microsoft 365, the Graph Connectors framework makes this possible - so that's what we're looking at here. 

This article comes in two parts:
  • Bring external data into Microsoft 365 using Graph Connectors (this article)
  • Microsoft 365 Graph Connectors - licensing, customising search results and more (available soon)

For us here at Content+Cloud, a good example of a valuable content source is ServiceNow - it's the platform that powers the Managed Services Provider side of our business, providing core ITSM capabilities around problem/incident/change management as well as various forms of automation, AI and analytics which help us maintain a great service. Something that ServiceNow does quite well is knowledge base tooling - which isn't the easiest thing for me to say as a Microsoft person immersed in Viva Topics and SharePoint Syntex. But it's true - ServiceNow has all sorts of concepts like the Article Quality Index scoring mechanism, the ability to create knowledge from tasks easily and identify possible duplicates - capabilities that suit it's role in providing curated knowledge to support engineers where misinformation can be disastrous. We follow Microsoft and ServiceNow integration closely here at C+C, and there have been LOTS of recent developments - one example is a Microsoft Graph Connector for ServiceNow, and when this became available I knew it would be something useful for us. 

Integrating external data with Microsoft Graph Connectors

At the time of writing Microsoft provide nine native connectors:

In addition, the Microsoft Graph connectors gallery provides a showcase of 3rd party connectors created by other vendors - usually these are paid products, but allow you to go beyond what's possible with Microsoft's connectors. Finally, it's possible to create your own Graph connector as illustrated by the Graph connector GitHub sample

A single framework and approach for all sources - not just ServiceNow
In this article I use ServiceNow as the external data to bring into Microsoft 365, but with Graph connectors the process is 90% the same regardless of data source. So if you want to connect to Azure SQL, Azure Data Lake, a file share, Salesforce or any of the other above sources, the process is much the same. 

The work you do on the non-Microsoft 365 side is generally around creating an app registration or account for the connection to use. You might need to take care of additional things around authorisation and permissions, but there's some flexibility in this in the Graph connector side too. 

Improving search and discoverability through integration 

Being able to index content in other systems allows you to provide a consolidated experience, with the additional location appearing as a search vertical (tab) in the results. You can control how results are displayed, including changing the pieces of data shown and the formatting. Clicking the tab runs your search across this content source (i.e. ServiceNow in my case): 

Viva Topics and Graph connectors
Being able to search and discover across other sources is powerful, but in the future Viva Topics will also be able to use external sources for knowledge gathering. This has the potential to make a huge impact in organisations where authoritative content is split across multiple repositories. Viva Topics will not only do the hard work of identifying, organising and surfacing knowledge across platforms - but will also "bring the knowledge to where the user is" through the topic cards which appear in Teams, Outlook and SharePoint. That could be very compelling indeed as a way to unify platforms and ensure data is brought into the core digital workplace experience.

Configuring the link between Microsoft 365 and ServiceNow

Configuring an OAuth app registration in ServiceNow

In the case of ServiceNow, the first step is to create an OAuth endpoint in ServiceNow for your Graph connector to use. This is documented at ServiceNow Graph connector for Microsoft Search. In my case I'm using a ServiceNow PDI (Personal Developer Instance) and the authentication type I used is ServiceNow OAuth. Here's what my ServiceNow endpoint looks like:

In ServiceNow authZ/authN, a ServiceNow account is also required for the connection - this needs to have access to the knowledge role in the platform. The app registration and the identity are used together in authentication.

Once you have ServiceNow side in place, it's time to create the Graph connector in Microsoft 365.

Creating a Graph connector in Microsoft 365

To start, head into the Admin Center and into Settings > Search & Intelligence.

Look for the 'Data sources' tab and click 'Add':

Select ServiceNow as the data source:

On the next screen you supply a name, ID and description for the connection - these can be any values you like but the ID can only contain alphanumeric characters:

The next screen is where the actual ServiceNow instance details are specified:

When the 'Sign-in' button is clicked a pop-up window will appear for you to login and provide in ServiceNow - the credentials to use at this point are those for the identity which has the knowledge role in ServiceNow:

You are then taken through a consent flow for that user:

Once the consent is granted the connection is tested by Microsoft 365 and the result shown:

The next few screens allow you to specify exactly how the ServiceNow data should be indexed. This process will be different depending on your data source, but for ServiceNow I can first pick the properties/fields to bring in and any additional filter I want to supply: 

The connector does a great job of specifying good defaults on your behalf. For example, in the "Filter data" section above you can see a default query string being used to filter out ServiceNow KB articles which aren't active or at a state of 'published' - this will be what you want in the vast majority of cases. 

The Graph connector framework has a nice preview capability to help you see the results ahead of full configuration. Hitting the 'Preview results' button gives me a few records from the external source:

Security trimming is the next section - depending on the connector used and how you configured authentication, you can specify that permissions in the underlying data source are respected so that the viewing user doesn't see anything different to within the source system:

I allow 'Everyone' to be used in my case, since I'm effectively using a form of app authentication (ServiceNow OAuth + ServiceNow named user) rather than delegated auth with the user's identity which would support this.

In the next step we map properties in the external source to properties in Microsoft 365 search - effectively the same as SharePoint managed properties for those familiar with those. This allows a common set of properties to be used across different content types within search - so that there's always a title, URL, last modified timestamp etc:

Related to this, you can set which properties are Queryable, Searchable, Refinable and Retrievable within Microsoft search. This allows you to control which you might want to use as refiners/filters in search and which can be queried on - for example, you might want to query on a person name and have it return ServiceNow articles where he/she is the author.

Again Microsoft's ServiceNow connector does a great job of providing defaults so you can probably accept them and continue:

The final config setting is how often the indexing process from Microsoft 365 to the source should run - with settings for an incremental and full crawl:

The config is now complete, so let's review settings before confirming:

It's now for Microsoft 365 to publish the connector and start indexing the external source

The result

Once the indexing has happened Microsoft Search is able to show results from the external data source, as shown earlier:

Courtesy of what is now known as 'Microsoft Search in Bing', another surfacing point that will be interesting to some is Bing itself - if it suits me, I can go direct to Bing in my browser and if the integration with my workplace is enabled I get the same results there too in the 'Work' tab:

By extension it should be possible to enable Windows search to cover this too - meaning you could hit the Windows key, type a search term and it search all configured sources including your external data source brought into Microsoft 365 through your Graph connector:


So that's the process and it's not too difficult - most of the effort is on the side of the external system since that's where you'll need to configure authentication. In the next post we'll look at:

  • Licensing
  • Controlling the appearance of search results

Next article: Microsoft 365 Graph Connectors - licensing, customising search results and more (available soon)

Thursday, 8 July 2021

Using RPA to control my ISP settings for kids gaming - Power Automate Desktop for web automation - part 2

In the last post I talked about how I automated my home wi-fi/ISP settings with Power Automate Desktop so that my kids can only use gaming websites like Roblox at permitted times. Since the scheduler in the portal doesn't support what I need, I had been manually visiting the site many times each week to block and unblock sites, which was a huge pain to both me and the kids. Enter Robotic Process Automation - RPA technologies are designed to "automate what cannot be automated", in other words applications without APIs, applications which run on the desktop and other legacy systems. By driving the keyboard and mouse directly, RPA opens the door to wider automation since it simply replicates how a human would interact with the application. Power Automate Desktop is now free with Windows 10 and Windows 11 - Microsoft's move to bring RPA technology to the masses and gain market share as RPA moves from exotic and niche to democratised and commonplace.

In this follow-up post I want to cover a few things:

  • Turning a Power Automate Desktop flow into a cloud flow which can run on a schedule
  • Sending a mobile notification once the flow has run
  • Licensing considerations for Power Automate Desktop
  • Authentication challenges and solutions - working around Captcha requests

In the last post I walked through how I got Power Automate Desktop to open a browser, navigate to my ISP's portal, find the page with the URL blocking, enter the two gaming URLs I want to block into a textbox and then click 'Apply' - with the final step being an announcement to Alexa. This now runs like clockwork every day once the kids have had their prescribed hour of fun. To give you a sense of what that looks like, the portal looks like this:

Where we got to in the last post is that my process was automated, but only in the sense that I have to click a button manually to trigger all the steps to execute. At this point I have a couple of "desktop flows" (one to block the URLs and one to unblock), and they show up in the relevant area in the Power Automate portal:

Of course, what I really want is for this to run in a 100% automated way and on a schedule - with no manual intervention involved. For that I need a cloud flow, so from our points above let's start there. 

Calling a Power Automate Desktop flow from a cloud flow for full automation

Once you have an RPA Flow it can be "wrapped" in a standard cloud flow - a cloud flow being the type of flow that you've been using all along if you've been working with Power Automate previously. Of course, a cloud flow can be triggered in a huge number of ways including when some data is changed in Microsoft 365, SQL or Dataverse or perhaps when something has happened in Salesforce, Workday or another cloud service, or even when an e-mail is received. In my case I just need a simple schedule. 

To get started I go into Flow and create a new scheduled cloud flow:

I then give my flow a name and define the schedule:

Once in the flow I can use the 'Desktop flows' connector to call the flow on my machine - this is the critical thing that links a cloud flow to a desktop flow:

This action allows me to select from a few Microsoft RPA options - note that all are premium actions (more on licensing later):

This action allows me to select from desktop flows I've already created - they are detected because of the connection previously established, which links my Microsoft 365 identity with my physical laptop:

Once I select one of my desktop flows, the action is intelligent enough to recognise any input parameters to that flow. In my case I parameterised the following to avoid them being hard-coded into my desktop flow:
  • RobloxUrl
  • ScratchUrl
  • AlexaAccessCode
The UI adapts to provide a way for me to enter these:

Take note of the "Run Mode" parameter above which is used to specify whether the desktop flow should run in attended or unattended mode - this is important for usage and licensing. We'll come to this later. 

Once things are saved, that's all I need to schedule and fully automate my desktop flow.

However, there's one more thing - the kids hear about the fact that gaming is either open or closed through the automated Alexa announcement I talked about last time. But I'd like to know that everything happened as expected too, so I add a Flow mobile notification:

...and specify the message:

Now wherever I am the notification comes through on my phone each time one of my flows run:

So now everything is fully automated and I have confidence that my automated process is behaving properly. 

Choosing between attended and unattended mode 

When we were creating a cloud flow to "wrap" our desktop flow earlier, we chose "Attended" for the run mode. When working with Power Automate Desktop and RPA in general, attended vs unattended mode is is an important decision to plan for - on the surface, unattended might seem sensible because you can't guarantee that a user will be at the machine when the flow runs, especially for a process that runs a few times through the day. However, an unattended flow can only run under the following circumstances:
  • All users are completely signed out
  • The screen is locked
  • The gateway connection between the cloud flow and the desktop flow has user sign-in information
In fact, a common use case for unattended flows is for a virtual machine to be used - thus giving you more control around guaranteeing these stipulations can be met. Unattended flows can also run concurrently on the same device if different user accounts are used, and this is useful with long-running processes or doing RPA at scale. 

Notably, licensing is significantly different for unattended vs. attended flows - so this is another reason why planning is required here. Let's turn to licensing now.

Licensing for Power Automate Desktop

I mentioned earlier that Power Automate Desktop is free with Windows 10/11, and that's true. What that gets you is the ability to click a button in Power Automate Desktop client on your machine and have your process run - the further automation that comes with a cloud flow requires pay-for Power Automate licensing however. As usual with Power Automate there are two ways of licensing this - per user and per flow. For per user licensing, you need a specific plan named the "Per-user plan with attended RPA", as shown below on the Microsoft pricing page:

Notice the additional "per bot" consideration if you want to run unattended (the second row). If you're doing per-user licensing you add the "unattended RPA add-on" to the per-user price:

So in summary, the options are:

Mode License approach Base cost Unattended add-on cost Total cost per month
Attended Per user $40 $15 per user per month (until 31 September 2021) - $15 (until 31 September 2021)
Attended Per flow $500 per month - $500
Unattended Per user $40 $15 per user per month (until 31 September 2021) $150 per month $165 (until 31 September 2021)
Unattended Per flow $500 per month $150 per month $650

As ever, you should refer to the official Power Automate licensing page for the current pricing and considerations between per user and per flow licensing.

From Microsoft's pricing you might observe:
  • There's a fairly significant premium for unattended processing. That's because you can automate at scale with this option with many processes running concurrently - if you're using RPA to process 10,000 invoices at month end, this incremental cost is likely to be justified of course.
  • The decision between per user and per flow licensing for Power Automate overall is relevant in this area too - most orgs decide this based on automation strategy and requirements they have in view  
  • Whilst there are some incentives at the time of writing (June/July 2021), it seems that the new pricing applied to Power Apps at this time doesn't carry over to Power Automate in case you're wondering
Overall, you can see this is designed for organizational use rather than anything personal or home-based. In that context of course, if a high value process is being automated the value of this to the organization could be many multiples of the cost - so comparing unattended RPA in Power Automate Desktop with non-enterprise solutions doesn't really make sense. And of course, my scenario of using this business technology to automate my wi-fi settings at home doesn't really make sense either - I created my solution on trial licensing, but if I needed to license this for the long-term the costs (even at $15 per month, which is all I'd need) would be a factor. None of this takes away from the power of the technology however - in a business context Power Automate Desktop is extremely powerful.

And when the trial licensing expires on my solution, I'll just click a single button each day and it will still be less work than going to my ISP portal and entering/removing URLs!

A final challenge for RPA - Captcha prompts

One final topic to address is how to use RPA to automate around visual Captcha challenges like these: 

Of course, the whole point of this kind of check is to ensure there's a human making the request rather than an automated bot - but this is to foil a malicious bot of some kind, rather than our wholesome, legitimate and approved automation. The image above shows the Captcha challenge presented when logging into my ISP's portal.

I suspect it may be possible to use a combination of DOM scraping and image recognition (e.g. Azure Cognitive Services Vision API) to get past something like this - however, reliability could be an issue since these images are small and frankly difficult enough for humans. So, my solution is far less exotic unfortunately:

  • Create a dedicated browser profile and pre-authenticate to the ISP site
  • Ensure the RPA process always uses this browser profile:
  • Enjoy the fact that persistent cookies last a long time with my ISP

So perhaps working around the Captcha problem more than solving it, but since I only need to take action every few weeks when the cookies expire that's good enough in this case. Clearly this could be a challenge for some automation scenarios which target public SaaS services in the enterprise, but wouldn't be common with other targets such as legacy non-cloud applications.


Over these posts we've seen how effective Power Automate Desktop can be in "automating what cannot be automated". We've discussed the basic "Power Automate Desktop only" approach vs. bringing a desktop flow into a cloud flows to facilitate scheduling or integration with other cloud processes - the latter method requires pay-for licensing in the form of a Power Automate per-user or per-flow license, and unattended processing comes with an extra cost but does enable automation at scale. 

As we noted that the technology is pitched at business automation rather than personal automation. The potential is clear, and it's great to see Microsoft have a strong easy-to-use offering in this space.

Wednesday, 9 June 2021

Using RPA to control my ISP settings for kids gaming - Power Automate Desktop for web automation - part 1

Like many families, we have annoying kids who would spend all their time gaming or on screens given half the chance. So, our tyrannical regime approach essentially grants them a certain amount of time per week, with controls put in place through our ISP which allows me to block or allow individual websites. Unfortunately with my ISP (Virgin Media here in the UK) there's no scheduling capability for this, only for parental controls overall - which means I have to manually go the portal and tap in the sinful URLs many times per week, and then unblock later on. A recurring conversation in our house goes something like this:  

  • Kid - "Dad, you know how you block our gaming websites when it's not gaming time so we can't sneak on?"
  • Me - "Yes?"
  • Kid - "Well, you're always on calls when it's 5pm on a gaming day and we're losing lots of time. It's so unfair! Can't you schedule it or something?"
  • Me - "Sorry son. Despite using a company who claim to be the UK's leading ISP and paying them through the nose each month, they chronically underinvest in their management portal and it was built by interns in the 1990s. There are no APIs either, so it's not possible unfortunately. Trust me, I'd like nothing more than to never visit that thing again or have notes shoved under the bedroom door when I'm on calls - but that's life son."
  • Kid - "Erm. Could we not just......turn the controls off?"   
  • Me - "Hahahahaha!"

After the 86th iteration of this conversation, I decided to spend a weekend looking at Power Automate Desktop for this since it does web automation. Microsoft announced in March 2021 that Power Automate Desktop is now free with Windows 10 as they expand the Power Platform into other forms of automation. In truth, the licensing means that if you want true unattended execution (rather than attended automation where you manually press a button and all the steps execute for you), Microsoft's RPA technology is more suited to workplace scenarios than home or personal automation. I'll talk more about licensing in the next article. Nevertheless, my ISP scenario was a good excuse to automate something else and try another scenario with Power Automate. 

What I'm automating

The basic process can be described as:

Once logged into the Virgin Media portal I can manage my "web safe" settings through a series of tabs. I'd need the automation to load the website (ensuring the session is authenticated with my credentials), and firstly navigate to the "Websites" tab shown below:

The websites tab provides an interface to specify the sites to block. I leave some sites permanently blocked, but what my automation needs to do is come in here and add or remove the "scheduled" websites. As the numbers in the image below depict, it's a 3-step process involving the URL being typed into the box, the "Add" button click and then settings applied with the "Apply" button once all changes are in:

The unblocking is effectively a reversal - this time the automation needs to find the right entries in the list and click the "Remove" link next to each. This involves a slightly different series of steps and there are a couple of ways in Power Automate Desktop to do this, but we'll get to that later. 

Getting started with Power Automate Desktop

Given that the tool is all about automation from the desktop (although cloud-integrated), we need to download some software. There are a number of ways you can do this, including from the "Install" menu within the Power Automate portal:

Once installed and signed-in, your machine effectively has a binding to a Power Platform environment:

Power Automate Desktop capabilities

The overall capability set is very powerful indeed, and of course the whole premise of desktop automation is that you can record (or manually create) steps which drive keyboard and mouse actions. You can open applications and browser windows, interact with their controls, and perform steps in systems that would be otherwise difficult to automate. Here are some of the things you can do:

Control elements of the desktop PC:      Work with files and folders:

Control applications through UI automation:     Control applications through web automation:

This distinction between "web automation" and "UI automation" is important - we'll come back to this later, but notice there are some similarities and some differences between the possible actions.

Overall, the toolbox covers many different areas of automation and there are almost infinite permutations of how these things can be combined:

Now let's look at the specific process to implement the automation I need.

Getting started with Power Automate Desktop

Like other desktop automation software, Power Automate Desktop brings a recorder to allow you to record the screen as a one-time operation to create your automated process. Whilst you *can* create your automation by directly dragging and dropping actions from the toolbox, in most cases you'll use the recorder somewhere in there. In fact, there are two:

  • A web recorder
  • A UI recorder
These are represented as two icons at the top of the designer:

As we're talking about this distinction, here's a tip:

Tip #1 - Web automation vs. UI automation
Since it's web automation I need to perform, using the web recorder is likely to give me the best results since it has a deeper understanding of browsers, web page structure and HTML input elements such as textboxes, radio buttons and dropdowns. The UI recorder is more suited to automation of desktop apps. This rule of thumb works in most cases, though I could imagine the occasional need to use UI automation in the browser (e.g. for a particularly complex web UI where pixel locations are more effective than navigating a DOM structure or dealing with content represented in images).

When using the web recorder you choose which browser to use - regardless, you'll always need the Power Automate Desktop browser extension installed and enabled (here are the links for Edge and Chrome). You select your browser of choice to get started:

Tip #2 - Chrome vs. Edge
At the moment, web automation with Chrome seems to be much more reliable than with Edge. The notorious "failed to get window" error seems much more common with Edge, and operations that fail in Edge tend to just work in Chrome. For now, I recommend switching to Chrome if you're trying with Edge but run into unexplained challenges.

As you record the steps you want to automate, they are captured by the Web Recorder:

In the end, my automated process looks for blocking the websites looks like this:

Tip #3 - use variables in your automated steps
Whilst the recorder will do a good job of detecting text you type into forms, replacing these with variables to more clearly separate out the values is a good idea. They become easier to replace and less buried in your script - just like in code.

In my case I have variables for the two websites I want to block and my Alexa access code:

The result

The video below shows the automated process in action - some observations:
  • It happens very fast :) 
  • It's hard to see what happens because some UI elements are off-screen or barely in the screen - however the web automation doesn't care about this 

Reversing the process - unblocking the sinful websites

As you might expect, I have a similar process to reverse the actions - in essence I have two distinct Flows (which can be scheduled independently):

In my case, unblocking is a little more challenging because I have to identify the correct sites in the list. This proved to be one of those web automation challenges where it takes more than the recorder to nail the steps. I found myself with two options:
  • Identify the right CSS selector to find the element with "", and navigate in the DOM to a sibling element which is the "Remove" hyperlink
  • Leverage the fact that the websites I'm removing will always be the last ones in the list. This was far simpler!
In the end I used Power Automate Desktop's ability to run some JavaScript on the page you're automating - very powerful! My actions looked like this:

The final step - announcing to Alexa

Since the whole problem scenario is the fact that I'm often on calls, I needed to signal to the kids that gaming was either available or unavailable. Alexa automation is a world I hadn't really dug into until now, but I found there are essentially two ways of getting what I wanted -
  • Alexa Notifications - this pings the Alexa devices, but only in a "you have a notification" way. To listen to the notification, someone has to say "Alexa, read me my messages", which is sub-optimal for what I wanted
  • Alexa Routines - this allows you to do any number of things, including make an announcement in the true sense with no "pull" of the message required
To integrate with Alexa Routines, I used the Virtual Buttons Alexa skill - this is a service which abstracts the triggering of Alex Routines and gives you a REST endpoint to call. If you want more than one virtual button, and I did because I wanted two different announcements, the service is chargeable but the cost is fairly negligible and it does seem to simplify things.

There's some setup work to do, but the steps are documented in an e-mail the service creators send. Part of the process involves defining the Routine in the Alexa app - in this case, to announce "Sorry kids, gaming is blocked" or similar:   

To integrate this into my automated process, I made use of the "Run PowerShell script" action in Power Automate Desktop and called the REST API through Invoke-RestMethod, passing the appropriate JSON in the body to call make the blocked/unblocked announcement as needed (via the Virtual Button ID):


Solution complete! I now have an automated process which opens a browser, drives the keyboard and mouse to make the changes to my ISP settings, and announce to Alexa when done - fantastic.

Except at this point, things aren't fully automated in an unattended sense - up to now, I have a button to click in Power Automate Desktop which will fire up the browser and execute the steps. I have to press the button manually though. The next step is to make this an automated process that runs from the cloud on a schedule. So next time, we'll talk about:

  • Turning this into a cloud Flow which runs on a schedule
  • Licensing considerations for Power Automate Desktop
  • Authentication challenges and solutions
For the last point, I'm talking about things like this - Captcha image verifications which are designed to foil malicious automation:

More on this next time!