Power Platform product release

pexels-clem-onojeghuo-175711 (1)

I am please to announce the inaugural product release from the CIAOPS Patron Power Platform Community:

Power Automate Drink Ordering System

This is a PDF document that takes your step by step through creating an automated process for bulk ordering of drinks. This solution was developed to solve the challenge or ordering many drinks at events, however it could be used for much more mundane things such as preparing complimentary drinks when guests attend a business.

This project can also be seen as a great way to start learning the Microsoft Power Platform by creating a real world process using Power Automate (Microsoft Flow). The information provided run to over 130 pages and includes screen shots and easy to follow instructions.

To celebrate the release of this inaugural product, it is being offered at a special 75% discount which you can take advantage of here:

https://directorcia.gumroad.com/l/lDekX/foundation

This special offer is available for the first 25 purchases! So if you are interested in getting your hands dirty with the Microsoft Power Platform, here’s a great opportunity to get started.

Look out for more projects coming soon from the CIAOPS Patron Power Platform Community.

Getting Message Center information into Teams

Recently, I wrote the following article:

Syncing M365 Message Center to Microsoft Planner

which took you through the process of getting Message Center information into Microsoft Planner. as good as that it is, the best place for that information should really be in Teams. The reason? With Teams people can ‘chat’ about the topics which adds far more value for an organisation in my opinion.

The good news is that it is very easy to not only sync messages with Microsoft Planner but also have them displayed in Microsoft Teams. It is all accomplished using Power Automate.

image

Create a new Flow and use the When a new task is created trigger as shown above. You’ll then need to configure this trigger action to point to the same Microsoft Plan into which you have already set up to sync with the Microsoft Message Center.

image

The next action should Get task details as shown above. You’ll need this to actually read the notes from each task, which contains the details of each item from the Message Center.

image

In my case, I save the Description field from the task into a string variable using the Initialize variable action as shown. I then use a number of separate Compose actions to search and replace text inside that variable to tidy up and format the Description field for posting into a Teams chat.

For example I remove the /r/n characters and replace them with the HTML line feed tag </br> using the following expression:

replace(variables(‘description’),decodeUriComponent(‘%0D%0A’),'</br>’)

image

Once I have the Description field formatted the way I want it then I use the Post a message (V3) action as seen above. The Title of the new task from Planner is the subject of the thread and the body is my now nicely formatted Description field, which is the data from the Message Center item.

SNAGHTML210e1093

You can see the result in a channel in Microsoft Teams above. Now others can easily add their reactions, comments and generally collaborate far easier than within Microsoft Planner.

I think having the Message center information delivered to Microsoft Teams make a lot of sense since it is a place more people will be spending more of their time generally. However, getting the Message Center information into Microsoft Teams still requires the sync configuration to a Plan first. However, once that is done, Power Automate allows you to achieve just about anything!

Using Defender for Endpoint API and Power Automate

I recently detailed:

Using Defender for Endpoint API and PowerShell

to produce this type of output

image

which is all well and good but does lack some flexibility when it comes to output as well as being something you need to manually initiate. There is way to deliver more using Power Automate.

To do this you’ll still need to complete the initial steps from the previous article and create an Azure AD app in the destination tenant and save the access information. This basically allows access to the destination tenant to extract data. However, now, rather than embedding that sensitive information inside a public script and having the credentials ‘in the open’, they can be securely stored in Azure Key Vault. This will provide a secure repository for the Azure AD app credentials while still allowing them to be readily accessible by service like Power Automate. To use Azure Key Vault you will need a paid Azure subscription.

image

In a nutshell, we want to create a basic Flow in Power Automate like that shown above. In this case it is initiated manually but it could just as easily be triggered on a schedule using the Recurrence action in Flow. Next, the required parameters are grabbed from the Azure Key Vault.

image

When you are building this Flow, if you see a dialog like shown above, it means you don’t have a Power Automate license that includes the ability to use Premium connectors like Azure Key Vault and HTTP. Licensing the Power Platform is beyond the scope of this article but, if you see that dialog you’ll probably need to purchase a stand alone license of Power Automate to gain access to the required premium connectors.

image

You construct the HTTP action as shown above, using the parameters from the Azure Key Vault to access the Azure AD app via the API URL:

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

that will return a list of vulnerabilities exactly like the PowerShell script did in JSON format.

image

After parsing the JSON output from the HTTP action that executes the API request, the results are mapped to a simple SharePoint list as shown.

image

Thanks to the magic of SharePoint, you get results that look like the above, which is vulnerabilities by machine, or

image

vulnerabilities by severity above, thanks to the ability to easily sort lists in SharePoint.

You’ll also notice that conditional column formatting has been applied to to highlight the severity. Yet another benefit SharePoint lists provide.

So the basis of all of this is an Azure AD app with the appropriate permissions inside a tenant that you wish to obtain information from. From there you can use an API request using PowerShell or Power Automate or whatever, to pull the desired information. The easily way to format that information is to send the results to SharePoint, as done here, to slice and dice as well as display the information any way you want.

This output could as easily have been sent to Power BI, Power Apps, an email, or any other service in Microsoft 365. That’s the benefit of using the Power Platform and things like Flow to get the information. Now the possibilities are endless.

A few important point to note about this:

1. You are in control of the permissions and credentials for obtaining the information using the API. You are not surrendering or trusting these to a third party to access the source data.

2. Credentials are save in Azure Key vault which ensure they are secure and access is controlled by you.

3. You can use this technique with just about any API to import information. All you need is the API URL and the appropriate permissions inside the Azure AD app.

4. You can extract information from multiple tenants into a single source tenant if you wish, you are not limited to just pulling information from the tenant where the Flow was created.

5. The extracted data can be mapped to any Microsoft 365 service. Here it was to SharePoint as that is the easiest, but it could just as well be sent to any Microsoft 365 service. This provides a huge amount of flexibility.

6. You can modify, enhance, extend, etc the Flow at any stage to suit any changing needs.

7. The Flow and the process it executes lives inside you Microsoft 365 tenant and is subject to all the compliance and security options that Microsoft provides here.

8. You can trigger the data extraction to happen on a scheduled basis of your choice with Flow easily.

I see lots and lots of benefits of using this process to regularly pull information from any tenant on just about anything and report it in what ever way you wish. It puts you in control of the whole process, and most importantly, the security of executing this, which in a world moving to zero trust, is a huge benefit.

Hopefully, this will inside you to start playing around with the possibilities when it comes to API and Power Automate.

Need to Know podcast–Episode 253

FAQ podcasts are shorter and more focused on a particular topic. In this episode I speak about some automation options that are available in the Microsoft Cloud.

This episode was recorded using Microsoft Teams and produced with Camtasia 2020

Take a listen and let us know what you think – feedback@needtoknow.cloud

You can listen directly to this episode at:

https://ciaops.podbean.com/e/episode-253-automation-optiona/

Subscribe via iTunes at:

https://itunes.apple.com/au/podcast/ciaops-need-to-know-podcasts/id406891445?mt=2

The podcast is also available on Stitcher at:

http://www.stitcher.com/podcast/ciaops/need-to-know-podcast?refid=stpr

Don’t forget to give the show a rating as well as send us any feedback or suggestions you may have for the show.

Resources

FAQ 16

CIAOPS Patron Community

@directorcia

Incrementing a SharePoint List Column using Power Automate and Flow

A common task that I have found need for in SharePoint is the ability to increment an identifier field for an item in a SharePoint list. One use case that I have is the ability to send regular posts to a Team channel but continue to loop through all the items in the list no matter how many there is. In short, I want to take a list of items from SharePoint and post the contents one item per day to a Teams channel, forever in an automated fashion

I want to take this opportunity to thank Andrew from Andrew Gallagher Computing for assisting me working this out. Here is the result:

image

You can achieve this outcome using Power Automate in Microsoft 365, which we have done as shown in the above summary screen shot. Now let me break down all the components.

image

The first place to start is to create a list in SharePoint that you will use with the appropriate columns. In this case I have 3 columns:

TitleId = a column to keep track of each entry. This will contain an integer that gets incremented.

Title = the subject line of the post in the Microsoft Teams channel

Description = the body of the post in the Microsoft Teams channel

image

The first step in Power Automate is to create a recurrence trigger, which is pretty easy. I want this Power Automate to run once a day at a set time.

image

Next, I initialise 3 variables:

MaxTitleId = will hold the current maximum value found in the TitleId column in the list

MinTitleId = will hold the current minimum value found in the TitleId column in the list

NewTitleId = this value will be MaxTitleId + 1 and will replace the existing TitleId value in the list items when that item is used.

An interesting point here that we found was that the field time for the variables has to be Float even though the actual numbers in the SharePoint list field are integers.

image

Next I need to get items from the SharePoint list I have created, from the SharePoint site location I desire. You will notice here that I also want to display the Advanced fields so I can add the following fields:

Order by = TitleId desc

Top count = 1

In effect, I am ordering the list by the TitleID column in descending order, which places the row with the highest value in the TitleId column at the top of the list. Then I simply take the 1 item from the list in that order, i.e. the top row. In essence, I am finding the item in the list with the highest  TitleId number in the list.

image

I then take that List row and set the variable MaxTitleId to be the value in the TitleId field for that item in the SharePoint list I just captured. That means that I am finding the highest value in the TitleId column for the list and saving that highest value into the variable MaxTitleId for later use.

image

next, I perform the operation of MaxTitleId + 1 using the expression:

add(variables(‘MaxTitleId’),1)

and store it into the variable NewTitleId. This will be the new value of the field TitleId in the list item after I have used that item. In effect, it will move the item that I use to end of my list.

image

I now again, get the same list, but this time sort by the field TitleId in ascending order using the command:

TitleId asc

After doing this, I again only take the top row of the list. In essence, I am now finding the row in the list where the field TitleId is the lowest. This will be the item that I want to post out to my Teams channel.

image

Before I do that, I save this minimum TitleId value to the variable MinTitleId as shown above.

image

I now post the information in the row item with the lowest value of TitleId to my preferred Microsoft Teams channel as shown above.

image

and above, is what it appears like in my Teams channel.

image

Finally, I update the item that I just used to post information to my Teams channel and replace the existing Titleid with the one in the variable NewTitleId. In effect, I am now making this used item in the list with the lowest TitleId field, the one with the highest value + 1 of TitleId in the list. This means that this item will fall to the bottom of the list and won’t be used again until down the track it becomes the item with the lowest TitleId field once again. That won’t happen until all other items in the list have been published and completed a similar process.

So there you have it. Yes, technically we don’t use the variable MinTitleId but we have included here to help anyone who may be looking for that using Power Automate. I speak more about the business reason for creating this type of automation in an upcoming article, but for now, consider it as a way to continually broadcast common messaging to people using Microsoft Teams.

My software and services 2020

startup-photos

Here’s last year’s post for comparison:

My software and services – 2019

All my PC’s are running the latest version of Windows 10 (1909) without any issues and none during the upgrade process either. I do have Windows 10 and Office Insider builds happening on an original Surface PC as a testbed. All Windows 10 Pro machines are directly joined to Azure AD and managed via Intune. All machines run no third party AV as Windows Defender is a far better option in my experience. Thanks to Microsoft E5 on my production tenant, I am also using Microsoft Defender ATP at the back end for monitoring and investigation of endpoint threats.

The WD Sentinel DX4000 runs Windows Storage Server 2008 and replacement has been delayed due to the “pending” arrival of the NBN which hopefully will provide better bandwidth. In the mean time I have established a site to site VPN to Azure and have begun moving data into Azure storage. In the end this device will merely function as a backup device but for the time being I need to wait for better bandwidth. Hopefully this year I’m being told.

My two main tenants are an Office 365 E5 demo and Microsoft 365 production environments. The Windows 10 Pro machines are Azure AD joined to the Microsoft 365 production domain. The production Microsoft 365 tenant has Microsoft 365 Business for all users except myself. I have a Microsoft 365 E5 license on which I have configured all the services including integrated PSTN calling via Switch Connect.

I use most major browsers:

– Edge – mainly for logging into my production tenant

– Edge Insider – will soon become my major production browser and is used for production and business websites, like reading Microsoft docs.

– Chrome – I am minimising/eliminating my use of this on existing machines and not installing on any new machines. I want to move away from Chrome totally as soon as possible and get it off all my machines.

Brave – I have become increasingly concerned about the surreptitious tracking that many sites perform, especially when it comes to social media sites. I therefore now do all my ‘random browsing’, searching and viewing of social media sites. I became aware of the extent of tracking when I was adjusting the security settings in Edge Insider and found the following:

image

Made me realise that I probably need to take this ‘do not track’ stuff more seriously!

– Firefox – I occasionally use this for testing or isolation but less so now thanks to profiles in Edge Insider.

I have now cranked Edge Insider up to the maximum security level but wanted to isolate the most likely tracking culprits into another browser that was security focused. After some evaluation, I have chosen Brave to be this browser. This is now where I do all the stuff that is more likely to be tracked and now hopefully blocked or at least minimised. I have also set this browser up to use Duck Duck Go as the default search engine, otherwise I use Bing for my production browsers.

Services like SharePoint Online and OneDrive I use regularly both in the demo and production tenant. I have the OneDrive sync client installed, running and connected to various locations on my production and demo tenants. I can now sync across all my different tenants as well as my consumer OneDrive storage. We have come a long way with the sync client!

I used to have  Microsoft Teams which is now my main messaging application. All the CIAOPS Patron resources like the intranet, team, etc all reside in the Office 365 E5 demo tenant but I connect to it on my desktop normally via an Azure B2B guest account from my production tenant. Thus, I can admin the Patron resources in a browser if need be but I get the same experience on my desktop as any Patron would. Handy to know what works and doesn’t work with Microsoft Teams guest access. Thanks to Microsoft E5 and Switchconnect, I also have Teams connected as a phone.

I use Lastpass to keep my passwords and private information secure. It allows me to do things like generate and store unique passwords for each website that I sign up for. It is also available across all browsers on my machine (including Microsoft Edge). I also now also use Lastpass to store secure notes.

The extensions I run in all my browsers are:

LastPass

GetPocket

I use the automation sites If This Then That and Zapier to automate many different tasks. A good example of one of these is automatically publishing to various social media sites. I am now using Microsoft Power Automate more and more for automation and I am still looking to dive deeper using things like Azure Functions in 2020. I have now replaced Socialoomph to post precisely scheduled social media posts with my own solution in  Power Automate.

For my Office 365 and Azure email newsletters I use Mailchimp.

My preferred public social networks for business, in order are:

1. Twitter

2. Linkedin

3. Facebook

The Apowersoft software allows me to display both iOS and Android devices on my Windows desktop which is really handy for demonstrations and presentations.

I also use Yammer extensively but for more specialised roles and thus don’t consider it really a ‘public’ social network, more a private one.

I consume a lot of content from YouTube both for business and personal interest. I also also use YouTube extensively for my publicly available training video training.

Microsoft Office desktop software is still part of my everyday workday via applications such as Outlook, Word, Excel, PowerPoint, etc. I use the desktop version of Outlook on my Surface Pro 6 which lives on my desk but I only use Outlook Web App on my travelling Surface Pro 4 device. I could happily not use Outlook on the desktop any more I believe but I still use so I understand the experience for most users. However, I do see the day when Outlook on the desktop begins to lose its appeal.

One of the things I have added to my desktop version of Outlook is a digital certificate that signs every email that I now send. This helps the receiver confirm that the message they have received is in fact from me and that it hasn’t been altered in any way. There are some issues when people attempt to reply to these emails from a mobile device but I believe a fix from Microsoft is not far away.

The key application from the suite for me is OneNote. OneNote is my go to Swiss Army knife for just about everything digital. I use it to capture all sort of data. I even use it as a diary as I have detailed previous here:

One of the ways I use OneNote

The reason OneNote is key is because:

1. Just about everything I put in there us searchable

2. It is freely available across all platforms.

3. All my information is synced and accessible on all devices.

4. It is available on the web or offline if needed.

There are now two version of OneNote, the Windows store OneNote and OneNote 2016. Microsoft have changed their stance on future upgrades to OneNote 2016 desktop which is great to hear and kudos to Microsoft for taking feedback on that score. I am a big user of OneNote on my iPad with the Apple pencil. This combination has allowed me to totally eliminate my paper notebooks for things such as journaling.

I use Pure Text to easily paste information, especially to and from OneNote as only text.

I am now a big Microsoft To-Do user. I use it to keep many tasks and items that I need to follow up. I love how it is available on all my devices and syncs across them all as well. I was becoming a bit worried when it had sat there with no updates for a long while, but that has changed now with heaps of updates being released. I’m keen to see where To-Do goes in 2020.

I use Windows terminal now for things like PowerShell execution and Microsoft Whiteboard for demonstrations and training.

Another key service I use everyday along with Office 365 and OneNote is Azure. Typically, I use it for running up virtual machines that I test various things with but I also use it to backup my local data as well as that of other members of my family using Azure Backup.

Azure desktop backup

I have also now implemented an Azure site to site VPN as well as Azure SMB File storage to start moving my data into. I use Azure Sentinel to monitor all my services and machines in one single console and tell me about any incidents. My plans for 2020 is to keep building out my Azure infrastructure to include Azure AD Domain Services, Windows Virtual Desktop and more. Stay tuned for updates on this throughout 2020.

There is just so much that can be done with Azure and I pretty much use it everyday.

For a subset of my local data that I wish to remain secure I use Truecrypt to create encrypted volumes. All my Windows 10 machines run with full disk encryption thanks to Bitlocker, but stuff like financial and customer data I keep inside Truecrypt volumes for that extra layer of security. I understand that Truecrypt is no longer maintained and may have some very minor security flaws, but for how and why I use it, it is more than adequate.

Production data is also protected using Windows Information Protection which provides yet a further level of protection and extends that to all devices including mobile devices like phones and tablets,

To capture my desktop for my online training academy or my YouTube channel I use Camtasia. I use SnagIt to capture screen shots and add highlights and emphasis to these. Snagit allows me to capture complete screens or specific areas quickly and easily.

I use Microsoft Teams to record my podcasts, which I then produce with Camtasia. These are uploaded to Podbean where they syndicated across various network.

To compose and publish blog articles I use Open Live Writer.

The majority of images I get, like the one at the top of this article, I get from Pexels. Pickit is also another great option.

For improved meeting management productivity I use Microsoft FindTime.

I use Visual Studio Code in which I do most of my PowerShell editing and publishing. The end result typically is my GitHub repository where you will find a range of scripts and other resources that I maintain regular. With Visual Studio Code I can edit publish and sync all my machines and my GitHub repository no matter where I am. Very handy.

Here are also a few of the other items I use regularly that are not for business:

Amazon Prime Video – only place to the latest The Grand Tour action. I also liked the Jack Ryan series and well as the Gymkana Files.

NetFlix – Just added this recently and have found many great documentaries.

XBox Live Gold – access to all the online Xbox goodness.

Duolingo – language learning, Japanese and Italian at the moment

Tinycards – language and facts learning via flashcards. Also handy for certification exams.

So there you have it, the major software and services that I use regularly. I continue to search out additional software that will improve my productivity. If you use something that you’ve found really handy, please let me know and I always keen to explore what works for others.

Microsoft 365 Automation presentation

These are the slides from my recent presentation on the automation options available in Microsoft 365.

The most important take away I believe is that we live in a world dominated by software. This fact is highlighted that:

Software is eating the world

There are plenty of reasons not to focus on software as a success path but that major reason to is simply the opportunity it provides, especially if most others believe it is all too hard.

It is important remember that software is a skill not a talent. This means it is something that can learned and improved continually over time. There is no such thing as a born developer. Some may have a higher aptitude to software development than others but that doesn’t means it isn’t something you can develop and learn.

As you ponder the worth of automation, have a look at all the simple processes you repeat continually throughout your day. Why is that? Why are these not automated? We live in a world of abundant technology. Most people carry a computer with them that is more powerful that the one that landed on the moon, yet it seems we all have less time to do the things we really enjoy. Why is that? We have allowed technology to master us, rather than using software to make it do our bidding.

The place to start with Microsoft 365 automation is on the desktop. Applications like Word, Excel, and so on contain the ability to record processes via macros and replay these quickly and easily. In fact it will actually convert these actions into code that can be further modified. Every Office application has a huge set of tools to assist with automating processes.

Although tools like SharePoint Designer have now been depreciated they are still available to use. If you are doing work with SharePoint, especially migration, it is important that you have some idea about the workflows SharePoint Designer creates and how they can be maintained.

Third party services like IFTTT and Zapier provide the ability to connect to Microsoft 365 services. One place that I use IFTTT is to save a backup of each of my blog articles directly to a OneNote file I have saved in OneDrive. I use Zapier to automate my free SharePoint email course offering.

The important consideration here is that the automation does not have to be purely focused on a technical outcome. It can be used in many places inside a business, including marketing.

The Microsoft equivalent of tools like IFTTT is known as Microsoft Flow. It allows to connect to both Microsoft 365 and third party services and map a process around these. The great thing about Flow is that it can integrated to includes on premises resources as well as be extended. More power is also available with tools like Azure Logic App and Azure Functions, which can be easily integrated into Microsoft 365.

Introduction to Microsoft Flow

Automation is also available in Microsoft Teams by utilising either the built in bots or even going far as to build your own. You will also find that Teams has a Flow bot that you can incorporated. This shows you the power of the power of the Microsoft solution via the integration of tools throughout the stack. Delivering automation for a business through a services like Teams makes a lot of sense as many of your users are already here most of the time.

The automation tool that most IT Professionals should be focusing on without doubt is PowerShell. Unfortunately, this seems to be the one that garners the most resistance and there is no doubt that getting started with PowerShell can be challenging. However, there are options like Azure Cloud Shell that make this much easier and also allow you to access PowerShell through a browser or even a mobile app.

The way forward with PowerShell is to use it’s ability to integrate and take advantage of the Microsoft Graph. This avoids the need to load multiple cumbersome service modules. If you are looking to invest your time in PowerShell with Microsoft 365 then you should be investigating how to take advantage of the Microsoft Graph using it.

As a final point to consider, I’d recommend you take a look at the following video from Daniel Pink, especially at this point (from about 29 minutes in):

https://youtu.be/CUDqN7MNsRw?t=1662