Content inventory scripts are essential for me, my teammates, and my clients. Often though, I find myself re-writing scripts because Client A wants these fields, and Client B wants those fields. To save me the headache of writing a new script every time, I wanted an inventory script that retrieves every field. I was tired of using CSOM scripts for inventory, so instead decided to try using PNP for inventory. Now instead of writing a new script every time, all I need to do is delete columns in Excel instead.
I’ve used PNP for provisioning, but never for inventory. I found that by using PNP the inventory script was much quicker to write and was much more powerful.
Here’s the script in its glory. It will work on SharePoint Online, 2013, and 2016. Before you can run it, you’ll need to install PNP. This blog walks through how to do that.
Param(
[parameter(Mandatory=$true)]
[string]$sitecollection
)
#update credentials based on your system. If in doubt, remove credentials param
Connect-PnPOnline -Url $sitecollection -Credentials 'outlook.office365.com'
#create variables for output file
$date = get-date -format yyyy-MM-dd
$time = get-date -format HH-mm-ss
$outputfilename = "SiteInventory" + $date + "_" + $time + ".csv"
$outputpath = "c:\scripts\"+$outputfilename
Write-Host "Connected to SharePoint site:" $sitecollection -ForegroundColor Green
function InventorySite($web){
#Create array variables to store data
$fieldvalues = $null
$fieldvalues = @()
$array = $null
$array = @()
#additional vars
$siteurl = $site.url
$weburl = $web.url
write-host "Performing inventory on " $weburl -foregroundcolor Yellow
#is this the root or not. If not, get lists
if($weburl -eq $siteUrl){
$lists = get-pnplist
}
else {
$lists = get-pnplist -web $web
}
#go through every list
foreach ($list in $lists)
{
write-host "List:" $list.title -foregroundcolor White
#get all items
#if this is the root, don't use web property. if subsite, use web
if($weburl -eq $siteUrl){
$items = get-pnplistitem -list $list -PageSize 1000
}
else {
$items = get-pnplistitem -list $list -Web $web -PageSize 1000
}
#go through every item
foreach ($item in $items)
{
$obj = New-Object PSObject
$fieldvalues = $item.FieldValues
foreach ($field in $fieldvalues){
$keys = @()
$values = @()
#work on the keys aka field names
foreach ($key in $field.keys){
$keys += $key
}
#work on the field values
foreach ($value in $field.values){
#if lookup value, do this
if($value.lookupvalue -ne $null){
$values += $value.lookupvalue
}
#not a lookup
else{$values += $value}
}
#add values to obj array
For ($j = 0; $j -lt $keys.count;$j++)
{
$obj | Add-Member -MemberType noteproperty -Name ($keys[$j]).tostring() -Value $values[$j]
$array += $obj
}
#close field in fields
}
#close item in items
}
#close list in lists
}
$filter = $array | Get-Unique -AsString
$filter | Export-Csv $outputpath -noType -Encoding UTF8 -Append -Force
}
#load site collection, and lists at site collection
$site = Get-PnPSite -includes RootWeb.Lists
#call inventory function
InventorySite $site
#load subsites
$subwebs = Get-PnPSubWebs -Recurse
#call inventory function for each subsite
foreach ($web in $subwebs){
InventorySite $web
}
write-host "Script complete! Exported file to " $outputpath -foregroundcolor green
#references
#https://veenstra.me.uk/2017/11/03/microsoft-teams-get-all-your-team-sites-using-pnp-powershell/
#https://blog.kloud.com.au/2018/02/01/quick-start-guide-for-pnp-powershell/
#https://github.com/SharePoint/PnP-PowerShell/blob/master/Documentation/readme.md
The script was originally written to run on a site collection. However, it bombs on large lists, and if there are a lot of subsites (on get-pnplistitem and get-pnpsubwebs -recurse respectively).
To get around this and run an inventory on very large site collections, in PowerShell I ran the following.
Connect-pnponline -site https://sitecollection $subsites = Get-pnpsubwebs $subssites | export-csv c:\scripts\subsites.csv
This gets all of the first-level subsites, and outputs them to a CSV file.
I then used a concatenate function to build the syntax to run the script so it looks something this this:
.\pnp.ps1 -sitecollection https://tenant.sharepoint.com/sites/projects/subsitenumberone
Then I took the value of the calculated column and pasted it into PowerShell and let it run. It’s a poor-man’s batch job, but it is effective.
If you choose to go this route, make sure you update the -Credentials parameter on Connect-PNPOnline to represent a credential on your system.
*UPDATE – 4/18/18* – I’ve explored the issues I ran into above. I’ve fixed the large list query by adding a page size. Second, I identified the issue with getting a lot of subsites. This is only an issue when you have provider hosted apps (i.e. Nintex). These can be filtered out in the script, but I’ll create a new post when I solve that. I’ve also logged this as a bug.