Strange behavior Powershell-script in Sharepoint 2019

46 views Asked by At

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
} 
0

There are 0 answers