Discover embedded Stream links in any SharePoint site and page

In case you haven’t heard yet, the Stream (classic) portal has already been shut down and content will be deleted.

While it’s hard to hear anything through the constant chattering about Copilot, there are regular changes to Microsoft 365 such as product renaming (every other week ), deprecations, licensing requirements, new features, etc. The shutdown of the Stream (classic) service and portal is a fairly big one.

Why it’s so big, is that for several years it was the only video portal we had in the service, and the default location for organisations to store Microsoft Teams meeting recordings.

In fact, many organisations actually invested time and effort into utilising the platform properly by creating channels, playlists, and integrating the stored videos into learning management systems, intranets, and other places.

Silly them!

The Challenge

While Microsoft provided tooling to identify and migrate Stream videos over to SharePoint, a big problem remains: we don’t know where those videos are referred to.

What I mean by this, is that organisations may have done a series of workshops and stored them in a Stream channel which was then embedded in a SharePoint page. Or if not embedded, even just linked to at all.

Because the migration tool does not offer any functionality to detect these links or embeds, we have a problem on our hands. This problem is due to the fact that for a period of time any migrated videos will still work using their old URL, but at a time in the near future (February 2025 at this stage), that will stop.

So even though the content may have been migrated, if the link is still pointing to the old address – then it just won’t work.

There are tools out there that allow organisations to scan through their content for specific words, and even with some colleagues I had a disagreement discussion about buy vs. build.

Normally, I’m all for getting third-party vendors and their tools in to do the job because ultimately, they have created the product out of a need and usually it’s good at the job.

However, for something specific like this, we don’t necessary want to go out and find/procure/configure external tooling. In many cases like this where we just need a point solution, a PowerShell script comes along to do what we want.

And in this scenario, it reiterated the point of why I prefer to do things in Power Automate instead of PowerShell. Because while the script our team used was built by an amazingly talented colleague, it utilised the PnP PowerShell module which sometimes doesn’t work exactly how we want – which is what happened.

So, reinforcing my the nickname of “Captain Automate” bestowed upon me by my colleagues, I built a solution in Power Automate using my good friend Microsoft Graph to make this work.

The Solution

Effectively what the workflow does is retrieves all SharePoint site collections from the admin center listing and then queries all pages within each site to see if they contain the specific text you’re looking for. In this case the text we’re looking for is “web.microsoftstream.com”, but the way I’ve built the workflow you can actually search for any text you like.

The output is then stored as a CSV in the SharePoint site of your choosing.

Because we’re using Microsoft Graph, this solution requires an Entra ID (formerly known as Azure AD) App Registration with the Sites.Read.All application permission, and an account that can use the HTTP connector (i.e. with the Power Automate per user license).

When you’ve created that App Registration you’ll also need to create a secret which we’ll use in our workflow.

Here’s the flow from start to finish:

Looks simple doesn’t it?

Step 1: Start your variables!

As I built this workflow for myself and my colleagues to apply to any Microsoft 365 tenant, I use the trigger to capture the key information that would change between organisation to organisation.

From here, I pass all these inputs into variables:

The logic behind doing this, is that if we modify the trigger inputs it would not affect the areas of the workflow where those values are utilised.

Additionally, if you only want to run this against a single tenant – you can remove the inputs without having to figure out what will be impacted.

So yeah, I did this for you, the reader. No no, please, no need for any gratuities, livestock, job offers or marriage proposals – I’m a kept man on multiple levels. Enjoy.

We also have an array variable which is where we’ll be storing every hit we find for future use:

Step 2: It’s like looking into the sun!

Next, we’re going to need to find all the SharePoint sites in the tenant. While you can query Microsoft Graph for this, I found a simpler way was to query the SharePoint lists that power the SharePoint Admin Center.

This is where the tenant domain variable and input we captured earlier comes in:

Next, we’re going to isolate the specific list in the admin center that stores all of the sites in the tenant, and get just it’s ID:

Once we have this, we then query the list to get all the items in it:

Now that we have that, it’s time to do the heavy lifting.

Step 3: The heavy lifting

Here’s an overview of the loop we’re going to have run against every site from the previous list:

Let’s break the loop down.

Scan all pages in the site at once

Using a beta endpoint (use at your own peril as it’s not officially supported) we can get not just a list of every page in the site, but also their contents! This allows us to scan a site almost instantly, without having to manually query every page. While we will query each page in the site later, this initial scan allows us to determine whether we need to scan pages in the site at all.

However, one thing we need to do, is tidy up the SiteId we retrieved from the ‘all sites’ list. This is because the values in the SiteId field are stored with braces (aka curly brackets: {} ) around them – which we need to get rid of.

For that, I’ve opted to use the substring method (you could also use replace, but same/same):

substring(items(‘Apply_to_each’)?[‘fields’]?[‘SiteId’],1,sub(length(items(‘Apply_to_each’)?[‘fields’]?[‘SiteId’]),2))

We then want to know – does any page in the returned results contain the text we’re looking for?

For that I’ve opted to use a contains expression inside of a compose action, which is then used in a subsequent condition. Could I have just done the contains expression inside of the condition action? Yes. But I’m not trying to win the “Leanest Workflow of the Year” award here. I like to break my workflows down to individual blocks as it helps me troubleshoot if I need to. Anyway, moving on from your judgement…

contains(string(body(‘HTTP_-_Get_pages_in_the_site’)), variables(‘SearchText’))

If it’s a hit, then we move onto the next task – individually querying each page on the site.

Querying each page on the site

Now that we know a page in this site has the text we’re looking for, we need to find which page and retrieve certain bits of metadata from it. Here, we’re going to query the page and its web parts, follow it up with a contains expression, and then a condition based on the results:

For each page within the site that contains our search text we record it into the array:

Now that we’re capturing the pages, we need to output them to something useful.

Outputting the data to something useful

While I love SharePoint Lists and Dataverse tables, sometimes you just can’t go past a good old Comma Separated Value (CSV) file. And that’s what we’re outputting to here, because it’s just so versatile!

Now, I have configured the next action to run even if the previous scope fails because you never know – one site out of thousands may give you grief and show as a failure, causing us to lose volumes of juicy, useful data.

I’ve still opted for a condition here, just in case it returns with no results – we don’t need a file. But if there are results, let’s output our array into a CSV table, and then store it in a SharePoint document library.

Why SharePoint and not your OneDrive? Because most likely this text scan is for a client, or a project, or something it relates to – which means other people would quite possibly be involved, WHICH MEANS IT SHOULD BE IN SHAREPOINT!!!! Wanna fight about it!?!?!?

Oh wait, sorry, we’re not here for that.

If you want it in OneDrive instead of SharePoint, change the action around. I don’t mind. Do it. No skin off my nose. It’s your workflow now. I’ll just be over here, judging you, like you judged me for my expressions and action usage.

Summary

What we end up with is a nice CSV output like this:

You can either leave it like that, or create a Power BI report like this:

Could the workflow be more elegant? Absolutely! Could it have a few less steps? Definitely.

But it works, and I’m happy with it. Enjoy!

Download the workflow and Power BI template from here.


Also published on Medium.


Discover more from Loryan Strant, Microsoft 365 MVP

Subscribe to get the latest posts sent to your email.

Discover more from Loryan Strant, Microsoft 365 MVP

Subscribe now to keep reading and get access to the full archive.

Continue reading