Tag Archives: SharePoint Online

Track Service Principals in Microsoft 365

Scenario

Developers in the organization use both – Azure Apps and SharePoint Apps to work with SharePoint sites in their “daemon” applications. You want to know – what are SharePoint Apps registered, who register SharePoint Apps.

One of the approaches – track Apps/Owners with Unified Audit Log

Use Unified Audit Logs

The following PowerShell code:

$operations = 'Add service principal.'
$recordType = 'AzureActiveDirectory'
Search-UnifiedAuditLog -StartDate $start -EndDate $end -ResultSize $resultSize -Formatted -Operations $operations -RecordType $recordType

returns events with operation = ‘Add service principal.’ Nice, but…
if an app was registered in Azure – event will contain user UPN under UserIds property:

Unfortunately, in case with registering app in SharePoint, an audit log event will be like:

i.e. UserId registerd is “spo_service@support.onmicrosoft.com”, so we do not know who registered a SharePoint-only app

I’m wondering – can we use events recorded immediately before and after “Add service principal” event to track a user who has registered a SharePoint-only app…

References

Find sites shared with Everyone in SPO

There is a know problem in SharePoint – it’s complicated permissions system. As a result, many sites are overshared (overexposed) and site owners/administrators even do not know – who has access to their sites…

The most concern is sites shared with “Everyone”, “Everyone except external users” and “All users”. How do we find sites shared with “Everyone” in a large Microsoft 365 tenant?

Approach #1 (Brute force)

We can get full permissions report at tenant level (or permissions provided to “Everyone”). There are 3-rd party tools (e.g. ShareGate, SysKit, AvePoint, Metalogix etc.), or you can run PowerShell script…

Sounds easy? Well, if you have 1000 sites – probably yes. But if your environment 10K+ sites – it will take forever. Permission report might run hours for an average site with site/subsite, list/library and list item details level.

We need report detailed up to every item level deep, as even one file with sensitive info shared with everyone can cause security issue.

So, if this approach is not working – what’s working?

Approach #2 (Search)

Clever idea: why do we have to iterate through all the tenant documents/items if all the content is already crawled by search? Can we just use search to get files shared with Everyone? Sure!

Check this and this articles. Can we get results programmatically (e.g. with PowerShell)? Can we use Microsoft Graph search API? Sure.
Check this article “How to search against SharePoint Online Content with Microsoft Graph search API with PowerShell”.

But! We have two problems here.

Search Problem #1. The problem is the same as in “brute force”. Search returns so many results – it’ll take weeks to get all of them. (There are team sites “legally” shared with everyone, public Office 365 group based sites, communication sites… ).

Search Problem #2. Even whet we get all search results – we do not know – what is the exact Url of the resource shared with all users. So we will need to build list of sites based on the search results – ant then still need to run permissions report against these sites.

Approach # 3 Hybrid

The idea: why do we need to get all search result if even one result from the site would be enough to add the site to the list of sites require permission report.

So, consider (imho, the best) approach.

  1. You get list of sites in tenant. Here you can refine the list excluding, e.g. sites connected to public teams or known communication sites… Finally wou’ll have a list of sites you want to check – if there are resources shared with “Everyone…”
  2. You run search against each site in the loop (e.g. consider KQL option “Site: https://yourTenant,SharePoint.com/sites/YourSite”. Once even one result fount for the site – add the site to the “Open Sites” list

With this approach you will get list of sites shared with “Everyone…” in a coule of minutes.

The Next step would be “How to let site owners know what are resources shared with Everyone… on their sites”.

References

SharePoint sites shared with Everyone and Microsoft Delve issue

There is a known problem with Microsoft Delve.

We know SharePoint site permissions are not easy. You can break permissions inheritance at any level – subsite, library, list, folder, list item or specific document. Anybody with full permissions can do that. The worst thing is there is was (*1) no one place where site owner could get full permissions report to the site. We must have used third-party tools or PowerShell to have all permissions in one document.

So no wonder SharePoint sites were heavily over-exposed. Especially when a site owner tired with complexity of SharePoint permissions system decided to share resource with “Everyone”. That is the real issue.

Now, what happens when sites are migrated to Microsoft 365 SharePoint Online with Microsoft Delve enabled by default? Delve works as it should work – it suggests to you documents it believes related to you (based on Microsoft Graph insights) and you already have access to.

What happens is people start seeing documents they never new they have access to. Where these documents from? Of course from sites shared with Everyone.

So strictly says, it is not Delve’s problem. It’s more human problem than technological.
Delve just does it’s job, and does perfectly.

How do we solve the issue?

  1. Disable Delve?
  2. Disable search (stop sites crawling and remove results)?
  3. Restrict access to Microsoft Graph ?
    e.g. Microsoft KBA on how to disable MS Graph for a specific User

Those methods are half-measure. 1-2-3 methods are just hiding the problem – not solving it. Agree it helps stop the deterioration, bud does not fix the issue.

How do we solve the root cause of the issue?

  1. Of course, we need remove incorrectly provided permissions. How?
  2. Only site owner (data owner) knows which content should be shared with whom with which access rights. So we need to ask sites owners to review their permissions. How?
  3. First, we need a list of over-exposed sites. How? There are two methods
    (more details – check this article)
    • Brute force – use PowerShell or 3-rd party tool to get permission report on all sites in tenant, select permissions provided for Everyone…
    • Smart move – use Microsoft search. As search is security-trimmed, we can search for available content on behalf of a user with no permissions provided.
  4. Then we need a list of sites and their owners. How?
    1. tbp
  5. Finally, we need to let every site owner know that his site is Open to everybody and ask to fix it. How?
    1. tbp
    2. inform the site owner how to get full permissions report to his site,
      e.g. KBA How To Get SPO Site Full Permissions Report
      and video “Full Permissions Report for a Team SPO Site Owner


References

Connect-PnPOnline with a certificate stored in Azure Key Vault

Scenario

You need to run some PnP PowerShell code unattended (daemon app, with no user interaction) against SharePoint and/or Azure AD. PnP require authentication with a certificate. You want certificate stored securely in Azure Key Vault.

Solution

  • create a self-signed certificate
  • register an application in Azure
  • add API application permissions to the app
  • upload the certificate to the app
  • create an Azure Key Vault
  • provide permissions to the Key Vault for the user
  • run Connect-AzAccount
  • upload certificate to the Key Vault manually (with GUI)

now you can run this code and it will not ask you to login:

# set parameters:
$orgName = "orgname" 
$clientID = "" # Client ID
$VaultName = "" # Azure Key Vault Name
$certName = "" # Certificate Name as in Azure Key Vault
$tenant = "$orgName.onmicrosoft.com"
$adminUrl = "https://$orgName-admin.sharepoint.com"
# run the following
$secretSecureString = Get-AzKeyVaultSecret -VaultName $vaultName -Name $certName 
$secretPlainText = ConvertFrom-SecureString -AsPlainText -SecureString $secretSecureString.SecretValue
Connect-PnPOnline -Url $adminUrl -ClientId $clientID -CertificateBase64Encoded $secretPlainText -Tenant $tenant 

The same PowerShell code in GitHub: https://github.com/VladilenK/PowerShell/blob/main/PnP/Connect-PnPOnline-with-certificate.ps1

References:

https://docs.microsoft.com/en-us/powershell/module/az.keyvault/get-azkeyvaultcertificate?view=azps-5.3.0

https://stackoverflow.com/questions/43837362/keyvault-generated-certificate-with-exportable-private-key

SPO: Allow users to create modern pages

Microsoft: “Using modern pages in Microsoft SharePoint is a great way to share ideas using images, Office files, video, and more. Users can Add a page to a site quickly and easily, and modern pages look great on any device.
If you’re a global or SharePoint admin in Microsoft 365, you can allow or prevent users from creating modern pages. You can do this at the organization level by changing settings in the SharePoint admin center. If you allow the creation of site pages as the organization level, site owners can turn it on or off at the site level.

By default both
– Allow users to create new modern pages
– Allow commenting on modern pages
are turned on (enabled)

Tenant or SharePoint admin can find settings under
SharePoint Admin Center -> Settings -> Pages

How it looks like:

Site Pages are created under “Pages” Library.

Let us test it, with:
– (tenant-level) Allow users to create new modern pages: ON
– (tenant-level) Allow commenting on modern pages: ON
– web feature “Site Pages” – “Allows users to add new site pages to a site”: Activated

User
Permissions
can create Pagecan edit pagecan Enable/Disable
page comments
can comment on Page
Full Control (Owner)YesYesYesYes
Edit (Member)YesYesYesYes
Read (Visitor)NoNoNoYes

There is a web feature “Site Pages” – “Allows users to add new site pages to a site”.
The feature is activated by default:

What if we disable this feature?
“New -> Page” has disappeared from “New” menu under “Site Contents” for Owners and Members…
From “Home” and “Pages” you still can see “New -> Page” options.
You can still create a new page from but if you try to create a page from Pages – “Sorry, something went wrong” “Cannot create a Site Page. Please have your administrator enable the required feature on this site.” :

Office 365 behavior, with:
– (tenant-level) Allow users to create new modern pages: ON
– (tenant-level) Allow commenting on modern pages: ON
– web feature “Site Pages” – “Allows users to add new site pages to a site”: Deactivated

User
Permissions
can create Pagecan edit pagecan Enable/Disable
page comments
can comment on Page
Full Control (Owner)Yes,
but only from “Home”
not from “Site Contents” or “Pages”
YesYesYes
Edit (Member)Yes,
but only from “Home”
not from “Site Contents” or “Pages”
YesYesYes
Read (Visitor)NoNoNoYes


If we disable feature “Site Pages” – “Allows users to add new site pages to a site” on the root web – it does not affect subsites (subwebs).

Can we Activate/Deactivate the feature “Site Pages” using PowerShell?

PowerShell

(TBP)

References
– Microsoft “Allow users to create and comment modern pages

See also:
Allow commenting on modern pages

Office 365 Search scopes

Search is everywhere in Microsoft 365. You can search from SharePoint, Teams, Delve, Yammer etc.

But! From SharePoint you cannot search for Teams chats (*1).
From Teams you cannot search for regular (no-group) sites.
Sites descriptions are totally out of search (including Yammer groups, Teams and regular sites).

So, what are the scopes of each search entry point in Office 365 and is there an entry point you can search for everything?

Search scopesSharePoint
Search center
SharePoint home
Office portal
Office desktop app
Delve
TeamsBing
SharePoint contentYesYesYes
Teams contentYesYesYesYes
Teams chats(*1)YesYes
Yammer contentYesYesYes
Yammer chat(*1)Yes
User profilesYesYes
Email
(*1) Microsoft announced they are working on bringing conversations (both Teams chats and Yammer) to SharePoint landing page first, then to Office home page.

Detailed:

ScopeOut of Scope
SharePoint Search Center– all sites content
(Teams, Yammer, regular),
– user profiles
– OneDrive
Teams chat
Yammer chat
SharePoint Landing Pagesame as SharePoint Search center
but Teams chats and Yammer Conversations are coming
same as SharePoint Search Center
Office.comsame as SharePoint
(Teams chats and Yammer Conversations are coming after SharePoint)
same as SharePoint
Delve
TeamsTeams content
Teams chat
OneDrive
Yammer
User Profiles
regular SharePoint sites
BingEverything* * except people profiles content
(e.g. about me)

Seems like the only tool you can search for EVERYTHING with is Microsoft Bing:

After Microsoft add Teams chats and Yammer conversations to SharePoint landing page search scope (then to Office home page) – it’ll be the best place to search from for everything.

More on Microsoft Search vs SharePoint Search and Microsoft Search RoadMap

Microsoft Office 365 Search: Find what you need with Microsoft Search in Bing

It is possible customize Modern Microsoft Search pages with PnP Modern Search

SharePoint PnP roadmap

Good news!
On Sep, 18 during the SIG community call, PnP Team shared their plans on PnP Sites Core library and PnP Core SDK.
“PnP Sites Core v4” library and “PnP Core SDK v1” with .net core support (.net Standard 2.0) – expected in December 2020!

PnP PowerShell v4 for SPO library built for .Net Standard 2.0 / PowerShell 7 will be released in Dec 2020 as well.

Fastest way to delete all items in a large SPO list: PnP.PowerShell batches

Scenario: You have a large (>5k items) list in SharePoint Online.
You need to delete this list. “Remove-PnPList” fails with a message “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator“. Deleting with UI fails too.

Try this PowerShell command with ScriptBlock:

Get-PnPListItem -List $list -Fields "ID" -PageSize 100 -ScriptBlock { Param($items) $items | Sort-Object -Descending | ForEach-Object{ $_.DeleteObject() } } 

or this PowerShell with batches:

$batch = New-PnPBatch
1..12000 | Foreach-Object { Remove-PnPListItem -List $list -Identity $_ -Batch $batch }
Invoke-PnPBatch -Batch $batch

for me both methods gave same good result: ~17 items per second ( ~7 times faster than regular).

PnP.PowerShell batches

With a new PnP.PowerShell we can perform some operations against an SPO list with batches!
How fast PnP batches are? My measurements:

Time elapsed, secondswith batcheswith scriptBlockwithout batches
Add-PnPListItem (100 items)4.33 seconds42 seconds
Add-PnPListItem (500 items)21 seconds234 seconds
Add-PnPListItem (7000 items)314 seconds
Remove-PnPListItem (1000 items)58 seconds58 seconds429 seconds
Remove-PnPListItem (7000 items)395 seconds397 seconds

i.e. with batches your pnp.powershell code runs 7-10 times faster!

References:

How to create a Sub-Site if subsites creation is disabled

Microsoft recommend “flat structure”, i.e. no subsites. So SPO admins are disabling subsites creation at tenant level. Did you know that you still can create subsite? Let me explain how it is done.

If creation subsites is allowed, you should be able to see it like this:

But actually subsites are not always best practice. Microsoft recommend “flat structure”, i.e. instead of subsite you should have a separate site collection, and if you need a hierarchy and navigation – use Hub sites. So, in Office 365 SharePoint admins usually “disable” SubSites creation:

Now, you see, SubSites are not really disabled, but only the button to create subsites is hidden: “This controls whether the Subsite command appears on the New menu on the Site contents page”.

Anyway, the result is: you are not able to create a SubSite (web) in SharePoint Online:

Actually there are at least 3 options to create a SubSite:

Option 1. Create a SubSite in Classic mode.

Step 1: Select “Site Contents” page
Step 2: Click “Return to classic SharePoint”
Step 3: Create SubSite

Option 2. Create a SubSite from “Sites and Workspaces” page

Step 1: Go to “Site Settings”
Step 2: Select “Sites and Workspaces” page (site/_layouts/15/mngsubwebs.aspx)
Step 3: Create a SubSite

Option 3: use PowerShell PnP

Step 1: Install PowerShell PnP
Step 2: Connect to your site with PnP
Step 3: create a SubSite

Install-Module SharePointPnPPowerShellOnline
Connect-PnPOnline -Url <your site Url> -UseWebLogin
New-PnPWeb -Url "<new Web Url>" -Title "MySubSite" -Template "STS#3"

References:

See also: How to create a SharePoint Site in Office 365 if site creation is disabled

SPO: Description, Owner fields are not searchable

Scenario:

You have a list (or a document library) in SharePoint Online.
You can search through the list but some fields like “Description”, “Owner”, “AssignedTo”, “Created”, “CreatedBy” are not searchable.

Cause:

This is frustrating, but expected behavior. Check Microsoft’s “Overview of crawled and managed properties in SharePoint Server“. You see some pre-created managed properties do not have “Searchable” option enabled.

Solution:

(The following is still not finished:)

Prove:

I have created a new SPO site test78, a new list Test11 and created (not added from existing) a custom field “Description” to the list:

I also created “Description2” column the same way. No data is added to the list so far.

Search schema looks like:

for Description managed property:

mapping:

Notice that “Description” managed property is not searchable and “ows_Description” crawled property is mapped to “Description” managed property.

Searching for “ows_Description” crawled property gives me:

and that’s OK, as we have no data in the list so “ows_Description2” crawled property does not exist.

Now let me add some data to the list:

and wait a few minutes while continues crawl grabs data.

You can see:

Title and Description2 are searchable, but we are not able to search through “Description” field content.

Explanation

Actually this is by design.
Microsoft: “The index only includes content and metadata from the managed properties”.
(? Maybe Microsoft tries to protect their resources from overloading or) maybe they protect us from irrelevant results, but by default list column “Description” is mapped to non-searchable managed property.

“Searchable” means: “…The content of this managed property is included in the full-text index.” I.e. if the property is not searchable – “The content of this managed property is not included in the full-text index.” => that’s by design.

But the property is queryable!
Queryable “Enables querying against the specific managed property”. E.e.
“Description:Descr1*” query should work. And it works:

“Description2:Descr*” query should not work as we did not map Description2 property to any managed properties, so we can find content via full-text search but cannot find under managed property:

Solution

Option #1.
Use queries like “Description:TextToSearch” (check also SharePoint KQL).

Option #2.
Do not use name “Description”.
Choose something else like “Short Description” or “Case Description”

Option #3.
Use existing site column “Description” from group:Custom Columns. It’s “single line of text” though. Note: “SharePoint Server Standard Site Collection features” must be activated.

The thing is it’s internal name is “CategoryDescription” and display name is “Description”. So if you add this column to the list – the content will be searchable:

Option #4
Create a new site column, name it e.g. “DescriptionSrchblClmn”.
Add this column to the list from existing site columns.
Rename it to “Description”.

Option #5 (under testing… TBU)
Change default mapping.
e.g. unmap “ows_Description” crawled property from “Description” managed property. This should be enough, as “ows_Description” crawled property has “Include in full-text index” option ON:

NB: if you unmap “ows_Description” crawled property from “Description” managed property, it’ll affect other site lists.

Option #6.
In addition to option #5 you can create your own managed property (e.g. “DescriptionSearchable”), make it searchable and map it to “ows_Description” crawled property.

Remember: if you made a change in search schema, run “Reindex site” under Site Settings -> “Search and Offline Availability”. It’s like on-prem “Full crawl” but works at web level.

================================

Links.

Microsoft: Manage the search schema in SharePoint
Microsoft: Keyword Query Language (KQL) syntax reference
Vladilen: Search for a crawled property name with wildcards
Microsoft: Overview of crawled and managed properties in SharePoint Server