CHUVASH.eu

CHunky Universe of Vigourous Astonishing SHarepoint :)

Category Archives: Office 365

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}.sharepoint.com/sites/{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”

Append1

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

Visualisering

Ett exempel är Treemap:

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 = "https://tenant.sharepoint.com/sites/hubsite1"
$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.

Two workarounds for overcoming the listview threshold

These are two workarounds to see documents / list items in a view that exceeds the listview threshold of 5000 items.

Overcoming the listview threshold is as fun as succeeding in the limbo games.

This is changing all the time. When you read this, it might have changed. Today, 2021-01-05, me and my colleague found following two workarounds for listing over 5000 items in a list view in SharePoint Online:

  1. Sorting by name in a view
  2. Adding a shortcut to OneDrive

Both methods require the Modern UI in SharePoint Online.

In our case we have a migrated document library with many items. In the source, the threshold was much higher, in SharePoint Online some folders didn’t show anything. It showed only “Something went wrong”:

The classic view had a better error message, but no solution for that:

This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.
To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.

Sorting by Name

We will split those big folders into smaller ones. But while trying things out, we found that having Sorting by name, suddenly showed the documents in the big folders.

As a user you have to scroll a lot to find your document (because of the infinite scroll), but still, now you are able to see your documents!

Also, you can start selecting files and re-organizing them by using “MoveTo”.

Adding shortcut to OneDrive

Another workaround (or I’d rather say trick) is to open the folder from within your personal OneDrive by adding a shortcut:

With that you’ll get the folder linked in your OneDrive.

Even with a big number of files, OneDrive will list the folder. Why is that? Maybe, Microsoft treats personal OneDrives differently, more gently, in a more forgiving way.

On your computer you’ll see the linked OneDrive folder, too.

Teams incoming webhooks for performance monitoring

Incoming webhooks in Teams are great, indeed. Last week I saw this tweet, which inspired me to share one of our webhooks.

There are many scenarios where it can be used, I would also like to share one of our scenarios: notifying our DevOps team about performance issues in SharePoint Online, detected through Exoprise.

This adaptive card (in Swedish) shows an alarm from Exoprise about login problems.

It’s simple and scalable:

  • On the Exoprise end you can define any combination of larms and connect it to a Teams Incoming Webhook.
  • On the Teams end, you can invite all the people who need to be notified, and you can also set up push notifications if you have Teams installed on your mobile.

This is just a little “demo”. We use Exoprise Larms, Teams and Adaptive Cards. I can share more technical details if you are interested, but in that case, leave a comment below.

DIY: Integrating Trådfri lights with Teams presence

It seems that Work from Home (WFH) is here to stay, it’s okay. I’d say, Work from a Smart Home is even more okay. To me, Home Automation (HA) and Work from Home (WFH) are really two peas in a pod.

Today’s “guest” is a tiny application that I’ve set up on my raspberry pi to listen to my presence (status) in Teams and show it with colors of my smart RGB light (IKEA Trådfri).

The code

You can find the application on github:

I’ll try to provide the needed documentation on the github repo and focus more on the story part in this blog post.

The story

I stumbled upon Elio Struyf’s blog post and I was amazed:

Wow! I thought immediately: that would be a cool challenge, I wanted to set up this, too. Although, with some adjustments for my smart home:

  • I wanted to run the whole application on one raspberry pi only, because I don’t have the second one, neither I have HomeBridge installation (maybe something for future projects, though).
  • I wanted to have as little code as possible, maintenance should be kept to minimal.
  • I wanted to use python in order to learn more python and because python seems to be the most supported language on the pi.
  • I wanted to use IKEA Trådfri lights (with a gateway and a remote) that I already have invested into.

I omit the configuration steps for Trådfri lights and Raspberry Pi, you can see them in my previous blog post:

Why show Teams presence with a smart light

Elio wrote his blog post in April this year – in the times of the lockdown in Belgium. In Sweden, we hadn’t a real lockdown, but it seems that it might come times when my children would need to be at home more while I work. In that case a superclear system that shows when I have important meetings is just awesome. Maybe, with that I am prepared for such times.

But to be really honest, the main driving factor is the fact that it is very satisfying to tinker around with this DIY stuff 😜😎

Lessons learned

There is a python wrapper for MSGraph which is awesome, but it needs more contributors:

In your Azure AD App Registration you can specify auth flow type as public, with that you don’t need to store a client secret for delegated access. That was a kind of a new thing to me.

Presence endpoint in MSGraph is in beta, make sure you call the beta endpoint. The scope is ‘https://graph.microsoft.com/Presence.Read’ and you need an admin consent for that permission grant.

Flashing Trådfri lights on Azure Alerts

What if you put together Work From Home and Home Automation? Well, removing the common denominator (HOME) would mean Work Automation (sic!). I want to tell you about a tiny hobby project I have had at home, still related to work of mine: Whenever an Azure alert is triggered, my Trådfri smart light from IKEA flashes for a couple of seconds.

Summary (if you want to skip the long story below): The solution is a tiny web application. The publicly accessible url, exposed using ngrok, is registered as a webhook in an Azure Alert. It’s on Github, you’re welcome to use it as you please 😎:

How I did this (the long story)

The github repo (linked above) is self-expaining, but here comes the story. I used the same setup for Azure Alerts as described in my previous blog post:

When I was done setting up an alert, I thought: besides a notification in a Teams channel, I thought: what if I could show the alert visually using some LED or similar? Then I thought about Home Automation and a Trådfri RGB bulb I’ve got. That’s the beauty of the mentioned equation: Work From Home and Home Automation. We can pick the best parts of it and combine to something unique.

Since I have a kit from IKEA containing a gateway, a remote, and an RGB lamp, I wanted to do something with that. Unfortunately I didn’t find any routines (Google Home), applets (IFTTT) or automations (Home app in iOS) that could do it.

Luckily, there is a way of controlling the Trådfri lights, best described in this tutorial:

As in this tutorial I also used a Raspberry Pi Zero W, and it went very well, except one thing: Trådfri team introduced a change for the security code, I needed an additional step that was missing, more on that later.

The flow from an Azure Alert to the flashing light.

The tutorial says: the world is your lobster. My “lobster” is a webhook that makes lights flash on an alert, so I needed to have a simple web server (http.server) and a tunnel to my network (ngrok). It was best to take one step at a time.

Step 1. Connect

First, I wanted to make sure I could have a simple web server that could host my webhook. I followed the advice from that tutorial and used http.server python module:

I didn’t need to install any additional modules, you have this already on the Raspberry Pi OS. Just create a simple file like this:

from http.server import BaseHTTPRequestHandler, HTTPServer
host_name = '192.168.0.193'
host_port = 8000
class MyServer(BaseHTTPRequestHandler):
def do_HEAD(self):
self.send_response(200)
self.send_header('Content-Type', 'text/html')
self.end_headers()
def do_GET(self):
self.do_HEAD()
self.wfile.write("hej".encode("utf-8"))
if __name__ == '__main__':
http_server = HTTPServer((host_name, host_port), MyServer)
print("Server Starts – %s:%s" % (host_name, host_port))
try:
http_server.serve_forever()
except KeyboardInterrupt:
http_server.server_close()

Start it:

python3 alert-step1-server.py
view raw alert-step1-start.sh hosted with ❤ by GitHub

I opened that page, (192.168.0.193:8000), and I could see “hej”, time to go further.

Step 2. Connect World

Next step was to open up this “web app” for the world, to make it accessible from outside my local network. ngrok is the best solution for that. I followed that guide to install ngrok on my Raspberry Pi Zero W.

The installation process was pretty straight forward, for the record I tried to install ngrok as a snap, it did not work.

cd ~
wget https://bin.equinox.io/c/4VmDzA7iaHb/ngrok-stable-linux-arm.tgz
tar -xvzf ngrok-stable-linux-arm.tgz
view raw alert-step2-ngrok.sh hosted with ❤ by GitHub

I also fetched the authtoken and registered it locally

Then I started the ngrok tunnel:

And my web app went online:

Step 3. Harness the lights

Now to the core of this hobby solution: controlling Trådfri lights.

I installed, configured and built the libcoap client, as described in the blog post I already mentioned:

But I also installed git, because my Raspberry Pi OS installation didn’t have it.

sudo apt-get install build-essential autoconf automake libtool git -y
git clone –recursive https://github.com/obgm/libcoap.git
cd libcoap
git checkout dtls
git submodule update –init –recursive
./autogen.sh
./configure –disable-documentation –disable-shared
make
sudo make install

Next, I found the IP Address and the security code of the IKEA Trådfri Gateway, using my router:

Then I created a new preshared key (that’s the news I mentioned above). With just the security code, you will get 4.01 “Unauthorized” when you try to control the lights, as described:

# -k = Security Code, that you can find on the back of the gateway
# 9090: xxx, your new client identity, you decide, in my case TOLLERASP0
# coaps: the ip address is the one of your gateway
coap-client -m post -u "Client_identity" -k "OHsfKxV0UaJu81" -e '{"9090":"TOLLERASP0"}' "coaps://192.168.0.120:5684/15011/9063"

I got the pre-shared key that I saved for later use:

With this information you can harness the IKEA lights:

# off
coap-client -m put -u "TOLLERASP0" -k "{presharedkey}" -e '{ "3311": [{ "5850": 0 }] }' "coaps://192.168.0.120:5684/15001/65537"
# on
coap-client -m put -u "TOLLERASP0" -k "{presharedkey}" -e '{ "3311": [{ "5850": 1 }] }' "coaps://192.168.0.120:5684/15001/65537"
view raw alert-step3-onoff.sh hosted with ❤ by GitHub

5850:0 is off, 5850:1 is on. Easy-peasy, right?

Want to know how to control the brightness, the colors etc, just check this documentation (already mentioned):

Step 4. Put everything together

When I knew I could have a simple webhook service, locally (step 1) and on the WWW (step 2), and that I could control the smart light I’ve got from IKEA using code running on my raspberry pi, then connecting everything was easy. I created a repo for that and you can see that it is a very simple one:

The main part is in the server.py. When it gets invoked, it calls the flash function. It uses os.system to call the libcoap-client and time.sleep for delay parts needed in the flash action. The configuration is parsed using configparser and the server is a simple http.server.

In the end I registered the ngrok endpoint in my Azure Alert Rule Action Group:

Then I triggered my test logic app that failed reliably 🙂

After 1-2 minutes my smart light started flash:

Success 🎯🎯🎯🎯

Words of caution and Tips

Security

http.server does not provide the right level of security, it’s most for prototyping. For this tiny hobby project I have, it’s exactly what I need. Don’t use it as it is for production.

Treat the security code your preshared key appropriately, you don’t want to be hacked.

Flashing lights reacting to alerts is cool, but think about the work-life balance. Don’t have it in your bedroom 😎.

Inspect ngrok from other computer

By default the ngrok web inspect interface is only available from localhost (127.0.0.1), make it available across your network by configuring ngrok:

# ip address of your raspberry pi
echo "web_addr: 192.168.0.193:4040" >> ~/.ngrok2/ngrok.yml

Reserve your local ip addresses

The router can assign new ip addresses to your devices. Reserve the ip addresses of your raspberry pi and your IKEA Trådfri Gateway. It will make your life easier.

Start ngrok closer to you and in the background

EU is closer to me, but also running the background is nice when you only have one terminal:

# -region eu
# >/dev/null & for running in the background
~/ngrok http 192.168.0.193:8000 -region eu > /dev/null &

Replay ngrok calls

This is a game changer: rather than wait for an alert to be triggered, you can just Replay it over and over again while you mickle-muckle your python code locally.

Keep running your server after logout

You just need to to have “nohup” when you start your server, ngrok has already what’s needed: nohup python3 server.py. With that the server will run even when you log out or, your ssh connection disappears.

Next steps

I’d like to end this post also by saying: The world is your lobster. Try out the flashing lights on Azure Alerts, or why not to replace Azure Alerts with Exoprise Alarms, or some triggers in Power Automate, perhaps, when a new site has popped up 🙂 Or maybe you want to elaborate the flashing behaviour, why not to use Morse code to send a message? Or maybe color-code the different types of alarms/alerts. Once again, the world is your lobster 🦞(or oyster 🦪, well whatever) .

Automatically detect new sites in SharePoint Online

Original image by William Warby. https://www.flickr.com/photos/wwarby/16414155179/in/photostream/

Sites in SharePoint are created all the time, not only for SharePoint, but also as storage for Yammer, Teams, Planner and other services in Microsoft 365. There are ways to keep track of them, but the ability to automatically detect a new site creation is quite appealing. Automatic detection means a trigger of a Power Automate (Flow) or a Logic App.

There are a few blog posts that exactly describe how you can detect when a new site is created in SharePoint Online:

The provided blog posts are great how-tos, I am not giving you a new how-to for that, I’d like to reason about that solution.

The solution for automatic detection of new sites

Power Automate and Logic Apps can listen to new items in SharePoint. There is a list in the admin site (tenant-admin.sharepoint.com) that has SharePoint Sites as list items, its name is DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGATED_SITECOLLECTIONS.

That’s it, in essence, it’s just setting up a new flow with “When an item is created in SharePoint” as a trigger, and you have thousands business scenarios you could implement, but let’s dig a little bit deeper.

One List to rule them all

Honestly, I was not aware of that list before I started looking at that. What is that list, why is it called DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGATED_SITECOLLECTIONS.

The name is hillarious. Why name something to “DO_NOT_DELETE…” and all capslock🤣. But I suppose, there were support cases.

Beware, that list is not documented, that means you’re on your own when Microsoft changes the name or moves the list to somewhere else. So don’t build business critical solutions with that.

From what I can see, that list keeps information about all sites (site collections) in SharePoint Online, even those that are deleted and permanently deleted (?). This might be a source for deeper troubleshooting in some scenarios. It is like an old card index in a library you might have seen long time ago. It is hidden nowadays, but it is still there.

Image by LisaJasminAdams from Pixabay

First, that list is in the SharePoint Admin Site Collection, you need to be at least a SharePoint Administrator to access it. Okay, I’d like to know what’s more in its Site Contents (_layouts/15/viewlsts.aspx):

Well, the UI of that page has not been focused on, but nevermind, the lists are there. But you cannot navigate to that list in the browser directly:

It doesn’t matter since we can use it as a trigger but also the SharePoint REST API to get the items, e.g.:

https://tenant-admin.sharepoint.com/_api/Web/Lists/GetByTitle('DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS')/Items

You can see more examples of listing the sites in the linked posts. Unfortunately I bumped into an issue when trying to filter the results. If that list contains more than 5000 items (and it will, soon or later), you’ll have to deal with the ListView Threshold.

If you filter on Modified, you won’t able to anything because of the ListView Threshold, but filtering on Created will work.

But this is a side note, this post is automatically detecting new sites, not listing them

Alternative solutions

Using this kind of a hidden list mentioned above is a bit of a hack. I’d say it’s okay as long as it works, and it serves an complementary function, e.g. notifying IT about new sites, and the work is backed up by documented and reliable alternatives:

SharePoint Online Admin

Visiting “Active Sites” in SharePoint Online Admin gives you all the sites, you can sort by Created and see all the new sites. You cannot set up an alert or a flow directly from that, but maybe there will be some built-in functionality for that.

Office 365 Usage Reports

You can get all the sites in an Usage Report, their created, size, last activity etc. It’s not real time, but if you’re fine with 1-2 days delay, you can get this report, extract the new ones and do whatever you wanted to do in your original scenario/need.

SharePoint PowerShell Module

It’s worth mentioning, too, although it’s “heavy”. In a tenant with many sites, the scripts for getting all the sites and connected groups may take hours. I am refering to those scripts that start with Connect-SPOService.

Permissions, Licenses, and Security

The SharePoint connection that listens to the DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGATED_SITECOLLECTIONS list in the Admin Site Collection needs to be set up with a SharePoint Administrator role account. Beware of who has access to that solution (Power Automate or Logic App), this SPO Admin connection in wrong hands can be disastrous. Especially in Azure, pay attention to who has access to the resource, but also to the resource group and the azure subscription.

The account who sets up a Power Automate needs obviously an appropriate license and also Power Automate activated. In my scenario, I don’t need any premium connectors, but depending on your solution, you might need to license your account appopriately.

Scenarios

In “my” scenario, I want to be notified of all new sites in my business unit within a shared tenant, so that we can contact the site owners, provide guidance and also provision important parts (initial folder structure, some spfx solutions etc).

What is your sceanario?

The code

When I am done developing my proof-of-concept, I’ll try to share more details on the actual implementation. It might be an idea to submit the template to the Microsoft Power Community, but I am not sure it will be accepted, given the fact that it uses undocumented and hidden parts of SharePoint Online that soon or later will be subject to change.

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

paul.tavares

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

SPJoel

SharePoint for everyone

SharePointRyan

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

blksthl

Mostly what I know and share about...