Thursday, February 11, 2016

How To Inventory Site Collections in SharePoint Online Using PowerShell and CSOM

Most of the SharePoint projects that Rightpoint works on, involves conducting some sort of content inventory of our customers’ existing MOSS 2007 or SharePoint 2010 or SharePoint 2013 site collections. And we have used a very useful PowerShell script that traverses through the site collections, and exports the information such as Site Url, List Title, List Url, Item Url, Item Type, Author, Editor, Created by, Modified by, to a CSV (Comma Separated Values) file.

But with the mass movement underway to Office 365, site collections are now hosted in the Cloud. A recent project with a renowned engineering firm in the Midwest, required us to inventory their existing SharePoint Online site collections.

The PowerShell script that we had used with success in SharePoint On-Prem environments, had to be modified in order for it to work in SharePoint Online (Office 365) environments. Using PowerShell and CSOM (Client-Side Object Model), I was able to successfully inventory our customer’s SharePoint Online site collection. I used the .NET managed client object model in the PowerShell script.

You can do some pretty cool stuff with this amazing PowerShell and CSOM duo.

Here is some information on the script:

1.       The script takes 4 input parameters:

a.       SharePoint Online Site Collection Url

b.      SharePoint Online User name

c.       Password

d.      Absolute path to the CSV file that will store the site collection inventory info

2.       The following information is inventoried and stored in the CSV file:

a.       Site URL

b.      List Title

c.       List URL

d.      List Type (this columns contains IDs. The IDs correspond to Document Library, Survey, Site Pages, etc. This link has the List Type ID enumeration)

e.      Item Title

f.        Item URL

g.       Item Type

h.      Created By

i.         Created

j.        Modified By

k.       Modified

3.       To reduce the amount of unnecessary inventory, the script does not inventory the following SharePoint lists. But if you do want these lists to be inventoried, make sure you remove the lists from the PowerShell script, before you run it.

a.       User Information List

b.       Workflow History

c.       Images

d.       Site Assets

e.       Composed Looks

f.       Microfeed

g.       Workflow Tasks

h.       Access Requests

i.       Master Page Gallery

j.       Web Part Gallery

k.       Style Library

l.       List Template Library

4.       [Ordered] used in the script below to create the columns in a specific order is only supported in PowerShell v3 and above. If using an earlier version of PowerShell, make sure that [Ordered] is removed, before you run the script. As a result, columns in the spreadsheet will be randomly ordered, but can be rearranged manually in the CSV file.


Without further ado, here is the script that can inventory SharePoint Online Site Collections.






   Script to inventory all sites within a SharePoint Online (O365) site collection.

   If running this script on a machine that does not have SharePoint bits installed, you will need to install the latest SharePoint Server 2013 Client Components SDK on the machine.   

   It can be downloaded from here:     



   The URL of your SharePoint Online site collection


   SharePoint Online User with FULL CONTROL permissions


   User password


   The absolute folder path and file name where the site collection inventory file (*.csv) should be saved


   .\Get-SPOInventory.ps1 -SiteUrl -UserName [email protected] -Password [email protected]  -OutputFile C:\Rightpoint\SiteInventory.csv   














function Get-SPOSites{





        #Create array variable to store data

        $siteitems = $null

        $siteitems = @()


        #get all webs under root web

        $Webs = $RootWeb.Webs



        #loop through the webs

        ForEach ($sWeb in $Webs)


            Write-Host $sWeb.url

            $siteUrl = $sWeb.Url;

            #get all lists in web

            $AllLists = $sWeb.Lists



            #loop through all lists in web

            ForEach ($list in $AllLists){

             Write-Host List: $list.Title

              #get list title

              $listTitle = $list.Title;


              # Do not inventory the following lists -> User Information List, Workflow History, Images, Site Assets, Composed Looks, Microfeed, Workflow Tasks, Access Requests, Master Page Gallery, Web Part Gallery, Style Library, List Template Library

              # NOTE: Add to (or remove from) the list below, as needed.


              If($listTitle -ne 'User Information List' -and `

                 $listTitle -ne 'Workflow History' -and `

                 $listTitle -ne 'Images' -and `

                 $listTitle -ne 'Site Assets' -and `

                 $listTitle -ne 'Composed Looks' -and `

                 $listTitle -ne 'Microfeed' -and `

                 $listTitle -ne 'Workflow Tasks' -and `

                 $listTitle -ne 'Access Requests' -and `

                 $listTitle -ne 'Master Page Gallery' -and  `

                 $listTitle -ne 'Web Part Gallery' -and `

                 $listTitle -ne 'Style Library' -and `

                 $listTitle -ne 'List Template Library') {


                 #Create a CAML Query object

                 #You can pass an undefined CamlQuery object to return all items from the list, or use the ViewXml property to define a CAML query and return items that meet specific criteria -

                 #In this script an undefined CamlQuery object is passed, to get all list items 

                 $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery

                 $AllItems = $list.GetItems($camlQuery)



                 If($AllItems.Count -gt 0) {

                 ForEach ($item in $AllItems){


                          $listType = $list.BaseTemplate

                          $listUrl = $item["FileDirRef"]

                          #set item title based on the type of list

                          switch ($listType) 


                                101 { $itemTitle = $item["FileLeafRef"] }    #Document Library

                                103 { $itemTitle = $item["FileLeafRef"] }    #Links List

                                109 { $itemTitle = $item["FileLeafRef"] }    #Picture Library

                                119 { $itemTitle = $item["FileLeafRef"] }    #Site Pages

                                851 { $itemTitle = $item["FileLeafRef"] }    #Media

                                default { $itemTitle = $item["Title"] }


                           Write-Host Item Name: $itemTitle


                          #retrieve item values

                          $itemType = $item.FileSystemObjectType

                          $itemurl = $item["FileRef"]

                          $itemCreatedBy = $item["Author"].LookupValue

                          $itemCreated = $item["Created"]

                          $itemModifiedBy = $item["Editor"].LookupValue

                          $itemModified = $item["Modified"]

                          #store the item values

                          #earlier versions (v2) of PowerShell do not support [Ordered]. If so, remove [Ordered]. Columns will be randomly ordered, but can be rearranged manually in the CSV file

                          $props = [Ordered]@{'Site' = $siteUrl;

                                     'List Title' = $listTitle;

                                     'List URL' = $listUrl;

                                     'List Type' = $listType;

                                     'Item Title' = $itemTitle;

                                     'Item URL' = $itemUrl;

                                     'Item Type' = $itemType;

                                     'Created By' = $itemCreatedBy;

                                     'Created'= $itemCreated;

                                     'Modified By' = $itemModifiedBy;

                                     'Modified' = $itemModified} ;                      

                          #append the values to the existing array object 

                          $siteitemarray = New-Object -TypeName PSObject -Property $props ; $siteitems += $siteitemarray

                   } #end loop for all items in list

                 } #check if item count is > 0

            } #check if it is a 'do not inventory' list          


          } #end loop for all lists in site 


          Get-SPOSites -RootWeb $sWeb -Context $Context #recursive call          

        } #end loop for all sites in site collection     


    #Output site collection inventory to CSV 

    $siteitems | Export-Csv $OutputFile -Append  



    #reference the SharePoint Client DLLs

    Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll" | Out-Null

    Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" | Out-Null    



    #pass SharePoint Online credentials to get ClientContext object

    $securePassword = ConvertTo-SecureString $PassWord -AsPlainText -Force

    $spoCred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $securePassword)

    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)

    $ctx.Credentials = $spoCred

    $Web = $ctx.Web



    #call the function that does the inventory of the site collection

    Get-SPOSites -RootWeb $Web -Context $ctx

	#Inventory complete

    Write-Host Site Collection Inventory Completed! -foregroundcolor yellow ;