Welcome to AlanPs1.io

This blog documents some of what I do every day. If you are a Sys Admin, an IT Pro or a DevOps Professional you might just find it useful. The content you will find should suit the beginner, intermediate users as well as advanced users. Each post has some nuggets of joy. I hope you find some content that is useful.

The content is mostly relating to Office 365, PowerShell and Power Platform such as Flow & PowerApps. I will be sharing tech tips and tutorials mostly linked to Office 365 administration and showing you ways to automate business processes using Flow, PowerApps and PowerShell. My focus lies mainly with Office 365 and I use the necessary tools to work with data from the various Office 365 APIs and Microsoft Graph etc. You will see me use PowerShell and the Power Platform to harness data and hopefully do something useful with it. Then, as time goes on I should be able to introduce more Azure related content as my discovery of all things Azure progresses.

Get All Site Administrators and Owners With Full Control – SharePoint Online PowerShell

A commonly posed question within an organisation that use SharePoint Online is "Can I get a list of all the site administrators?". A perfectly reasonable question, I am sure you will agree. Regardless of whether your role is a SharePoint Administrator, Office 365 Administrator or even a PowerShell Developer, you will spot there is more to this request than meets the eye. You will no doubt start googling looking for scripts that get all SharePoint Administrators or all SharePoint Site Owners. Why re-invent the wheel after all.

Google SharePoint Administrators PowerShell

I found several scripts and blog posts that mostly met my needs. I did feel the need to start from the ground up so this is what I am sharing with you in this post.

Site Administrators or Members with Full Control?

Most administrators will find themselves looking initially at Get-PnPSiteCollectionAdmin, which is an admirable starting point.

Read More

Power Query – User Power BI or Excel to connect to Office 365 Service API

Using Power Query within Power BI and within Excel has many uses. In this conceptual example, I will make data from the Office 365 Service Communications API available within excel so we are able to check the service status of each cloud service. At a glance we can then determine if there is any Service Degradation affecting an Office 365 Service.

Here is a method (MFA bypass required) you can use to get at the data in which you can then model:

Azure App to Allow API Communication

Firstly, you will need an Azure App registration set up with permissions to access the API. At a minimum, the delegated permission for ServiceHealth.Read is required.

Once you have the App set up, you will need to create/retrieve then capture the following values:

  • Tenant ID
  • Client ID
  • Username
  • Password
  • RedirectURI
Read More

Create Azure App Registration for Accessing Office 365 Service Communications API

In order to create an App Registration in Azure to allow Oauth methods when contacting the Office 365 Service Communications API follow these steps if using delegated permissions.

NB: Grant any permission your current task requires.

  • Open portal.azure.com and go to App Registrations.
  • Choose + New registration
  • In the Register an application form provide a name (O365ServiceApi)
  • Select Accounts in this organizational directory only
  • Choose Register

Here is a visual representation of those steps:

Now let's give the app some permissions.

Read More

Adding Retention Labels to Subfolders in SharePoint Online and OneDrive

Having authenticated to a SharePoint online Site, Site Collection or a OneDrive site, like me, you may have the need to add retention labels to folders and the files within those folders. I have seen this done a lot using the SharePoint Online PowerShell Module but in terms of authentication in the modern world it is not so easy to authenticate to that module at site level.

So in steps the mega powerful PnP PowerShell. The method I will demonstrate below will use PnP to apply the retention label to both root Documents & Shared Documents and an alternative way to apply the labels to subfolders too.

Should you be looking to automate this process in bulk or are just looking for an easy way to authenticate without having to respond to MFA prompts. Please see Connect-PnPOnline Unattended Using Azure App-Only Tokens.

Set Retention Label on the Root Folder

$RootLabel = "Default"

# SharePoint Online
Set-PnPLabel -List "Shared Documents" -Label $RootLabel

# OneDrive for Business
Set-PnPLabel -List "Documents" -Label $RootLabel
Read More

Connect-PnPOnline Unattended Using Azure App-Only Tokens

There are lot's of reasons why unattended authentication is handy using modern authentication methods. This is true in Azure, Exchange Online, Azure AD, SharePoint & more. Azure Automation is the main reason I tend to use them but more regularly, I create scripts, functions & modules that will be used by my colleagues. If I set up authentication using a service principal/Azure App registration, I can then pre-configure the less sensitive data within, leaving my colleagues to pass the certificate password, secret or perhaps thumbprint to the script via the console. This keeps the sensitive data safe but takes away they need for the MFA prompts and permission checks.

In this example, I am going to walk through how I set up an Azure App registration that can be used in this way. There is a very helpful post at PnP-PowerShell covering Connect Using App Permissions that is probably worth a read. This approach is more simple but works very well and it uses a self signed certificate. I have found it pretty easy to put the certificate in a shared area making it simple for the code to access when colleagues use my PowerShell offerings too.

Here is the code

$Password = "P4ssW0rd"

$SecPassword = ConvertTo-SecureString -String $Password -AsPlainText -Force

$Params = @{
    Out                 = "C:\temp\LabSPOAccess.pfx" 
    ValidYears          = 30 
    CertificatePassword = $SecPassword 
    CommonName          = "LabSPOAccess" 
    Country             = "GB" 
    State               = "Scotland" 
    Locality            = "Glasgow"

$Cert = New-PnPAzureCertificate @Params
Read More