count files in a site, broken down by library
sometimes libraries have file counts that exceed the 5000-file limit. So other means fail. But this works.
First, download a getSPOobjectWithRestModule.psm1 module. Then import:
import-module C:\PowerShell\MSModules\getSPOobjectWithRestModule.psm1 -Verbose
Read in your password as you type it into a variable:
$Password = Read-Host -AsSecureString
Now, specify a Url and site or list (object) in the following command
Get-SPOObject -Username "someUser@yourDomain.com" -password $Password -Url "https://yourTenant.sharepoint.com/someSite" -Object "lists" | select title, itemcount
create site, see site, create
deleted sites, listsites, list deleted
You can only list 50 at a time and they're in alphabetical order. So, a "position" is like a screen full of results that you'd get in the GUI.
Get-SPOExternalUser -Position 0 -PageSize 50
For just one user:
Get-SPOExternalUser -Position 0 -PageSize 50 -Filter bob
Filter only seems to work on display name.
files, list all in a library beginning with a string
This finds all files (including subdirectories) beginning with "someSubString" in the "someLibrary" under the "someSubSite" sub-site.
$web
=
Get-PnPWeb
-Identity someSubSite
$query
=
@"
<View Scope='RecursiveAll'>
<Query>
<Where>
<BeginsWith>
<FieldRef
Name="FileLeafRef"/>" + "<Value
Type="Note">someSubString</Value>
</BeginsWith>
</Where>
</Query>
</View>
"@
$$Items
=
Get-PnPListItem
-List someLibrary -Web $web
-Query
$query
foreach($item
in
$items){
$item.FieldValues.FileLeafRef
}
groups, list all users with each users' groups
Note: Get-SPUser is a little misleading in that it returns all user or security group accounts. With a name like "Get-SPOUser", you'd think that it'd only get users and not also groups.
$SPusers = Get-SPOUser -site https://yourtenant.sharepoint.com -Limit All | ft DisplayName, Groups -AutoSize
This query takes a while, so I usually stuff the results into a variable so I can run other commands on it if necessary. Running:
$SPusers | ft
or, better yet:
$SPusers | ogv
You might see a bunch of "users" that look like
- c:0u.c|tenant|026297b403004915085f0ff42d0925d1783f5b34058ce868f2e99e6ad2db2283
- Guest Contributor
which can be:
- groups created by users sharing out files
- AAD Guest
groups, list with members
$siteURL = "https://yourtenant.sharepoint.com"
$SPgroups
=
Get-SPOSiteGroup -Site $siteURL
foreach ($SPgroup in $SPgroups)
{
Write-Host $SPgroup.Title -ForegroundColor "Yellow"
Get-SPOSiteGroup -Site $siteURL -Group $SPgroup.Title | Select-Object -ExpandProperty
Users
Write-Host
}
or
Get-SPOSiteGroup -Site https://YourTenant.sharepoint.com/ | select title, users | Export-Csv 'SharePointGroups.csv'
But the problem with the above is that users don't display right.
That is, the users
column ends up looking like
System.Collections.Generic.List`1[System.String]
.
So try this instead:
Get-SPOSiteGroup -Site https://YourTenant.sharepoint.com/ | select title, {% {"$($_.users)"}} | Export-Csv 'SharePointGroups2.csv'
or
Get-SPOSiteGroup -Site https://YourTenant.sharepoint.com/ | select title, @{e={$_.users};name='users'} | Export-Csv 'SharePointGroups3.csv'
It's useful to be able to find all groups with no members (anonymous shares). Since it takes so long to gather the groups, maybe put them in a variable first:
$SPgroups = Get-SPOSiteGroup -Site https://YourTenant.sharepoint.com/
Then find those with no members and which start with "SharingLinks."
$SPgroups | ?{($_.users.Count -eq 0) -and ($_.title -like "SharingLinks.*")} | select title
Get a count:
($SPgroups | ?{($_.users.Count -eq 0) -and ($_.title -like "SharingLinks.*")}).count #131
groups, which groups is a user a member of?
Get-SPOUser -site https://yourtenant.sharepoint.com -loginname someuser@yourdomain.com | fl Groups
guest users, list - see external users, list
inheritance broken for a list (or not) - see lists in a sites, what type and whether inherited permission or inheritance broken
list deleted sites, see sites, list deleted
- what type and
- whether inherited permission or inheritance broken
Quick way for just libraries with broken permission:
$web
=
Get-PnPWeb
-Identity someSubSite
$list
=
Get-PnPList
-web
$web
-Includes SchemaXml |
? {$_.SchemaXML
-match
"SharedWithDetails"}
more detail:
Function Invoke-LoadMethod() {
param(
[Microsoft.SharePoint.Client.ClientObject]$Object = $(throw "Please
provide a Client Object"),
[string]$PropertyName
)
$ctx = $Object.Context
$load = [Microsoft.SharePoint.Client.ClientContext].GetMethod("Load")
$type = $Object.GetType()
$clientLoad = $load.MakeGenericMethod($type)
$Parameter = [System.Linq.Expressions.Expression]::Parameter(($type), $type.Name)
$Expression = [System.Linq.Expressions.Expression]::Lambda(
[System.Linq.Expressions.Expression]::Convert(
[System.Linq.Expressions.Expression]::PropertyOrField($Parameter,$PropertyName),
[System.Object]
),
$($Parameter)
)
$ExpressionArray = [System.Array]::CreateInstance($Expression.GetType(), 1)
$ExpressionArray.SetValue($Expression, 0)
$clientLoad.Invoke($ctx,@($Object,$ExpressionArray))
}
# Initialize client context
$siteUrl = 'https://yourdomain.sharepoint.com/'
$username = 'admin@yourDomain.com'
$password = 'topSecret'
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object
Microsoft.SharePoint.Client.SharePointOnlineCredentials($username,$securePassword)
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$clientContext.Credentials = $credentials
$Web = $clientContext.Web;
$clientContext.Load($Web)
$clientContext.ExecuteQuery()
Write-Host $siteUrl -ForegroundColor Yellow;
$Lists = $Web.Lists
$clientContext.Load($Lists)
$clientContext.ExecuteQuery()
#Iterate through each list in a site
$ListCount = 0
$ListsCount = $lists.Count
ForEach($List in $Lists)
{
$ListCount++
#Get the List Name
$BaseInfo = "'$($List.Title)' (list $ListCount of $ListsCount), List type is"
Invoke-LoadMethod -Object $List -PropertyName "HasUniqueRoleAssignments"
$clientContext.ExecuteQuery()
if($List.HasUniqueRoleAssignments -eq $true)
{
$inherited = " broken"
$inheritedColor = "red"
}
else {
$inherited = " inherited"
$inheritedColor = "green"
}
if($List.BaseType -eq "GenericList")
{
$ListTypeColor = "Cyan"
}
else {
$ListTypeColor = "Magenta"
}
Write-host $BaseInfo -NoNewline; Write-host " $($List.BaseType)," -foreground $ListTypeColor -NoNewline; `
Write-Host $inherited -foreground $inheritedColor -NoNewline; Write-Host " permission"
}
There's a way to get around including that
Invoke-LoadMethod
function up top. Instead of
invoking
Invoke-LoadMethod -Object $List -PropertyName "HasUniqueRoleAssignments"
use this instead
Load-CSOMProperties -object $item -propertyNames @("HasUniqueRoleAssignments")
This acts as a "wrapper" to replicate what you would do with a
lambda expression in C#. But in order for this to work, you have to
download Load-CSOMProperties.ps1
and make it available in the same path or
specify a path.
PnPOnline - a module you can download here or here that helps generate list of subsites
The easiest way to get it to actually work:
Install-Module SharePointPnPPowerShellOnline
returns
Untrusted repository
You are installing the modules from an untrusted repository. If
you trust this repository, change its InstallationPolicy value by running the
Set-PSRepository cmdlet. Are you sure you want to install the modules from 'PSGallery'?
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help
(default is "No"): y
now that it's installed, connect to a site
(how to get $credentials
not covered here)
Connect-PnPOnline -Url https://yourTenant.sharepoint.com/someSite -Credentials $credentials
invoke it all by itself to list subsites:
Get-PnPSubWebs
returns:
Title ServerRelativeUrl Id
----- ----------------- --
Accounting /accounting d856f80a-d979-444d-b0ba-af64f492efe2
Finance /finance f3dad18f-0ab7-4d35-8d02-fdd4fe89683c
someOtherSite /someOtherSite ecb27c05-99ab-45b0-9bac-943624872d61
to get sub-subsites:
Get-PnPSubWebs -Recurse
retrieve only the sub-subsites for one subsite (i.e.: Subsite X)
Get-PnPSubWebs -Web "/subsiteX" -Recurse
versions availalble:
Get-Module SharePointPnPPowerShell* -ListAvailable | Select-Object Name,Version | Sort-Object Version -Descending
update:
Update-Module SharePointPnPPowerShell*
methods and properties
Get-PnPSubWebs | gm
view all cmdlets
Get-Command -Module *PnP*
This recursively finds files in a library newer than 4 days (green) or 7 days (yellow). Although probably not as useful, can also be tweaked to find folders.
$SiteUrl = "https://yourTenant.sharepoint.com/someSite"
$ListName
=
"someLibrary"
$UserName
=
"someUser@yourDomain.com"
$Password
=
"topSecret"
$SecurePassword
=
$Password
|
ConvertTo-SecureString
-AsPlainText -Force
$Creds
=
New-Object
Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$SecurePassword)
#Bind to Site Collection
$ctx
=
New-Object
Microsoft.SharePoint.Client.ClientContext($SiteURL)
$ctx.Credentials
=
$Creds
$list
=
$ctx.Web.Lists.GetByTitle($listName)
$query
=
New-Object
Microsoft.SharePoint.Client.CamlQuery
# Collaborative Application Markup Language (CAML)
# for files, set to 0; for folders, set to 1
$qCommand
=
@"
<View Scope="RecursiveAll">
<Query>
<Where>
<Eq>
<FieldRef Name="FSObjType"
/><Value Type="Integer">0</Value>
</Eq>
</Where>
</Query>
</View>
"@
$query.ViewXml
=
$qCommand
$items
=
$list.GetItems($query)
$ctx.Load($items)
$ctx.ExecuteQuery()
Write-host "Total Items Found:"$items.Count
foreach($item
in
$items)
{
if
($item["Modified"] -gt (get-date).AddDays(-4)) # last 4 days – can be adjusted
{ # ["FileLeafRef"] for just the name without the
directory
Write-Host -ForegroundColor green $item["FileRef"] - "Last Modified
Time: " $item["Modified"]
}
elseif ($item["Modified"] -gt (get-date).AddDays(-7)) # last 7 days
{
Write-Host
-ForegroundColor yellow
$item["FileRef"] -
"Last Modified Time: "
$item["Modified"]
}
}
This command seems to make a whole new site collection, not just a new site
New-SPOSite -Url https://yourtenant.sharepoint.com/sites/SomeNewSite -Owner someuser@yourtenant.onmicrosoft.com -Title " Some New Site" -StorageQuota 100
Assume you've already deleted the site but now you need to remove from the recycle bin
Remove-SPODeletedSite -Identity https://yourtenant.sharepoint.com/sites/SiteToBeRemoved
Get-SPOSite | Sort-Object Url
this doesn't necessarily list sites you'd expect - which are the subsites under any one of these main sites. I think this lists only top-level sites. And a lot of those seem like they're:
- personal sites
- sites created in Outlook
- sites created with Planner
Get-SPODeletedSite
see also PnPOnline - especially to recurse to sub-subsites.
sub-sites are known as "webs" in SharePoint parlance
$siteURL = "https://yourTenant.sharepoint.com"
$username = "someAdmin@yourDomain.com"
$password = "topSecret"
$credentials = New-Object
Microsoft.SharePoint.Client.SharePointOnlineCredentials ($username, $(convertto-securestring $Password -asplaintext
-force))
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$ctx.Credentials = $credentials
$Web = $ctx.Web
$sites = $Web.Webs
$ctx.Load($Web)
$ctx.Load($sites)
$ctx.ExecuteQuery()
"Site Name: $($Web.Title), Site count: $($Sites.Count)"
foreach ($site in $sites)
{
$ctx.Load($site)
$ctx.ExecuteQuery()
Write-Host $site.Url "-" $site.Title
}
users, list all users - see also external users, list
Get-SPOUser -site https://yourtenant.sharepoint.com -Limit All
this query takes a while, and it returns a bunch of users that look like
- c:0u.c|tenant|026297b403004915085f0ff42d0925d1783f5b34058ce868f2e99e6ad2db2283
- Guest Contributor
Because this command takes so long, if I need to work with this collection, I usually store into a variable:
$SPusers = Get-SPOUser -site https://yourtenant.sharepoint.com -Limit All | ft DisplayName, Groups -AutoSize
or
$SPusers = Get-SPOUser -site https://yourTenant.sharepoint.com -Limit All
followed by
$SPusers | ogv