Get-ListsWithXAmountOfItems.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
<#PSScriptInfo
.VERSION 4.0
.GUID 3233e372-a85d-4b25-b665-f677a50a8fab
.AUTHOR
 Maarten Peeters - SharePointFire - https://sharepointfire.com
.COMPANYNAME
 SharePointFire
.COPYRIGHT
.TAGS
 SharePoint, Lists, Count
.LICENSEURI
.PROJECTURI
.ICONURI
.EXTERNALMODULEDEPENDENCIES
 Microsoft.Online.SharePoint.PowerShell
 SharePointPnPPowerShellOnline
.RELEASENOTES
 Version 1.0: Original published version.
 Version 2.0: Updated the IF statement regarding the subWeb all items variable
 Version 3.0: Changed the new-file value to not include a value
 Version 4.0: Remove the URL header from the CSV
#>
 

<#
.SYNOPSIS
 Function to get all lists with an X amount of items in SharePoint Online
  
.DESCRIPTION
 This script will count all items in each list for each web you specify and return the lists with an X amount of items.
 This can be helpfull to identify all large lists in your environment.
 You only need to specify the start location, the amount of items to look for, a directly for the log file.
 The required modules will be installed if not available on your device directly from the PowerShell Gallery.
  
.PARAMETER web
 Enter the full path of the SharePoint Online web which you want to retrieve the lists.
 It will look also look through all the subwebs.
  
.PARAMETER amount
 Specify the threshold to look for lists and libraries with more than this amount of items.
  
.PARAMETER logPath
 Enter the full path to store a .csv file (; delimited) of all lists and libraries with more then X amount of items.
 For example: C:\Install
  
.PARAMETER MFA
 This will prompt for username and password and creates a context using MFA to authenticate.
 
 .PARAMETER ADFS
 This will prompt for username and password and creates a context using ADFS to authenticate.
  
.EXAMPLE
 Get-listsWithXAmountOfItems.ps1 -web "https://spfire.sharepoint.com" -amount 5000 -LogPath "C:\Install" -MFA
  
 .NOTES
 Version: 4.0
 Author: Maarten Peeters
 Creation Date: 25-01-2019
 Purpose/Change: Retrieving lists with X amount of items
#>


param(
    [Parameter(mandatory=$true)]
    [string] $web,
    [Parameter(mandatory=$true)]
    [int] $amount,
    [Parameter(mandatory=$true)]
    [string] $logPath,
    [Parameter(mandatory=$false)]
    [switch] $MFA,
    [Parameter(mandatory=$false)]
    [switch] $ADFS
)

function Update-Log
{
    param
    (
        [Parameter(Mandatory = $true)]
        $amount,
        [Parameter(Mandatory = $true)]
        $logPath
    )

    #First create the logfile
    try{
        $fileName = "Lists$((get-date).tostring('sshhMMddyyyy')).csv"
        $file = New-Item -Path $logPath -Name $fileName -ItemType "file"
        Add-Content -Path "$($logPath)/$($fileName)" -Value "Site;List;Items"

        #Next count all lists from the rootweb
        try{
            $rootWeb = Get-PnPWeb
            $lists = Get-PnPList -web $rootWeb

            write-host "Indexing $($rootWeb.title)" -foregroundcolor green
            foreach($list in $lists){
                $items = (Get-PnPListItem -List $list -Fields "GUID").FieldValues
                
                if($items.count -ge $amount){
                    Add-Content -Path "$($logPath)/$($fileName)" -Value "$($rootWeb.title);$($list.title);$($items.count)"
                }
            }
        }
        catch{
            write-host "Error retrieving information from the rootweb: $($_.Exception.Message)" -foregroundcolor red
        }

        #Next count all lists from all subwebs
        try{
            $subWebs = Get-PnPSubWebs -Recurse

            foreach($subWeb in $subWebs){
                write-host "Indexing $($subWeb.title)" -foregroundcolor green
                $lists = Get-PnPList -web $subWeb

                foreach($list in $lists){
                    $items = (Get-PnPListItem -List $list -web $subWeb -Fields "GUID" ).FieldValues

                    if($items.count -ge $amount){
                        Add-Content -Path "$($logPath)/$($fileName)" -Value "$($subWeb.title);$($list.title);$($items.count)"
                    }
                }
            }
        }
        catch{
            write-host "Error retrieving information from the rootweb: $($_.Exception.Message)" -foregroundcolor red
        }
    }
    catch{
        write-host "Error creating the logfile: $($_.Exception.Message)" -foregroundcolor red
    }
}

try{
    #Verify if all required modules have been installed and otherwise install it.
    try{
        #Microsoft Online SharePoint PowerShell
        $SharePointOnlineModule = Get-Module -ListAvailable "Microsoft.Online.SharePoint.PowerShell"
        if(!$SharePointOnlineModule){
            Install-Module "Microsoft.Online.SharePoint.PowerShell"
        }else{
            Import-Module "Microsoft.Online.SharePoint.PowerShell"
        }

        #SharePoint PNP PowerShell Online
        $SharePointPnPPowerShellOnline = Get-Module -ListAvailable "SharePointPnPPowerShellOnline"
        if(!$SharePointPnPPowerShellOnline){
            Install-Module "SharePointPnPPowerShellOnline"
        }else{
            Import-Module "SharePointPnPPowerShellOnline"
        }

        #Create an PnP SharePoint Online Contex
        try{
            if($MFA){
                $context = Connect-PnPOnline -Url $web -UseWebLogin -ReturnConnection
            }elseif($ADFS){
                $context = Connect-PnPOnline -Url $web -Credentials (Get-Credential) -UseAdfs
            }else{
                $context = Connect-PnPOnline -Url $web -Credentials (Get-Credential)
            }

            #Run the function which will retrieve all lists from all webs beneath the specified web
            try{
                Update-Log -amount $amount -logPath $logPath
            }
            catch{
                write-host "Error running Set-Log function: $($_.Exception.Message)" -foregroundcolor red
            }
        }
        catch{
            write-host "Error setting up context: $($_.Exception.Message)" -foregroundcolor red
        }
    }
    catch{
        write-host "Error occurred importing required modules: $($_.Exception.Message)" -foregroundcolor red
    }
}
catch{
    write-host "Error occurred: $($_.Exception.Message)" -foregroundcolor red
}