CHunky Universe of Vigourous Astonishing SHarepoint :)

Category Archives: Office 365

A powershell script for activating an eligible role assignment in Azure AD

Recently my role assignments in Azure AD were switched from permanent to eligible ones. This is part of PIM – Privileged Identity Management, you can read more about it on MS Docs:

To activate your eligible assignment you can use Azure Portal, Graph API, and PowerShell. The activation in the portal and Graph API is described on MS Docs:

My roles within Privileged Identity Management in Azure Portal

I created a simple powershell script for activating my eligible roles quickier when I need it. There are two variants of this script:

  • a generic one, that can be run by anyone
  • a “shortcut” version that can be created for a specific account, a specific role, to make it even quicker.

A generic version

This version fetches the assignments you have, tenant id (resourcid), your account id (objectid, subjectid), and then it activates your desired role. Some parts can be made even more generic, but the key thing here is that you can adjust it and run for any account.

# I use SPO Admin a lot, change it to your desired role
$roleToActivate = "SharePoint Administrator"
# default 2 hours, update it to your needs
$hours = 2
$reason = Read-Host "Justify your elevation"
$connection = Connect-AzureAD
$account = $connection.Account
$tenantId = $connection.TenantId
$user = Get-AzureADUser SearchString $account
$objectId = $user.ObjectId
$roleDefs = Get-AzureADMSPrivilegedRoleDefinition ProviderId aadRoles ResourceId $tenantId
$roleDefinition = $roleDefs | Where-Object { $_.DisplayName -eq $roleToActivate }
$roleDefinitionId = $roleDefinition.Id
$filter = "(subjectId eq '$objectId') and (roleDefinitionId eq '$roleDefinitionId')"
$assignment = Get-AzureADMSPrivilegedRoleAssignment ProviderId "aadRoles" ResourceId $tenantId Filter $filter
if (!$assignment) {
Write-Error "There is no assignment for you as $roleToActivate"
} elseif ($assignment.AssignmentState -eq "Active") {
"Your role assignment as a $roleToActivate is already Active"
} else {
$schedule = New-Object Microsoft.Open.MSGraph.Model.AzureADMSPrivilegedSchedule
$schedule.Type = "Once"
$now = (Get-Date).ToUniversalTime()
$schedule.StartDateTime = $now.ToString("yyyy-MM-ddTHH:mm:ss.fffZ")
$schedule.EndDateTime = $now.AddHours($hours).ToString("yyyy-MM-ddTHH:mm:ss.fffZ")
Open-AzureADMSPrivilegedRoleAssignmentRequest `
ProviderId 'aadRoles' `
ResourceId $tenantId `
RoleDefinitionId $roleDefinitionId `
SubjectId $objectId `
Type 'UserAdd' `
AssignmentState 'Active' `
Schedule $schedule Reason $reason
"Your assignment as $roleToActivate is now active"

Shortcut version

This version assumes that you already know all the ids, by running the generic version or by looking it up in Azure. When you know those ids, you can skip many calls to Azure AD, which makes activation quicker and you can start working on your task rather than surfing around to activate your role in Azure.

# find your guids once and fill in the values
$values = [PSCustomObject]@{
Reason = "Support"
Hours = 2
ResourceId = "f7aa13e9-c03a-49f9-8fd4-c943d2612301"
SubjectId = "cafc35f9-bf31-489a-b468-76580f780506"
RoleDefinitionId = "9039a352-599b-4e09-8693-4a17eb83a73e"
$schedule = New-Object Microsoft.Open.MSGraph.Model.AzureADMSPrivilegedSchedule
$schedule.Type = "Once"
$now = (Get-Date).ToUniversalTime()
$schedule.StartDateTime = $now.ToString("yyyy-MM-ddTHH:mm:ss.fffZ")
$schedule.EndDateTime = $now.AddHours($values.Hours).ToString("yyyy-MM-ddTHH:mm:ss.fffZ")
Open-AzureADMSPrivilegedRoleAssignmentRequest `
ProviderId 'aadRoles' `
ResourceId $values.ResourceId `
RoleDefinitionId $values.RoleDefinitionId `
SubjectId $values.SubjectId `
Type 'UserAdd' `
AssignmentState 'Active' `
Schedule $schedule `
Reason $values.Reason


Save it as a script and run it when you need it. Much quicker. One important note, though: Please be aware that it still can take time to fully activate (propagate) your role, especially SharePoint Administrator, often a couple of minutes. But instead of clicking around, run the script and go grab a cup of coffee, when you’re back, you are good to go.

Security Note. Automating role activations is not less secure. You still have to log in to Azure AD using MFA (I hope you have it) even when you run the script.

Sites.Selected and Governance

The new permission in Graph API – Sites.Selected – is a step in the right direction. Since long we have been looking for ways of scoping the accesses to live up to the least privilege principle. It was either nothing or everything. I have tried out the new Sites.Selected permission and here are my findings.

First of all, if you haven’t heard about Sites.Selected, please visit these pages to find out more. I am skipping the introduction, since there are already good resources on that out there.

List of resources

How to grant permissions

Once you have your Azure AD App and the admin consent for Graph Sites.Selected, all you need is the Azure AD Application Id and Site Collection Administrator on a particular site. The simplest way is to use PnP.PowerShell:

Grant-PnPAzureADAppSitePermission -AppId $appId -DisplayName 'MyTest' -Site $url -Permissions Write

How to see the granted permissions

The only way to the application permissions is PowerShell or Graph, there is no indication on the site.


What about governance

A site collection administrator can grant Read or Write permissions on a site. It gives the desired granularity for application access. But on the other side, there is no way (as of writing) to get all the sites that an Azure AD Application has permissions to.

Which leads me to the biggest weakness of the today’s implementation. Of course, we can traverse through all the sites using powershell and get the summary of all application permissions. The problem is that it can be time consuming in a bigger where you have plenty of sites. Also, it requires that your account that runs the script is a Site Collection Administrator on every site, which is a complete opposite of the granularity goal that Sites.Selected permission tries to achieve.

With that you might end up with several applications that have Write permissions to many sites and you might not have any clue wether it is used or not, who has access to those applications and if they need it.

My wish is that:

  • There will be an api (graph) or azure cli (or similar) that can list all the sites that an application with Sites.Selected has access to, without me being a Site Collection Admin on every site.
  • There will be transparency in the user interface, so that users and site owners can see which applications can read and write content on their sites, the same way as we can see the members of a site.

Monitoring Microsoft 365 using Raspberry Pi and M365 CLI

I would like to show you my recent hobby project with a raspberry pi, a unicorn phat and the powerful cli-microsoft365: A simple monitoring solution of Microsoft 365 Services.

Status of some important services in Microsoft 365

In essence, I put the unicorn phat onto the raspberry pi zero w and wrote this python script:

The python script checks the service status every five minutes and shows it with colors on the unicorn phat.

Color coding

Since the unicorn phat is just a grid of 8×4 rgb leds, I needed to color code the different service statuses (more on the statuses later in this post). I came up with these color combinations. It doesn’t matter what combinations they are as long as they mean something to you (or as long as you can decode them).

  1. 🟩 🟩 🟩 🟩 ServiceOperational
  2. 🟩 🟩 🟩 🟨 ServiceRestored
  3. 🟪 🟪 🟪 🟪 Investigating
  4. 🟩 🟩 🟩 🟪 FalsePositive
  5. ⬜️ ⬜️ ⬜️ ⬜️ InformationUnavailable
  6. 🟥 🟥 🟥 🟥 ServiceInterruption
  7. 🟥 🟥 🟥 🟨 ExtendedRecovery
  8. 🟥 🟥 🟨 🟩 ServiceDegradation
  9. 🟩 🟩 🟩 🟦 PIRPublished
  10. 🟥 🟨 🟨 🟩 RestoringService


There is a list of all possible statuses you can get for Microsoft 365 Services, and it is here:


Install the cli-microsoft365 npm package globally.

sudo npm i -g @pnp/cli-microsoft365

You have to log in, admin consent (if you run this for the first time) and then you can get the status of the Microsoft 365 Services by running:

m365 tenant status list


There are many services in Microsoft 365. I choose the 8 most important ones (from my point of view), because there are only 8 rows on the unicorn phat, you can choose your services and order them as you prefer of course. Beware the spelling and the casing:

  1. SharePoint
  2. microsoftteams
  3. Exchange
  4. OneDriveForBusiness
  5. yammer
  6. Forms
  7. PowerBIcom
  8. Intune

Assembling the hardware

I had my raspberry pi zero w, with raspberry pi os already installed. I attached the unicorn phat using solderless pogo pins. I found a little white cardboard box, cut out a rectangular hole for the unicorn phat and glued the raspberry pi with unicorn inside the box. On the front side I put a sticker with the actual service names for every led row. I connected it to the power, ran the script.

Only three pins are needed.
I tested it first without a box.
I glued the hardware on the inside of the cardboard with a glue gun.
Exploring the @pnp/cli-microsoft365.

Other tips and tricks

The pogo pins were to loose and the leds did not work. I had to shorten the plastic holders a little to tighten the the pins.

Login to cli-microsoft365 as sudo

When I explored the m365 commands, it worked perfectly. My login was cached. Then I needed to run my scripts as sudo, since it requires communication with GPIO pins and the unicorn phat. It didn’t work. The login cache is in different place if you run as sudo. Obvious, when I look at it afterwards, but it took some time to realize that. So, if you are going to do the same, just make sure you log in to m365 as sudo as well, before running the script:

sudo m365 login


This web resource is gold, it shows the pinout and connections to many hats etc:

You only need three pins:

  • 5V Power (Pin 2)
  • Ground (Pin 6)
  • GPIO 18, Data (Pin 12)

subprocess in python

m365 is a command that you can run in terminal, from a python script I use subprocess to call it and get the results.

Running the script even when you log out

start the script with nohup:

nohup python3 &

1TB=1024GB in SPO Storage

You want to calculate your storage capacity in SharePoint Online? Here is how:

  • Every 1TB is 1024GB (it might be confusing, see my previous post, but it’s how it is calculated)
  • A tenant gets 1024GB by default
  • For every user license of a product that includes the service plan called “SHAREPOINTSTANDARD”/SharePoint Online (Plan 1) you get 10 GB extra
  • For every user license of a product that includes the service plan called “SHAREPOINTENTERPRISE”/SharePoint Online (Plan 2) you get 10 GB extra
  • For every user license of a product that includes the service called “ONEDRIVEBASIC”/SharePoint Online OneDrive Basic you get 0.5 GB extra

Products vs. Service Plans

A product (a.k.a. SKU) consists of service plans. E.g. Office 365 E3 (product) consists of SharePoint Online Enterprise among others. It is a Service Plan that gives you additional storage, not a product. The information on “SharePoint Limits” page is (over-)simplified. Simplified for a good reason of course – to give a rule of thumb for calculating your storage.

But if you want to calculate the exact storage capacity, like I do, and even break it down into different departments etc based on licenses, then you need to be aware of the fact that a service plan makes you eligible of more space. A service plan, such as SharePoint Online (Plan 1) can be part of 1 or more products.

Service Plans eligible additional storage and the corresponding SKUs

  • SharePoint Online (Plan 1) – “SHAREPOINTSTANDARD” – 10 GB per user license
    • Project Online Plan 1 – PROJECT_P1
    • Office 365 Enterprise E1 – STANDARDPACK
  • SharePoint Online (Plan 2) – “SHAREPOINTENTERPRISE” – 10 GB per user license
    • MICROSOFT 365 E3 – SPE_E3
    • Dynamics 365 Customer Service Professional – DYN365_CUSTOMER_SERVICE_PRO
  • “ONEDRIVE_BASIC” – 0.5 GB per user license
    • VISIO Online Plan 2 – VISIOCLIENT

ActiveUnits vs. WarningUnits vs. ConsumedUnits

You can ignore the ConsumedUnits, because they are not used in the storage calculation. The ActiveUnits are the ones that are purchased. The WarningUnits are the licenses that have not been renewed and will be removed after 30 days.

So you need to count both the ActiveUnits and WarningUnits. Licenses = ActiveUnits + WarningUnits.

Further reading

Print2SPO – en enkel utskrift till SharePoint

Den här bloggposten är ett (en aning större) användar- (eller verksamhetsutvecklar-)tips om hur man kan sätta upp smarta utskrifter till SharePoint Online – utan några extraappar eller lösningar.

Först och främst, stort tack till min kollega Shahram som har presenterat idén för mig. Tänk dig ett följande scenario:

Du har en mall i Word som du fyller i, skriver ut på papper. Låt säga, det är en plockorder. Du vill digitalisera processen genom att skicka pdf:en till ett gemensamt dokumentbibliotek i SharePoint eller Teams.

Tekniskt är det enkelt, bara man i sin grupp kan komma överens om att göra det så. Då finns många möjligheter, både för att spara skog och kunna samarbeta smartare.


I det här scenariot använder jag ett bibliotek i SharePoint Online, men du kan koppla det även till Teams eller personliga OneDrive. Låt oss kalla det “Plockordrar”


Nästa steg är att lägga till en genväg till min personliga OneDrive. Man kan såklart synkronisera direkt, men i det här fallet väljer jag en OneDrive-genväg.

Mappen dyker snyggt upp på min dator:


Jag trycker på “Srkiva ut” och väljer “Microsoft Print to PDF”

Sedan väljer jag min OneDrive och “Plockordrar” och skriver in namn på filen

Det dyker upp i dokumentbiblioteket.

Smarta funktioner

Nu är jag inte längre begränsad till det analoga. Jag kan jag göra all magi som finns i SharePoint Online för att sätta upp ett smart samarbete med mina kollegor, som till exempel:

  • Lägga till kolumn Ansvarig och en vy “Mina plockordrar”
  • Lägga till kolumn Status för att skilja på aktiva och färdiga plockordrar
  • Lägga till kolumn Datum för att hålla en eventuell deadline
  • Använda kommentarer för att samarbeta med mina kollegor
  • Sätta upp alerts och påminnelser
  • Sätta upp godkännande-flöden etc
  • Formatera listan med olika färger för att kunna se bättre aktuellt arbete

Nästa gång du skriver ut från Word till PDF, kommer systemet ihåg ditt senaste val, så att det kan gå riktigt snabbt.

Det analoga

Den här processen betyder inte att det en tvär övergång till digitalisering heller. Det går fint att kombinera speciellt om man föredrar att ha det på papper! Mer än så. Du kan skriva ut den flera gånger om så behövs. Du minskar risken att det faller mellan stolarna (bokstavligt) och kan minska stressen för dig och dina kollegor.

Andra appar

Det finns dedikerade appar för att skriva ut till SharePoint, de kan vara mer precisa i vissa fall, men även med befintliga medel och smarta processer kan man ha ett smart samarbete i Office 365.

1 TB = 1024 GB in SPO?

There is confusion around how the storage is calculated in SharePoint Online. I believe, in SharePoint Online 1 TB is 1024 GB (based on powers of two), although the SI Prefix is for numbers based on powers of 10 (1TB = 1000GB, Wikipedia). In this post I would like to summarize the results of my investigations and I hope Microsoft or the community can confirm or disconfirm this.

First, let me explain why we care about it. The storage in SharePont is limited and we need to keep an eye on it. Especially in our case, where we need to track storage utilization across different parts of the organization/our tenant. The storage in SharePoint is calculated like so:

1 TB + 10GB * E-licensed users

The tricky part, though, is how to convert it into TB correctly.

Why I believe Microsoft treats 1 TB as 1024 GB

First of all, I can see it clearly in my dev tenant with exactly 25 licenses.

That would give 1TB + 10GB*25 = 1,25 TB if it would be based on powers of 10. But it isn’t because the storage I get is 1,24 TB, or 1,244 to be precise.

That means, for every E-license you get 10 GB or 10/1024 TB.

That also means you need more licenses to get the desired storeage. E.g. 10 TB more storage requires 1024 licenses and not 1000, 10 TB = 10240 GB, 10240 GB / 10 = 1024 E-licenses.

Also in OneDrive, the initial space I get, is 1024 GB (or 1TB). If 1TB = 1024GB in OneDrive, why should SPO be different?

Further, the MSDocs page reveals that the 25 TB are 25600 GB (which is exactly the product of 25 and 1024):

One contradictory page, though is the news about storage increase:

The calculations there are based on the decimal system:

Calculation of MB and GB

Just to verify how the storage is calculated in KB, MB and GB, I looked at the Storage of a SharePoint site. Luckily, I can get the storage used in Bytes, MB and GB (from different sources) and compare them to each other.

When I calculate back and forth I can defnitely see, it is multipled/divided by 1024, hence powers of 2:

The values in blue are the reported values. The other values are calculated.

The values in GB are exactly the same, the Bytes, KB and MB differ a bit due to rounding

Demote News in SharePoint Online

In case you published a news in SharePoint Online and you now want to “downgrade” it to a regular page, I have a solution for you.

The reasons why you would like to demote a News might vary:

  • You publish an important message, perhaps a note about an operational disturbance. It might not relevant anymore, but you still want to keep the page in case someone wants to access the information.
  • You accidentally create a page as a news.
An example of a news of a temporary character

The News “Promotion” is controlled through the field called “Promoted State”. You cannot edit, but you can include it in a view to check the current state of it.

  • “0” means a regular site page
  • “2” means News
Show the “Promoted State” and ID to check the current status and identify pages and news

So in order to convert a news to a regular page, you need to update the list item’s field “Promoted State” from 2 to 0.

Unfortunately, the Promoted State field is read only. Hence we need to make it editable first, edit it, and in the end, reset the field.

Lucky us, that is easily done with PnP 🙏🙏🙏🙏🙏

$site = "https://{tenant}{site}"
Connect-PnPOnline Url $site SPOManagementShell ClearTokenCache
$field = "Promoted State"
$list = "Site Pages"
$id = 85 #the item id of the news
Set-PnPField Identity $field List $list Values @{ReadOnlyField=$false}
#make sure to enclose the number into quotation marks
Set-PnPListItem List $list Identity $id Values {"Promoted State"="0"}
#reset the field
Set-PnPField Identity $field List $list Values @{ReadOnlyField=$true}
view raw demote-news.ps1 hosted with ❤ by GitHub

That’s it. Easy, isn’t it?. I wonder why the field is readonly, though. What downsides might this procedure have? What do you think?

Kombinera två SharePoint-listor i PowerBI

Det här är en enkel guide på svenska om hur du kan ladda in data från två eller fler listor i SharePoint och lägga ihop dem till en.

Scenariot är följande. Du har två eller fler sajter i SharePoint Online som har var sin lista (med samma kolumner). Du vill ladda in data från båda och se en aggregerad/summerad version. Alternativet är att ha en delad lista, men ibland (av behörighetskäl eller av behovet för smärre anpassningar av enskilda listor), ligger det i separata listor/sajter.

För enkelhetens skull, har jag följande demouppsättning:

  • Två enkla listor i samma sajt:
    • ShapesA
    • ShapesB
  • Listorna innehåller två exakt likadana kolumner
    • Shape
    • Color
Första listan
Andra listan

Målet är att addera raderna ifrån två listorna till en större lista.

Power BI

Att läsa in SharePoint-listor in i Power BI är ganska enkelt. Starta Power BI Desktop på din Windows-dator.

Man börjar med Get Data…
Filtrera på SharePoint och välj SharePoint Online List
Klistra in länken till din sajt
Välj listorna
Viktigt! Tryck på “Transform Data” först
Radera alla kolumner du inte behöver. Gör det på båda listorna.

Nästa steg är det vikigaste i den här guiden: Kombinera de här två listorna: Append Queries

Append Queries
Välj “Append Queries as New”
Välj lista 1 och lista 2 och klicka på OK
När den är färdig, klicka på Close & Apply

Den kombinerade datan ligger i “Append1”


Resten är “bara” visualisering. “Bara”, eftersom det hårdaste jobbet (Tranformation, kombinering) är redan bakom oss. Testa olika alternativ.


Ett exempel är Treemap:

För att få till Treemap i det här exemplet behövde jag ha Title (=Shape) både i Group och Values

Summering och reflektion

Den här guiden visar hur man kombinerar (lägger på) två listor och jobbar med dem som om de vore en lista. Hör av dig om det har varit till hjälp eller om du har frågor eller funderingar.

Själva behovet är verkligt. I SharePoint har det alltid varit en utmaning att samla ihop datan från olika sajter och listor. Vi har använt oss av söken (med Sökcenter och DisplayTemplates) och andra tekniker. Visst vore det enklare att ha det samlat i en central databas eller en lista. Det är dock en viss frihet att låta olika organisatoriska enheter “äga” sina delar (användare kan justera sina vyer, ordna formatering, lägga till extrakolumner och annat trevligt). Aggregeringsbehovet kan nu, med Power BI, lösas på ett relativt smidigt och användarvänligt sätt.

Add a security group as hubbers using PowerShell

Today I needed to add a security group to “People who can associate sites with this hub” through PowerShell. Here is quick how-to. I usually say “hubbers” instead of the long “People who….”. By the way, if you want to know what prerequisites there are for being a hubber, read my other blog post

An example of how to add a security group is missing in the MS Docs:

This is easy, all you have to do is to find out the security group id, prepend it with “c:0t.c|tenant|” and run the command.

$url = ""
$groupId = "0b2d5885-158a-4aa1-bea3-cd28f71fcc37"
$securityGroup = "c:0t.c|tenant|$groupId"
Grant-SPOHubSiteRights $url -Principals $securityGroup -Rights Join

That’s it. I’ll try to provide this example to the MSDocs github.

Own praise badges in Teams

While reading the Teams News recap from December 2020, I found one thing that caught my attention particularly: Custom Praise Badges in Teams. Let’s try this. But first, those badges are the default ones:

They are good starting point. But to take it a step further, to really engage people and praise, you need some specific badges that mean something for your company.

To add a new badge all you have to do is to go to Teams Admin (obviously it requires you having the Teams Administrator Role):

  1. Click on Teams Apps –> Manage Apps
  2. Search for “Praise” and open it
  3. Click on Settings and scroll down to Custom Badges
  4. Click on “Create a custom badge”
  5. Give it a name, upload a picture, define the text color and the background color.
That’s how you add a new custom badge as a Teams Administrator.

That’s it! When I added my little construction worker badge, it took seconds (but be patient if takes more time).

You can select the new badge when you are about to praise someone.

Custom badges might be this little “extra” that make difference for better adoption and better collaboration in your team and your organization.

Here I praise my colleague Adele Vance. Well you know Adele Vance, she works in every demo Office 365 environment 😜

Of course, for those who has not tried the Praise feature in Teams yet, I highly recommend it. Next time you want to say “Thanks” to someone or show your appreciation, just hit this tiny badge button within the “New Conversation” in Teams.

The size

The badge image should be 216×216 px.

Learn more on MSDocs:

Manage the Praise app in the Teams admin center – Microsoft Teams | Microsoft Docs

Daniel Chronlund Cloud Tech Blog

News, tips and thoughts for Microsoft cloud fans

Вула Чăвашла

VulaCV - Чăвашла вулаттаракан сайт

Discovering SharePoint

And going crazy doing it

Bram de Jager - Architect, Speaker, Author

Microsoft 365, SharePoint and Azure

SharePoint Dragons

Nikander & Margriet on SharePoint

Mai Omar Desouki

PFE @ Microsoft

Cameron Dwyer

Office 365, SharePoint, Azure, OnePlace Solutions & Life's Other Little Wonders


Me and My doings!

Share SharePoint Points !

By Mohit Vashishtha

Jimmy Janlén "Den Scrummande Konsulten"

Erfarenheter, synpunkter och raljerande om Scrum från Jimmy Janlén

Aryan Nava

DevOps, Cloud and Blockchain Consultant


SharePoint for everyone


Ryan Dennis is a SharePoint Solution Architect with a passion for SharePoint and PowerShell

SharePoint 2020

The Vision for a Future of Clarity

Aharoni in Unicode

Treacle tarts for great justice

... And All That JS

JavaScript, Web Apps and SharePoint


Mostly what I know and share about...