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

CIAOPS Need to Know Office 365 Webinar–December 2017

laptop-eyes-technology-computer

The last Office 365 webinar for 2017, oh where has the year gone, is now scheduled. Not a lot of time this month when you take holidays into consideration but hopefully you can attend. The session will have news plus updates as well as deep dive into Microsoft Flow, which is the automation engine built right into Office 365. I’ll show you how to use Flow to start automating all those repetitive processes.

You can register for free at:

December Webinar Registrations

The details are:

CIAOPS Need to Know Webinar – December 2017
Thursday 21st of December 2017
11am – 12am Sydney Time

All sessions are recorded and posted to the CIAOPS Academy.

There of course will also be open Q and A so make sure you bring your questions for me and I’ll do my best to answer them.

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.

When good Flows go rogue

At about 2.15am local time this morning, two Microsoft Flows in my Office 365 tenant went rogue and started blasting select email addresses with continual emails.

The two Flows in question I used to handle registrations for my regular monthly webinars. You can read more about how I created these here:

Using Microsoft Flow for event confirmations

Basically, they are triggered by a submission from Typeform. They then send the registrant a confirmation email as well as writing the details to a SharePoint list. These Flows are linear and incorporate no looping. These Flows had run successfully for over 12 months and had not been edited, changed or even viewed in a few weeks.

However, at approximately 2.15am local time, both of these Flows started to execute repeatedly sending hundreds of emails to a select group of people who had previously registered for the webinars.

image

The above shows a very small sample of the the sent items from the mailbox in question.

The mailbox sending out the emails from the rogue Flows was not my production mailbox so when I checked my production inbox just before 6 am local time when I awoke, I was quickly made aware of the issue from various people.

I immediately logged into the tenant with the rogue Flows and disabled the Flows but emails continued to be sent. I then went in and deleted the Flows but email continued to be sent. I therefore went in and created an Exchange transport rule to prevent that mailbox from sending anything further.

At that point the emails stopped being sent. In hindsight, that could have been from exhaustion of emails queued to be sent upon disabling the Flows. Whatever the reason, outbound emails had apparently stopped.

I immediately then logged a support request with Microsoft to confirm that the rogue Flows where not still running in the background, even though I had deleted them.

My request was escalated to the SharePoint Team who look after Flow. All the details of my situation were recorded and verified via a screen sharing session.

With the Exchange transport rule still in place I looked at the Flow Admin and found:

clip_image001

I then downloaded the CSV file to get more details and found:

clip_image001[5]

The two rogue Flows had each run almost 5,000 times. Clearly an issue.

At this stage Microsoft is still investigating the issue behind the scenes and I have removed the Exchange transport rule and confirmed emails are not being sent. Thus, it appears the rogue Flows have ceased.

What is interesting here is that the Flows that went rogue were only designed to run once someone completed the online Typeform. However, overnight they decided to run over and over again obviously caught in some sort of loop.

My guess as to the cause is that the Typeform connector used with Microsoft Flow received some type of update causing it to replay previous registrations over and over. The strange part is the fact that it kept repeating even though it was never designed to loop.

I am sorry to those people who received over 600 emails from me due to this issue and if it keeps happening or reoccurs please contact me asap and let me know.

With both Flows now deleted I am going to have to rebuild them but the question is how (can?) I prevent something like this happening again?

My current thinking is that I move the registrations to their own dedicated email box that I can, in the worst situation, completely delete if needed. I also need to work out some sort of rule that prevents constant email being sent if they exceed a threshold (say 10 emails in 10 seconds) and take appropriate action.

I’ll have to have a think about how (or if) I can do this and how I go about creating and monitoring any new Flows I create. I welcome any suggestions people might have on how I can prevent a recurrence.

A painful example of what happens when automation breaks.

Introduction to Microsoft Flow

Microsoft Flow is way to automate your business processes. It is way that you can connect to both cloud and on premises services and trigger actions and results.

The above presentation is one that I recent provided to the SharePoint Users Group in Sydney that I re-recorded to make available publically.

If you want the slides they can be found here:

https://docs.com/d/embed/D25190739-0716-1916-3610-001758080412%7eMd4186d87-61d5-259a-4d26-00a8bd86cfff

 

https://doc.co/6DSLKk