Web Solutions/Intranets

Pull the count of items from a SPO List

In doing a relatively large migration I ran into an issue where I knew that I had migrated some content from the old environment to the new SPO environment and I knew that the size of the two was off, but how do I find out where I am missing information?

I turned to SQL server and Powershell to solve my issues.  First, I ran a query against the SQL databases to pull the list of sites and lists and counts of items.

This gives me a report that shows me the following:

So I have the old info, how do I get the same from SPO.  Here is where PowerShell comes to the rescue.  First we will load the assemblies and connect to the site.  We also will setup a Hast Table to store our results.

Note that I connected twice.  Once to SPO and once to the CSOM.  This is because I am going to use the Get-SPOSite cmdlet to get all of the site collections.

Next we get the sites and use CSOM to grab the Sites and List.  First we go through the lists of the Root site and add their info to the Hash Table.  Then we see if there are any subsite and recurse them to get their lists.

Here is the GetSubsiteLists Function

I haven’t cleaned this up, but I suspect that we could make this cleaner by doing the entire thing as a recursive function, but this worked.  😉

Lastly, I output the Hash Table to a CSV file that I can load into Excel and compare to the SQL query

 

Leave a Reply