Set up PAYG for Power Platform

The Power Platform now has the ability to be Pay As You Go (PAYG) for licensing. This is a great option to get access to many advanced capabilities on demand. When you configure this option the billing is done via Azure rather than Microsoft 365. This means, prior to setting up PAYG for the power Platform, you’ll need to have an Azure subscription in place. As I have highlighted before:

Deploy Office 365 and Azure together

Once you have the Azure subscription in place, inside the same tenant where you want to enable PAYG for the Power Platform, you’ll need to have or create an Azure Resource group that will be associated with the PAYG option. You need to create this ahead of time. The following will show you how to create one if you need to:

Manage Azure resource groups by using the Azure portal

image

You’ll then need to visit the Power Platform admin center which is at:

https://admin.powerplatform.microsoft.com/

then select Billing policies. From the top menu now select New billing policy as shown above.

image

Give the policy a name, the name needs to be at least 10 alphanumeric characters. Select Next to continue.

image

Now select the Azure subscription you want the billing ties to. Then select the Resource Group that you want to use, the must already be in your Azure subscription as aI noted earlier. Finally select the region and press Next to continue.

image

Now add any environment to the subscription and press Next to continue.

image

Here select Create Billing Policy.

image

The policy should now be created and displayed as shown above.

You create additional billing policies if you wish by simply repeating the above process. Doing so would allow you to tie that policy to a different Azure subscription and/or Resource Group for billing and management if needed.

For more details on the Power Platform PAYG option see:

Set up pay-as-you-go

CIAOPS Need to Know Microsoft 365 Webinar – March

laptop-eyes-technology-computer

Join me for the free monthly CIAOPS Need to Know webinar. Along with all the Microsoft Cloud news we’ll be taking a look at Power BI.

Shortly after registering you should receive an automated email from Microsoft Teams confirming your registration, including all the event details as well as a calendar invite.

You can register for the regular monthly webinar here:

March Webinar Registrations

(If you are having issues with the above link copy and paste – https://bit.ly/n2k2203 – into your browser)

The details are:

CIAOPS Need to Know Webinar – March 2022
Friday 25th of March 2022
11.00am – 12.00am Sydney Time

All sessions are recorded and posted to the CIAOPS Academy.

The CIAOPS Need to Know Webinars are free to attend but if you want to receive the recording of the session you need to sign up as a CIAOPS patron which you can do here:

http://www.ciaopspatron.com

or purchase them individually at:

http://www.ciaopsacademy.com/

Also feel free at any stage to email me directly via director@ciaops.com with your webinar topic suggestions.

I’d also appreciate you sharing information about this webinar with anyone you feel may benefit from the session and I look forward to seeing you there.

Automated user tenant access control

image

If you ever used an on premises Active Directory (AD) you may be aware of the setting, shown above, that allows you to set users login times. This was typically done to prevent users logging in after hours, say from 9pm to 6am.

Unfortunately, with Azure AD there is no direct equivalent setting but we can create something similar quickly and easily using Power Automate.

image

The first step in this process is to create a new Azure AD security group that will contain the users who will be prevented from accessing the tenant. You can also create this security group in the Microsoft 365 portal but it is better to do it in Azure as you’ll need to get the ObjectID for this group as highlighted above. There is also no need to actually put any users into this group as they’ll be added dynamically by Power Automate.

image

To prevent access to the tenant you’ll need to create a Conditional Access policy as shown above. This will require you to have a license for Azure AD Premium P1 or P2 for each user. Microsoft 365 Business Premium already includes Azure AD P1, so if that is already in the environment you need nothing additional.

Give this new Conditional Access policy a name and set it to include just the Azure AD security group created previously. It is also best practice to exclude at least one administration account to prevent you from being ‘locked out’ of your tenant. Ensure that All cloud apps is selected and that Block access is configured, as shown above. Finally, turn this Conditional Access policy On.

With no members in this Azure AD Security group, no one will be restricted from accessing the tenant.

image

Next, create a List in SharePoint that contains a list of users you want to be blocked. You can achieve this by adding the Person field to the list in question. This will basically allow you to enter users who are in your tenant by doing a lookup from Azure AD.

image

Create a new Flow and use the Recurrence action to trigger it as shown above. Select an appropriate time once a day when users will be prevented from accessing the tenant, say 9pm.

image

Add the Get items action as shown above next. Configure this action to retrieve from the list of users from the SharePoint list just created.

image

Then use the Apply to each action to loop through all the users returned by this Get items as shown above.

image

Inside the Apply to each action use the Get user profile (V2) action as shown, with the users email address, which is effectively their Azure AD identity.

image

Also inside the Apply to each action add the Add user to group action as shown above. Populate this action with the ObjectID of the Azure AD security group obtained at the start and the Id from the Get user profile (V2) action.

image

Now test the Flow manually to ensure it works correctly.

image

Once the Flow completes, the users in the list in SharePoint should now appear in the Azure AD security group as shown above.

image

Now when a user on that list attempts to login, because they are part of a security group that is part of a blocking Conditional access policy, they will no longer have access to the tenant.

image

To allow access again for users simply create another scheduled Flow executing at say 6am that uses the Get group members action and then a Remove Member from group action inside an Apply to each action as shown above. In essence, this removes all users from the Azure AD security group that is part of the blocking Conditional Access policy, resulting in those users no longer being blocked from accessing the tenant.

image

Run this Flow manually and ensure it completes,

image

and you should find that Azure AD security group to be empty, as shown above.

image

The users in the list who were previously blocked,  should now be able to access the tenant as normal. Left to its own devices, users in the SharePoint list will have their access blocked from 9pm to 6am each day now.

This automation is very quick and easy to set up. It can solve the challenge of ‘forcing’ users to take a break from work after hours, rather than being ‘on’, aiding their mental health and making them more productive when they do work. It could be used to improve security but allowing account to only operate during ‘business hours’ and limiting attacks after hours, which is when many attacks happen.

This process could be extended and enhanced to provide more granular options to suit any need as well as alerting. However, hopefully, it demonstrates how easy it is to solve business challenges thanks to the Power Platform and the integration of Microsoft 365. Remember, the only extra you need is Azure AD Premium P1 to enforce Conditional Access, something already part of Microsoft 365 Business Premium.


Get a list of devices from Defender for Business into a SharePoint list

image

One of great things about an API is that it can be used in many places. I showed how to:

Offboard devices from Microsoft Defender for Business using an API with PowerShell

and I can do something similar with the Power Platform.

First step in that process is to get a list of Microsoft Defender for Endpoint devices and put them into a pre-existing list in SharePoint. For that I use the above Flow.

image

Once the Flow has been triggered I grab the Azure AD application credentials from the Azure Key Vault. I’ve covered off how to create an Azure AD application here:

https://blog.ciaops.com/2019/04/17/using-interactive-powershell-to-access-the-microsoft-graph/

and using a PowerShell script I wrote here:

https://blog.ciaops.com/2020/04/18/using-the-microsoft-graph-with-multiple-tenants/

Getting the Azure AD application credentials into an Azure Key Vault can be done manually or by using this scripted process I’ve covered previously:

Uploading Graph credentials to Azure Key Vault

Once they are in the Azure Key Vault they are easy to access securely using the Flow action Get secret as shown above.

image

The next step is to delete devices I already have in the list in SharePoint because I want only current devices to be brought in. To achieve this, I get all the items from my destination SharePoint list using the Get items action. Then, using the Apply to each action and the Delete item action inside that loop, existing entries will be removed so I have a clean list.

image

I’ll now use the HTTP action to execute an API call to the Defender environment as shown above. The API endpoint URI to get a list of devices in Defender for Endpoint is:

https://api.securitycenter.microsoft.com/api/machines

Access is granted via Active Directory Auth and the Authority is https://login.microsoftonline.com. You also need to use the credentials of the Azure AD application obtained previously from the Azure Key Vault, as shown above. Ensure that the Audience is https://api.securitycenter.microsoft.com/.

image

The output of this API request will be a JSON file so we now use the Parse JSON action to obtain the fields needed. To understand what the JSON looks like and insert a copy into this action look at the Microsoft documentation here:

List machines API

which provides a response sample that you can use.

image

The last action in the Flow is to take the parsed JSON output and enter those details into the pre-existing SharePoint list that you need to create to house this information.

image

I’ve kept the destination list simple, as you can see above. Basically, the final Apply to each action places each device and its information as a row into the destination SharePoint list.

image

If I now run this Flow, I see it runs successfully.

image

Looking at my SharePoint list I see I have a new list of items as expected.

image

If you weren’t aware, the ‘eyelashes’ on an entry in SharePoint indicate it is new.

Now I have copy of all the machines in my Defender for Endpoint in a SharePoint list. You will also see that my SharePoint device list contains an additional ‘Offboard” column that I am going to use when I implement another Flow to offboard devices from Defender for Endpoint, much like I did with PowerShell previously.

You can also easily extend the operation across multiple tenants if I want using Azure AD applications in each.

The great thing about using the Power Platform and APIs is that for many, it is much easier to get the result they want rather than having to write code like PowerShell. Also, the Power Platform environment has many capabilities, such as sending emails, adding extra metadata, etc. that are much easier to do than using PowerShell. Once the Defender for Endpoint device list is in SharePoint there is really no end to what could be done.

With that in mind, stay tuned for an upcoming post on how to use what’s been done here and another Flow to actually offboard devices from Defender for Endpoint.

Celebrating anniversaries with Power Automate

A very common requirement is to remind people about anniversaries. In a business this could take the form of birthdays or commencement dates. It could, however, just as easily be any sort of event that happens on a certain date.

Previously, I’ve shown how to:

Send recurring tweets using Microsoft Flow

However, in this case, instead of simply rotating through a list of posts we want to match today’s date to a date on a list and then broadcast the message that corresponds to that date entry.

image

The starting point for this process is to create a reference list containing the dates and details you wish to share. I recommend that easiest place to do this is in a SharePoint list, as shown above. Of course, this list can contain as much detail and additional columns as you wish, but for this, I’ll keep it simple and just have two fields. It is important that you have at least one column (here Dateoption) that refers to the current year in which that item will be displayed.

image

For simplicity, I have also configured my date column in the SharePoint list to exclude time and display in standard format as shown above. There is nothing stopping you using Date & Time if you wish, it just makes the filtering a little more complex later in this process.

You’ll then want to create a Power Automate Flow that looks like this:

image

It all starts with a Recurrence action that will trigger this process once a day like so:

image

If you select the Show advanced options in this action like so:

image

You can set an exact time when this Recurrence action will be triggered (say 10am). However, since this example is a daily anniversary, we only need to trigger it at any time during the day.

image

We now need our process to determine what the current date is and we can do this using the Current time action as shown above.

image

Next, add the Convert time zone action. There are two reasons for adding this action. Firstly, the Current time action returns today’s date in UTC which may cause issues if you are not in that time zone like me. Thus, I want the current time BUT I want it as a local value (i.e. to reflect the actual time in Sydney, Australia), thus the Source and Destination time zone field settings.

The second reason for the Convert time zone action is so the time value is in the right format for a comparison test later on in the process. Thus, the Format string field should be set to yyyy-MM-dd as shown.

Now, I need to add the Get items action to actually go and look at what is in my SharePoint list.

image

In this action I enter the Site Address and List name, however I also expand the advanced options to reveal the Filter Query field as shown.

image

The Filter Query field will limit the items returned by this action to only those that match the filter. Thus, I want the returned items to only be those that match today’s date, which I have correctly formatted and stored in the Converted time action result. Thus, I want to compare the date field from my SharePoint list (here Dateoption) to the Converted time result. It is important to note that I have enclosed Converted time result in single quotes (‘) to convert the value to a string for comparison. It is very important that you do that, otherwise you’ll get errors when your Flow runs.

image

With the values that the Get Items action returns you’ll need to perform a number of steps. For this you use the Apply to each action as shown above.

image

In the case of this example, I’m simply going to post the text from the Title field in the SharePoint list that matches today’s date into a chat message as shown above. Again, this action could be anything you want, in fact, I’ll talk about how I use this with Twitter later on. For now the expectation is that if there is a match in the SharePoint list for today’s date, then the text for that entry will appear in a Teams chat message.

image

We need to do one more thing before we are finished here. As this is an anniversary calendar, we want to increment the current item for today and have it reoccur on the same date next year. To do that we use the Add to time action as shown by adding 12 months to the result date we have determined as shown above.

image

Before the new date can be added back to the SharePoint list it needs to be formatted correctly. This is achieved using the Compose action as shown using the following expression:

formatDateTime(body(‘Add_to_Time’),’yyyy-MM-dd’)

You’ll notice that that date format yyyy-MM-dd is the same as the one we set in Convert time zone action earlier.

image

All that remains is to use the Update item action to update the item in the SharePoint list with the new date entry just composed. As shown above, the same SharePoint site and list is selected, along with the item ID and Title but the Dateoption field is set to contain our new formatted date output from the previous action.

You can now save your Flow and run a manual test.

image

If I look at the chat in my Team I see the expected message that matches the item Title field in the original SharePoint list.

image

Also looking at my original SharePoint list I see that the date of today’s item has been incremented twelve months as shown.

image

One of the ways that I use this process with Twitter is to regularly post anniversary dates around ANZAC participation from World War One, which are taken from my site ANZACS in France.

The idea is that that the Flow checks this list of dates and then tweets out the text in the Title field if there is a match. Then it increments the PostDate field twelve months ready for next year. You’ll also see that I have added another custom column that records the original date of action just so I can filter and sort easily. Feel free to follow @ANZACSIF to be reminded of these dates.

As I initially mentioned, I believe there are plenty of applications for this type of process in a business. The most common ones I would suggest are for staff birthday and anniversary reminders. The great thing is that with Power Automate it is easy to modify this process to suit whatever need to have. It also makes it easy to edit the events and more if you need to because all you need to do is modify the SharePoint list that this process uses.

The possibilities are endless thanks to the Power Platform.

Power Automate ODATA filter failure when field named ‘Date’

image

So, I was doing some testing with a new Flow in Power Automate. What I wanted it to do was, at a recurring time each day, look for today’s date in a list of SharePoint items and then display other values from any matching record in a Team’s chat. To prototype this out I created a very simple list with two columns, as shown above, Title and Date. Remember, the Title field is generally created for you by default when you create a basic SharePoint List.

image

In Power Automate I used a SharePoint Get Items action as shown above to get the information I wanted. To filter down to the data I used on ODATA query like:

Date eq ‘2021-12-31’

to test. Problem was, as shown above, I was getting no results that were feeding through to the next Apply to each action that followed directly after.

There were no errors indicated in my Flow. I tried a number of different format options and so on, trying to work out what the issue was.

image

The issue turned out to be the name of the field – Date – I had created in my SharePoint List! Once I created a new column called Dateoption with the same format, and entered the same data into it and removed the offending Date column, it successfully filtered data as expected and passed the result to the following Apply to each action as you can see above.

The moral of the story is that you should probably avoid naming your fields with any ‘reserved’ programming commands like ‘Date’ as I did. Make it something unique like ‘Datefield’ or whatever. Just don’t use a common term like ‘Date’ as I did or you might struggle to troubleshoot as I did here.

Hopefully, this will save you wasting the amount of time I did to solve this that you can better spend on creating your Flow!

Adoption with fun

The majority of IT products and services are not actually used by IT people (amazing eh?). They are in fact, used by ordinary people (aka Muggels) in businesses, trying to do their job. For these people, changing the way that they work is frustrating because they need to adopt new approaches and tools. Helping with this adoption is a key to the success of modern approaches to IT I believe.

A handy technique that I have found to work well is make using new systems fun. In the distant past, when I was implementing SharePoint on premises, I used to implement the Daily Dilbert web part to post a Dilbert cartoon onto the front page of the SharePoint Intranet each day. The idea was to help drive adoption by getting people to visit the company Intranet to read the Dilbert comic and then, hopefully, dive into the other content that was there.

Today, the technology has changed but the adoption challenge hasn’t. I thought that I’d therefore share with you a way to get a Dilbert comic into your Teams channel daily using Power Automate.

This is all made possible via APIs and a suitable one I found is:

https://dilbert-api.glitch.me/json

which will produce an output that looks like this:

{"title":"Simulation TestingElbonia University Partial Win","image":"https://assets.amuniversal.com/4f2025a02e0d013a8769005056a9545d.png"}

In here you’ll see an image link to the Dilbert Cartoon.

Step one is to create a new Flow that is triggered at a recurring time.

image

Next, you want to add the HTTP action. In here, use the GET method and the URI set to the above API link as shown above.

The HTTP action is actually a ‘premium’ connector and may not be available to you by default. Thus, you may need an upgraded Power Platform license to have this available. Remember however, you’ll only need that license for the user creating and running that Flow.

image

You’ll then need to the Parse JSON action as shown above. The content here will be the Body from the HTTP action above and simply copy and paste the output of the API above into the option Generate from sample.

image

Now add Post message in a chat or channel action.

image

Enter option to post into the Team and Channel of your choice as shown above.

image

For the Message field select the </> option from the menu bar across the top, as shown. This will allow you to use raw HTML code here.

Type the following:

<img src = ”

then select the option to insert dynamic content like so:

image

(the lightning bolt icon)

image

In this list that appears you should be able to select image as shown above.

image

add the following text after the dynamic field

” width=”738″ height=”229″>

so the completed Message field looks like:

image

It is important that the HTML formatting is correct, otherwise the image will not display.

image

If you now test your Flow you should see the cartoon appear in your Teams channel as shown above. If you have scheduled your Flow daily then you should see a new comic every day. Remember, there is only one cartoon every 24 hours! Rerunning the Flow before then will simply display the same strip.

When the daily comic is more than three frames then it is cut off by default like so:

image

However, clicking on the comic will enlarge it for full viewing. This limitation is due to the height and wide parameters the HTML code used inside the Flow. Most strips are only three frames, that is why I used those height and width defaults for most readability most of the time, but you can vary those parameter if you wish.

So, the idea is to make visiting a Team a more fun place to visit regularly, hopefully with people engaging about the content to help drive adoption.

This Flow/API method can be utilised with just about anything that supports an API. Another I have found (although somewhat more risqué) is a Chuck Norris API here:

https://api.chucknorris.io/

which can be moulded to give a similar result (be it text only).

The only limitation of all of this is the need for the premium Flow HTTP action, but as I said, it is well worth the investment and is only really necessary for the user creating the Flow. Having a premium license for Flow opens up so many more capabilities, so it is highly recommended if you want to get serious about automation inside your environment.

Happily, Daily Dilbert is back baby! And now in Microsoft Teams.