Tag Archives: SharePoint Online

Get list of new SPO sites with PowerShell

Scenario

Let say you administer Microsoft 365 SharePoint Online and you want to get a list of new SharePoint sites (e.g. sites created during last week/month).

With GUI it’s done easily: SharePoint Admin Center -> Active Sites -> sort based on “Date Created” – done.

With PowerShell – not so simple.
“Get-PnPTenantSite” cmdlet returns site objects but the object does not have “Created” field. You have to connect separately to each site and get root web object where you can check when the web was created. For small environments it is possible, for large environments it can take days… And still not nice.
-Filter option would help, but “…Currently, you can filter by these properties: Owner, Template, LockState, Url.”

Get-SPOSite – similar experience.

Solution

Microsoft Graph API helps. It returns result in seconds. There are some pros and cons for each method though.

Option #1: Microsoft Graph Search API.

Entry point: https://graph.microsoft.com/v1.0/search/query

Microsoft Graph Search API allows KQL in queries. So we can form a query with something like “created>=1/1/2021” and use entity type = ‘[“site”]’. Search should return only sites created after Jan 01, 2021.

Check PowerShell script sample here: Get-NewSites.ps1
https://github.com/VladilenK/PowerShell/blob/main/reports/SharePoint/Get-NewSites.ps1

If you are getting more than 500 results – think of paging.

Option #2: Microsoft Graph Sites API

Entry point: https://graph.microsoft.com/v1.0/sites

This option is also based on Microsoft Graph API, but sites entry point, which allows search too and sort results by property “createdDateTime”. So we will just search for everything and select how many results we need based on createdDateTime property.

Check PowerShell script sample here: Get-NewSites.ps1
https://github.com/VladilenK/PowerShell/blob/main/reports/SharePoint/Get-NewSites.ps1

References

Retrieve programmatically SharePoint Online system page html content

How can I get HTML content of a SharePoint online page from code, e.g. PowerShell?

Invoke-WebRequest returns “Sign in to your account” page, not a real page, even with -Token option.

Thanks to Denis Molodtsov, the solution is found. It turns out the “Invoke-PnPSPRestMethod” PnP cmdlet works not only against /api endpoints, but also against site pages and system pages.

But (as per my experience) it works only with PnP.PowerShell and with -UseWebLogin authentication option and with -raw parameter.

Connect-PnPOnline -url $siteUrl -UseWebLogin
Invoke-PnPSPRestMethod -url /_layouts/15/viewlsts.aspx -Raw

Other combination of authentication options ( -interactive, -clientId, -Token, -SPOManagementShell, -PnPManagementShell ) – worked well, but only for /_api endpoints, and gave me “401 UNAUTHORIZED” against system/site pages.
Unattended authentication (with clientId, clientSecret and certificate) – same.

Legacy PnP module SharePointPnPPowerShellOnline did not work at all: “EXCEPTION,PnP.PowerShell.Commands.Admin.InvokeSPRestMethod”.

I tested it with
– SharePointPnPPowerShellOnline v 3.29.2101.0 (under Windows PowerShell 5.1) and
– PnP.PowerShell 1.8.0. (both Windows PowerShell 5.1 and .net core PowerShell 7.1.5)

PowerShell Script to Fetch All Alerts from SharePoint Online Site

PowerShell Script to get All Alerts of all Users from a specific SharePoint Online Site Collection, including subsites:

https://github.com/VladilenK/PowerShell/blob/main/reports/Site/Fetch-All-Alerts-from-SPO-Site.ps1

https://raw.githubusercontent.com/VladilenK/PowerShell/main/reports/Site/Fetch-All-Alerts-from-SPO-Site.ps1

based on Salaudeen Rajack:
SharePoint Online: Get All Alerts from a Site Collection using PowerShell

Connect to SharePoint Online with PnP.PowerShell Interactively with Client App and msal token

Scenario

You use PnP.PowerShell and you need to connect to SharePoint Online via Connect-PnPOnline interactively (on behalf of a user).

Solution

  • register an Azure App (single tenant)
  • configure authentication blade:
    – add platform – “Mobile and Desktop app”
    select “https://login.microsoftonline.com/common/oauth2/nativeclient”
    add custom Redirect URI: “http://localhost”
  • configure API permissions blade:
    – add delegated permissions you need (refer to specific API you’ll use)
    e.g. Microsoft Graph Sites.FullControl.All and SharePoint AllSites.FullControl
  • use the following code to connect to your site:
$siteUrl = "https://contoso.sharepoint.com/teams/myTeamsSite"
$appId = "" # Client Id
$connection = Connect-PnPOnline -ClientId $appId -Url $adminUrl -Interactive -ReturnConnection # -ForceAuthentication
$connection

A pop-up window will appear to authenticate interactively. If you are already authenticated with another credentials (or single-sigh-on) – an interactive window might pop up and disappear – so you are not able to enter your admin id.
To ensure Connect-PnPOnline prompts you for your credentials – use ” -ForceAuthentication” option.

If you are a SharePoint tenant admin – you can connect to a tenant with:

$orgName = "yourTenantPrefix" 
$adminUrl = "https://$orgName-admin.sharepoint.com" 
$appId = "" # Client Id 
$connection = Connect-PnPOnline -ClientId $appId -Url $adminUrl -Interactive -ReturnConnection # -ForceAuthentication 
$connection 

The other option is to use MSAL.PS module to get an msal token. This might help with Microsoft graph-based requests:

$tenantId = ""
$clientid = ""
$url = ""
$token = Get-MsalToken -ClientId $clientid -TenantId $tenantId -Interactive
Connect-PnPOnline -AccessToken $token -Url $url 

By default token expires in ~ 1 hour. But you can refresh it silently.
This helps if you run heavy PowerShell script and it takes hours to complete.
So you can include something like this in the loop:

if ($token.ExpiresOn.LocalDateTime -lt $(get-date).AddMinutes(10)) {    
  $token = Get-MsalToken -ClientId $clientid -TenantId $tenantId -ForceRefresh -Silent    
  Write-Host "Token will expire on:" $token.ExpiresOn.LocalDateTime
}

NB: For delegated permissions, the effective permissions of your app are the intersection of the delegated permissions the app has been granted (via consent) and the privileges of the currently signed-in user. Your app can never have more privileges than the signed-in user.

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 (over-exposed) 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 it will work. 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. So the approach will not work for large enterprise environments.

We cannot limit report with root web only – 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 need 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!

The idea is to use some dumb/test user account with no specific permissions provided and no group membership and try to search content on behalf of the user. Results we get are obviously from sites shared with everyone.

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 if 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 a site would be enough to add the site to the list of sites require permission review.

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… Using sensitivity labels you can start with high-sensitive sites.
    Finally you’ll have a list of sites you want to check – if there are resources on this site 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 at least something found in the site – add the site to the “Open Sites” list

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

NB: consider there are resources like “Styles Library” shared with everyone by default.

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

References

Access SPO Site Programmatically via MS Graph API and SharePoint API

Scenario

You are a software developer. Your company uses Microsoft Office 365 (SharePoint, Teams etc.). The need is to work with a specific site collection programmatically (from code – Python, C#, Java, PowerShell, JavaScript etc.) – e.g. upload/download documents, update list items, search etc.

The code must run without user interaction (unattended, aka daemon app). Sometimes this is also called “SharePoint Automation”.

The solution is based on a new Graph API feature – Sites.Selected and a classic SP-Only app.

Solution

  1. Register an Azure App and configure it:
    MS Graph API permissions: add -> Microsoft Graph -> Applications Permissions -> “sites.selected
  2. Ask SharePoint/Tenant admin run PowerShell code (e.g. this one) to assign proper permissions to your azure app for a specific site collection (consider site owner consent)
  3. Provide SharePoint API permissions:
    (require Site Collection Owner/Admin account) – use
    https://YourTenant.sharepoint.com/teams/YourSite/_layouts/15/appinv.aspx
    to add SharePoint API permissions to your app. E.g. full control permissions to site collection would be
<AppPermissionRequests AllowAppOnlyPolicy="true">  
   <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" 
    Right="FullControl" />
</AppPermissionRequests>

Consider minimal permissions (e.g. as per Sumit)

Problem Solved

  • you get access to one and only one site collection (“least privilege” principal)
  • you get both – SharePoint API and Microsoft Graph API permissions to SharePoint
  • you can use app secret or certificate to authenticate – depending on what are your security requirements

Note: if your scenario require authenticated user present – the solution would be a little different: Connect-PnPOnline Interactive with Client App Id

References:

SharePoint sites shared with Everyone and Microsoft Delve issue

There is a known problem with Microsoft Delve. It’s not a technology problem though.

We know SharePoint site permissions are not easy to manage. E.g. 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 native ability for site owner to get full site permissions report. 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”. And the other person, not knowing site is shared with everyone, might save some sensitive data. That is the real issue.

Now, what is Delve? It’s a service that
– get signals from allover Office 365 – who did what etc.
– based on that, using AI and Office Graph, generates suggestions – “what others do”.
Of course, Delve is security-trimmed, i.e. it will neve suggest you a document you do not have access to. But some sites might be overshared. Delve works as it should work – it suggests you documents it believes related to you (based on Microsoft Graph insights) and you already have access to.

Now bad thing happens – people start seeing documents they never new they have access to. Where are these documents from? Of course from sites shared with Everyone. Who to blame for the security breach? Delve? Microsoft Graph? Microsoft 365 SharePoint Online?

Strictly says, it is not Delve’s problem. It’s more human problem than technological.
Delve just does it’s job, and does perfectly. Delve simply displays the information already shared widely.

How do we solve the issue?

  1. Disable Delve?
  2. Disable search (stop sites crawling and remove results)?
  3. Restrict users who can provide signals via item insights privacy?
    see Microsoft KBA on how to disable MS Graph for a specific User

Those methods are half-measure. Methods above are just hiding the problem – not solving it. Agree it helps stop the deterioration, bud does not fix the root cause.

How do we solve the real problem and what is the root cause?

  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?
  4. 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.
  5. Then we find owners for each wide-open site. How?
    • for group-based sites we get member of the “owners” group
    • for non-group based sites we get site collection administrators
  6. We would also sort sites by “is it supposed to be public?”. I.e. if the site was born as public – e.g. Public Team or Public Yammer community – or Communication site – maybe it’s less concern.
  7. It would be a good idea to bring DLP and/or automatic content sensitivity labelling, so we could start remediation from sites labelled as storing most sensitive data.
  8. Finally, we need to let site owner know that his site is Open to everybody and ask to fix it. How?


References

Bill Baer’s on search and “prevent sensitive files from being exposed in search”

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