Dan Sanders
  • Home
  • About
  • Contact
  • – PowerShell
  • – SharePoint
  • – Workflow
Copyright © 2016 Dan Sanders. All Rights Reserved.
Dan Sanders
  • Home
  • About
  • Contact
  • – PowerShell
  • – SharePoint
  • – Workflow

Content Database and Site Collection Report

byDan Sanders inPowershell, SharePoint posted11 December, 2018
0
0
Content Database and Site Collection Report

About the report

The below script will generate a detailed report of Content Databases and Site Collections in your Farm, send it in an HTML email to the recipients specified.

The Content Database section reports on Size (GB), Database Status, current Site Collection count and Max Site Collection limit.

Snippet from Content Database report section of email.

The Site Collection section reports on all Site Collections per Content Database. This could be useful when deciding which Site Collections should be moved to another Content Database

Snippet from Site Collection report section of email.

The Script

To use the script
– Copy the below script to your environment.
– Update the variables in the mail info section.
– Create a scheduled task to run the script monthly.

<#
# Monthly Content Database and Site Collection Database Report
# Sends and email with details of Content Databases (Size, Status, SC Count) Per Web Application
# Also appends report of each Site Collection, size and URL per Content Database
# @Author Dan Sanders | dan@sanders.nz
#>

#Get SharePoint Content database sizes
if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
    Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

# Mail Info 
$SMTPServer = "mail.domain.com"
$emailFrom = "noreply@domain.com"
$emailTo = "<recipent@domain.com>", "<recipent@domain.com>", "<recipent@domain.com>", "<recipent@domain.com>"
$subject = "Monthly Report on Content Databases and Site Collections"
# Setup Content Database Table
$CDBTable = New-Object System.Text.StringBuilder
$CDBTableHeader = "<table class='tbl'><tr><th class='left'>Content Database</th><th class='left'>Size (GB)</th><th class='left'>Database Status</th><th class='left'>Current SC Count</th><th class='left'>Max SC</th></tr>"
# Setup Site Collection Table
$SCTable = New-Object System.Text.StringBuilder
$SCTable.Append("<table class='tbl'><tr><th class='left'>Site Collection URL</th><th class='left'>Size (MB)</th></tr>")
#CSS Styles for the Tables
$css = "
<style type='text/css'>
.tbl  {border-collapse:collapse;border-spacing:0;border-color:#999;}
.tbl td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#999;color:#444;background-color:#F7FDFA;}
.tbl th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#999;color:#fff;background-color:#26ADE4;}
.tbl .right{text-align:right;vertical-align:top}
.tbl .left{text-align:left;vertical-align:top}
</style>"
# Initilise Totals
$totalCDBSize, $totalCDBCount, $totalSCCount = 0
# Begin email body 
# Add CSS Styles and email header text
$emailBody = "$css <h1>Monthly Report on Content Databases and Site Collections</h1><h2>Content Database Report</h2>"
# Get all Web Applications
$webApps = Get-SPwebApplication

# For each Web Application in the SharePoint Farm
foreach($webApp in $webApps)
{
    # Grab all Content Databases and sort by size 
    $ContentDatabases = $webApp.ContentDatabases | Sort-Object disksizerequired -desc

    # Increment the total Content DB count
    $totalCDBCount += $webApp.ContentDatabases.Count
    
    # Begin Content Database table per Web Application
    $CDBTable.Append("<h3>Content Databases for $($webApp.url)</h3> $CDBTableHeader")
    
    foreach($ContentDatabase in $ContentDatabases)
    {
        # Get CBD size by GB 
        $ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2)
        $CDBName = $ContentDatabase.Name
        
        # Create CDB Table Row
        $CDBTable.Append("<tr><td class='left'>$CDBName</td><td class='right'>$ContentDatabaseSize</td><td class='left'>$($ContentDatabase.Status)</td><td class='right'>$($ContentDatabase.CurrentSiteCount)</td><td class='right'>$($ContentDatabase.MaximumSiteCount)</td></tr>")
        
        # Increment the Total DB Size
        $totalCDBSize = $totalCDBSize + $ContentDatabaseSize
        
        # Site Collection Table Row = CDB name.
        $SCTable.Append("<tr><td class='left' colspan='2'><strong>$CDBName</strong></td></tr>")
        # Site Collection REPORT for CDB
        # Get all Site Collections for CDB and sort by size.
        $SCs = Get-SPSite -Limit All -ContentDatabase $CDBName | Select url, @{label="SC Size";Expression={[math]::Round($_.usage.storage/1MB)}} | Sort-Object "SC Size" -Descending
        foreach($SC in $SCs)
        {
            # Create SC Table Row
            $SCTable.Append("<tr><td class='left'>$($SC.url)</td><td class='right'>$($sc.'SC Size') MB</td></tr>")
            # Increment the total SC count
            $totalSCCount += 1
        }

    }
        # End Content Database Table for that Web Application
        $CDBTable.Append("</table>")
	
}

# End Site Collection Table for all Web Applications
$SCTable.Append("</table>")

# Update email body
# Append Content Database table and totals.
# Append Site Collection table and totals.
$emailBody += $CDBTable.ToString() + "<br/>
<Strong>Total Content DB Count $totalCDBCount</strong><br/>
<Strong>Total Content DB Size $totalCDBSize GB</strong><br/>
<h2>Site Collection Report</h2>
 $($SCTable.ToString()) 
<Strong>Total Site Collection Count $totalSCCount </strong>"

# Send HTML Email with $emailBody
if(!($SMTPServer) -OR !($emailFrom) -OR !($emailTo))
{
    Write-Host "No e-mail being sent, if you do want to send an e-mail, please enter the values for the following variables: $SMTPServer, $emailFrom and $emailTo."
}
else
{
    Send-MailMessage -SmtpServer $SMTPServer -From $emailFrom -To $emailTo -Subject $subject -Body $emailBody -BodyAsHTML 
}

Thanks to this post ‘Content Database size report using PowerShell‘ that generates a simple Content Database report into a text file which formed the basic structure of the above.
The post also describes how to create a scheduled task for those that are unsure.

Share this :

Related Posts

0
4 February, 2016
Adding a ContentType to each Library with PowerShell

About the report The below script will generate a detailed report of Content Databases and Site...

30 Comments
0
24 June, 2013
Enable Fly-Out for Quick Launch

About the report The below script will generate a detailed report of Content Databases and Site...

96 Comments
0
17 September, 2013
Remove Drop Off Library and fix up the QuickLaunch in SharePoint 2010

About the report The below script will generate a detailed report of Content Databases and Site...

67 Comments
0
Fix SharePoint 2013 UPS Stuck on Starting
4 July, 2018
Fix SharePoint 2013 UPS Stuck on Starting

About the report The below script will generate a detailed report of Content Databases and Site...

54 Comments
0
PowerShell for every List in each Web of a SiteCollection
28 February, 2016
PowerShell for every List in each Web of a SiteCollection

About the report The below script will generate a detailed report of Content Databases and Site...

37 Comments
0
24 June, 2013
Customizing the Quick Launch

About the report The below script will generate a detailed report of Content Databases and Site...

368 Comments
0
Showcase: SharePoint 2013 Global Reusable Workflow using REST
14 March, 2016
Showcase: SharePoint 2013 Global Reusable Workflow using REST

About the report The below script will generate a detailed report of Content Databases and Site...

92 Comments
0
28 April, 2016
Disable Loopback check – UPA Picture Sync Error

About the report The below script will generate a detailed report of Content Databases and Site...

73 Comments
7
Enabling multiple Document Set views
20 September, 2017
Enabling multiple Document Set views

About the report The below script will generate a detailed report of Content Databases and Site...

2 Comments
0
28 February, 2016
Publish SharePoint 2013 Workflow Globally

About the report The below script will generate a detailed report of Content Databases and Site...

No comment

Leave a Comment! Cancel reply

You must be logged in to post a comment.

Recent Posts

  • SharePoint Add-in 401 Unauthorized
  • Content Database and Site Collection Report
  • SharePoint helpful hidden URLs

Tags

Config (1) Content Types (2) Design (3) Document Libraries (7) Document Sets (1) How-To (1) Lists (4) Office365 (1) PnP (1) PowerShell (12) Quick Edit (2) REST (1) SharePoint (11) SharePoint 2010 (11) SharePoint 2013 (14) SharePoint Foundation (2) SharePoint Online (1) SP2010 Workflow (2) SP2013 Workflow (3) Systems (2) User Profile Service (1) Views (5) Windows Server (1) Workflow (2)

Archives

  • November 2020
  • December 2018
  • September 2018
  • July 2018
  • September 2017
  • August 2016
  • April 2016
  • March 2016
  • February 2016
  • January 2016
  • September 2013
  • July 2013
  • June 2013