Introduction

Hi guys, I want to share a cool SPO PowerShell script that can easily generate an Inventory Report with all the Lists/Libraries their respective Site Collections/Subsite collections and other related details.
Save the below PS script on your local Path. Run it from your Windows PowerShell ISE after making the below-highlighted changes.
SPO PowerShell Script
  1. #Add - PSSnapin Microsoft.SharePoint.PowerShell - ErrorAction SilentlyContinue
  2. #Load SharePoint CSOM Assemblies
  3. Add - Type - Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
  4. Add - Type - Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
  5. Add - Type - Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.Client.Tenant.dll"
  6. Import - Module Microsoft.Online.SharePoint.Powershell - DisableNameChecking
  7. #Import - Module‘ C: \Program Files\ SharePoint Online Management Shell\ Microsoft.Online.SharePoint.PowerShell’ - DisableNameChecking
  8. #Config Parameters
  9. $ReportOutput = "D:\WebJobs\SPOListInfo_$(get-date -f yyyy-MM-dd).csv"
  10. #Get All Site collections from the Tenant - Including Modern Team sites and communication sites
  11. Function Get - SPOSites($AdminSiteURL, $Cred) {
  12. #Setup credentials to connect
  13. $Credentials = New - Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
  14. #Array to store Result
  15. $ResultSet = @()
  16. #Setup the context
  17. $Ctx = New - Object Microsoft.SharePoint.Client.ClientContext($AdminSiteURL)
  18. $Ctx.Credentials = $Credentials
  19. #Get the tenant object
  20. $Tenant = New - Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($ctx)
  21. #Get All Site Collections
  22. $SiteCollections = $Tenant.GetSitePropertiesFromSharePoint(0, $true)
  23. $Ctx.Load($SiteCollections)
  24. $Ctx.ExecuteQuery()
  25. #Iterate through Each site collection
  26. ForEach($Site in $SiteCollections) {
  27. Write - Host "Processing Site Collection :"
  28. $Site.URL - f Yellow
  29. $ctx = New - Object Microsoft.SharePoint.Client.ClientContext($Site.Url)
  30. $Ctx.Credentials = $credentials
  31. $rootweb = $Ctx.Web
  32. $childWebs = $rootweb.Webs
  33. $Ctx.Load($rootweb)
  34. $Ctx.Load($childWebs)
  35. #Check the Feature Status
  36. $SiteFeatureId = "E3540C7D-6BEA-403C-A224-1A12EAFEE4C4"
  37. $FeatureStatus = $rootweb.Features.GetById($SiteFeatureId)
  38. $FeatureStatus.Retrieve("DefinitionId")
  39. $Ctx.Load($FeatureStatus)
  40. $Ctx.ExecuteQuery()
  41. $lists = $rootweb.Lists
  42. $Ctx.Load($lists)
  43. $Ctx.ExecuteQuery()
  44. foreach($list in $lists) {
  45. #Get site collection details
  46. $Result = new - object PSObject
  47. $Result | add - member - membertype NoteProperty - name "Title" - Value $Site.Title
  48. $Result | add - member - membertype NoteProperty - name "Url" - Value $Site.Url
  49. #$Result | add - member - membertype NoteProperty - name "SiteExperience" - Value $Site.Features["E3540C7D-6BEA-403C-A224-1A12EAFEE4C4"] # - eq $null) {
  50. "Classic"
  51. } else {
  52. "Modern"
  53. }
  54. if ($FeatureStatus.DefinitionId - ne $null) {
  55. $Result | add - member - membertype NoteProperty - name "Site Experience" - Value 'Modern'
  56. } else {
  57. $Result | add - member - membertype NoteProperty - name "Site Experience" - Value 'Classic'
  58. }
  59. $Result | add - member - membertype NoteProperty - name "List Title" - Value $list.Title
  60. $Result | add - member - membertype NoteProperty - name "List ItemCount" - Value $list.ItemCount
  61. $Result | add - member - membertype NoteProperty - name "List Experience" - Value $list.ListExperienceOptions
  62. $Result | add - member - membertype NoteProperty - name "List BaseTemplateID" - Value $list.BaseTemplate
  63. $Result | add - member - membertype NoteProperty - name "List Created" - Value $list.Created
  64. $Result | add - member - membertype NoteProperty - name "List BaseType" - Value $list.BaseType
  65. $Result | add - member - membertype NoteProperty - name "List Hidden" - Value $list.Hidden
  66. $Result | add - member - membertype NoteProperty - name "List Id" - Value $list.Id
  67. $Result | add - member - membertype NoteProperty - name "List LastItemModifiedDate" - Value $list.LastItemModifiedDate
  68. $Result | add - member - membertype NoteProperty - name "List ParentWebUrl" - Value $list.ParentWebUrl
  69. $Result | add - member - membertype NoteProperty - name "List Description" - Value $list.Description
  70. $ResultSet += $Result
  71. }
  72. #$ctx = New - Object Microsoft.SharePoint.Client.ClientContext($Site.Url)
  73. #$ctx.Credentials = $Credentials
  74. #$Web = $ctx.Web
  75. #$ctx.Load($web)
  76. # $ctx.Load($web.Webs)
  77. # $ctx.executeQuery()
  78. # $Site = $Ctx.Web
  79. # $Ctx.Load($Site)
  80. # $Ctx.Load($Site.Webs)
  81. # $Ctx.executeQuery()
  82. foreach($web in $childWebs) {
  83. #$web = $Ctx.Web
  84. Write - Host "Processing subsite :"
  85. $web.URL - f Green
  86. #Check the Feature Status
  87. $WebFeatureId = "52E14B6F-B1BB-4969-B89B-C4FAA56745EF"
  88. $FeatureStatus = $web.Features.GetById($WebFeatureId)
  89. $FeatureStatus.Retrieve("DefinitionId")
  90. $Ctx.Load($FeatureStatus)
  91. $Ctx.ExecuteQuery()
  92. $lists = $web.Lists
  93. $ctx.Load($lists)
  94. $ctx.ExecuteQuery()
  95. foreach($list in $lists) {
  96. #Get site collection details
  97. $Result = new - object PSObject
  98. $Result | add - member - membertype NoteProperty - name "Title" - Value $web.Title
  99. $Result | add - member - membertype NoteProperty - name "Url" - Value $web.Url
  100. #$Result | add - member - membertype NoteProperty - name "SiteExperience" - Value $web.Features["52E14B6F-B1BB-4969-B89B-C4FAA56745EF"] # - eq $null) {
  101. "Classic"
  102. } else {
  103. "Modern"
  104. }
  105. #if((Get - SPFeature - Identity {
  106. 52E14 B6F - B1BB - 4969 - B89B - C4FAA56745EF
  107. } - ErrorAction SilentlyContinue - Web) - ne $null) {
  108. if ($FeatureStatus.DefinitionId - ne $null) {
  109. $Result | add - member - membertype NoteProperty - name "Site Experience" - Value 'Modern'
  110. } else {
  111. $Result | add - member - membertype NoteProperty - name "Site Experience" - Value 'Classic'
  112. }
  113. $Result | add - member - membertype NoteProperty - name "List Title" - Value $list.Title
  114. $Result | add - member - membertype NoteProperty - name "List ItemCount" - Value $list.ItemCount
  115. $Result | add - member - membertype NoteProperty - name "List Experience" - Value $list.ListExperienceOptions
  116. $Result | add - member - membertype NoteProperty - name "List BaseTemplateID" - Value $list.BaseTemplate
  117. $Result | add - member - membertype NoteProperty - name "List Created" - Value $list.Created
  118. $Result | add - member - membertype NoteProperty - name "List BaseType" - Value $list.BaseType
  119. $Result | add - member - membertype NoteProperty - name "List Hidden" - Value $list.Hidden
  120. $Result | add - member - membertype NoteProperty - name "List Id" - Value $list.Id
  121. $Result | add - member - membertype NoteProperty - name "List LastItemModifiedDate" - Value $list.LastItemModifiedDate
  122. $Result | add - member - membertype NoteProperty - name "List ParentWebUrl" - Value $list.ParentWebUrl
  123. $Result | add - member - membertype NoteProperty - name "List Description" - Value $list.Description
  124. $ResultSet += $Result
  125. }
  126. }
  127. }
  128. #Export Result to csv file
  129. $ResultSet | Export - Csv $ReportOutput - notypeinformation
  130. }
  131. #Set Parameters
  132. $AdminSiteUrl = "https://abcdcorp-admin.sharepoint.com"
  133. $Cred = Get - Credential
  134. #sharepoint online powershell list all sites
  135. Get - SPOSites - AdminSiteURL $AdminSiteUrl - Cred $Cred
Just configure your own File Path and Tenant Admin URL as highlighted.
Once the CSV report is generated, save it as an Excel WorkBook type.
Sort the List Item Count from Descending to Ascending.
Classify all the Large Lists/Libraries[Items Count>5k] and paste them in a separate spreadsheet.
Classify all the Future Large Lists/Libraries[Items Count 3k-5k] and paste them in a separate spreadsheet that is prone to be future potential Large Lists/Libraries.
Handle the Large Lists/Libraries with some cool concepts like Automated Archival + Retention Labeling + Retention Policy using Power Automation Flows OR Azure Web Jobs OR Azure Functions.
Thus, we can use the above Inventory Report generation for various further Processes like Archival + Retention. Cheers!!!