I wrote a script that traverses all content databases and retrieves information about websites from each of them.
Total number of databases: 1200, Number of websites: about 39000
The number of websites in the databases is uneven. There are databases with 100 websites, while others have 10-20-30, etc.
At the very beginning, if a database has fewer websites, the script traverses it super quickly (less than a second), but towards the end, it takes around 3-5 seconds for each database with the same number of websites.
Why does this happen? Is it due to caching? Or did I make a mistake somewhere in the algorithm?
Add-PSSnapin "Microsoft.SharePoint.PowerShell"
Measure-Command {
$databases = Get-SPContentDatabase -WebApplication 'https://...'
$emptyDB = 0
$fullDB = 0
$totalSites = 0
foreach ($database in $databases) {
if ($database.Sites.Count -eq 0) {
Write-Host 'Database ' $database.Name ' is empty' -ForegroundColor Red
$emptyDB++
}
else {
Write-Host 'Database ' $database.Name ' has ' $database.Sites.Count ' sites' -ForegroundColor Green
$fullDB++
$sites = $database.Sites
$siteInfo = [System.Collections.ArrayList]::new()
foreach ($site in $sites) {
if ($site.Url.Contains("sitemaster")) {
Write-Host $site.Url "was excluded from export" -ForegroundColor Yellow
}
else {
$UserEmail = $site.Owner.Email
$siteUrl = $site.Url
if ($site.Owner.Email -ne '') {
$siteOwner = $site.Owner.Email
}
else {
$siteOwner = $site.Owner.DisplayName
}
$siteUsage = [math]::round($site.Usage.Storage / 1GB, 3)
$siteLastTimeUsage = $site.LastContentModifiedDate.ToString("yyyy-MM-dd")
$siteDatabases = $site.ContentDatabase.Name
$siteQuota = [math]::round($site.Quota.StorageMaximumLevel / 1GB, 3)
if ($siteQuota -ne 0) {
$QuotaUsage = [math]::round($siteUsage / $siteQuota * 100, 2)
}
$webTitle = $site.RootWeb.Title
if (($UserEmail -ne '') -and ($UserEmail -like '*ozon.ru')) {
$ozon = Get-AdUser -Server "ozon" -Filter { userPrincipalName -like $UserEmail } -Properties employeeType, manager
if ($ozon.manager -ne $null) {
$manager = Get-AdUser -Server "ozon" -Filter { distinguishedName -like $ozon.manager }
$manager = $manager.userPrincipalName
}
}
# Конвертируем дату увольнения в формат YYYY-MM-DD для загрузки в БД
if ($ozon.employeeType -like 'Fired*') {
$UserStatus = $ozon.employeeType.split()[0]
$FiredDate = [datetime]::parseexact($ozon.employeeType.split()[1], 'dd.MM.yyyy', $null).ToString('yyyy-MM-dd')
}
else {
$UserStatus = $ozon.employeeType
}
$siteInfo.Add([PSCustomObject]@{
WebTitle = $webTitle
SiteURL = $siteUrl
Owner = $siteOwner
LastTimeUsage = $siteLastTimeUsage
DiskUsage = $siteUsage
Quota = $siteQuota
QuotaUsage = $QuotaUsage
Database = $siteDatabases
Ozon = $ozon.Enabled
UserStatus = $UserStatus
FireDate = $FiredDate
Manager = $manager
})
}
}
$siteInfo | Export-Csv -Path "C:\SysOps\SharePointSites.csv" -NoTypeInformation -Encoding UTF8 -Delimiter ';' -Append
$siteInfo | Export-Excel -Path "C:\SysOps\SharePointSites.xlsx" -Append
$UserStatus = ''
$FiredDate = ''
$manager = ''
$ozon = ''
$site.Dispose()
}
$totalSites = $totalSites + $database.Sites.Count
}
Write-Host 'Empty databases - ' $emptyDB -ForegroundColor Red
Write-Host 'Databases with sites - ' $fullDB -ForegroundColor Green
Write-Host '---'
Write-Host 'Total sites - ' $totalSites -ForegroundColor Green
}