Sharepoint Content Database Size Report

Hi Readers,

Today I am sharing a script which we have used to extract content database size report across multiple farms.

First of all enable ps remoting on sharepoint farms so that script can be executed remotely, follow below article.

Enable Powershell Re-moting for SharePoint 2010

Now next step is download & extract the zip file from below link, edit the .ps1 file.

https://github.com/VikasSukhija/Downloads/blob/master/Sharepointcontentdbreport.zip

Change the variables as per your enviornment

# ***************************************************************************

# Variable initializing to send mail

$TXTFile = “.\ContentDBReport.html”

$SMTPServer = “smtp.lab.com”

$emailFrom = “Messaging@lab.com”

$emailTo = “vikassukhija@lab.com”

$subject = “Sharepoint Farms Content databases Report”

$emailBody = “Dailyreport on Sharepoint Farms Content databases”
#****************************************************************************

We have 4 farms so we created 4 ps sessions (you can increase or decrease as per your environment)

$s1 = New-PSSession -ComputerName Server1 -Authentication CredSSP -Credential $cred1

$s2 = New-PSSession -ComputerName Server2 -Authentication CredSSP -Credential $cred1

$s3 = New-PSSession -ComputerName Server3 -Authentication CredSSP -Credential $cred1

$s4 = New-PSSession -ComputerName server4 -Authentication CredSSP -Credential $cred1

################################################################

Call function

$h1 = ContentReport $s1

$h2 = ContentReport $s2

$h3 = ContentReport $s3

$h4 = ContentReport $s4

################################################################

Change Html Details accordingly
ConvertTo-HTML -head $b -Body “<h1>$(Get-Date) Sharepoint Farm Database Content DB Report</h1> <br /> <h2>SharePoint_ParentFarm $h1 SharePoint_IntranetFarm $h2 SharePoint_GalwayFarm $h3 SharePoint_TokyoFarm $h4</h2>” | Out-File $TXTFile

################################################################

Now execute the batch file, it will ask for credentials, enter the credentials of user that has farm admin access on all 4 farms.

Below is the report:-

Note:- Script will also change the color to orange & red ( db size more than 100 gb –> Red & 80 GB –>yellow) — you can change the value inside accordingly. cell will also turn to orange if site count is on warning level.

#*************************************************************************** 
#    Script to Get Content Db report for Different Farms                    *  
#    Date   : 7th August,2104                                              * 
#    Author : Abhishek Gupta                                               * 
#    Reviewer: Vikas Sukhija                                               * 
#    modified: Added logging, error checking & converted to function       * 
#*************************************************************************** 
$date = get-date -format d 
# replace \ by - 
$time = get-date -format t 
$month = get-date  
$month1 = $month.month 
$year1 = $month.year 
 
$date = $date.ToString().Replace(“/”, “-”) 
 
$time = $time.ToString().Replace(":", "-") 
$time = $time.ToString().Replace(" ", "") 
 
$log1 = ".\Processed\Logs" + "\" + "skipcsv_" + $date + "_.log" 
#$log2 = ".\Processed\Logs" + "\" + "Modified_" + $month1 +"_" + $year1 +"_.log" 
#$output1 = ".\" + "G_DistributionList_" + $date + "_" + $time + "_.csv"  
 
$logs = ".\Processed\Logs" + "\" + "Powershell" + $date + "_" + $time + "_.txt" 
 
Start-Transcript -Path $logs  
 
# *************************************************************************** 
# Variable initializing to send mail 
$TXTFile = ".\ContentDBReport.html" 
$SMTPServer = "smtp.lab.com"  
$emailFrom = "Messaging@lab.com"  
$emailTo = "vikassukhija@lab.com"  
$subject = "Sharepoint Farms Content databases Report"  
$emailBody = "Dailyreport on Sharepoint Farms Content databases" 
 
#**************************************************************************** 
# HTML code to format output 
$b = "<!--mce:0-->" 
 
#******************************************************************************** 
# Creating PSSession and Loading Snapin(make sure your account has rights to sharepoint) 
 
$cred1 = Get-Credential 
 
$s1 = New-PSSession -ComputerName Server1 -Authentication CredSSP -Credential $cred1 
$s2 = New-PSSession -ComputerName Server2 -Authentication CredSSP -Credential $cred1 
$s3 = New-PSSession -ComputerName Server3 -Authentication CredSSP -Credential $cred1 
$s4 = New-PSSession -ComputerName server4 -Authentication CredSSP -Credential $cred1 
 
Function ContentReport ($session){ 
 
Invoke-Command -Session $session -ScriptBlock {Add-PSSnapin Microsoft.SharePoint.PowerShell} 
 
$f1 = Invoke-Command -Session $session -ScriptBlock {Get-SPWebApplication | Get-SPContentDatabase} 
$g1 = $f1 | Select-Object DisplayName,WebApplication,CurrentSiteCount,disksizerequired,WarningSiteCount,MaximumSiteCount |  
      ConvertTo-Html -Fragment DisplayName,WebApplication,WarningSiteCount,MaximumSiteCount,@{L='DiskSizeRequired';E={ 
                                          if($_.disksizerequired -gt 100Gb){ 
                                               "#font"+$_.disksizerequired+"font#" 
                                          }elseif($_.disksizerequired -ge 80Gb){ 
                                                "#blue"+$_.disksizerequired+"blue#"} 
                                          else{ 
                                               $_.disksizerequired 
                                          } 
                                       } 
                                 },@{L='CurrentSiteCount';E={ 
                                 if($_.CurrentSiteCount -ge $_.WarningSiteCount){  
                                 "#blue"+$_.CurrentSiteCount+"blue#" 
                                 }else{ 
                                               $_.CurrentSiteCount 
                                          } 
                                          } 
                                          }                             
$g1 = $g1 -replace ("#font",'<span style="color:black; background-color:red" >') 
$g1 = $g1 -replace "font#","</span>" 
$g1 = $g1 -replace ('#blue','<span style="color:black; background-color:orange"> ') 
$g1 = $g1 -replace "blue#","</span>" 
 
return $g1 
} 
###############call function for diffrent farms################################################### 
 
$h1 = ContentReport $s1 
$h2 = ContentReport $s2 
$h3 = ContentReport $s3 
$h4 = ContentReport $s4 
 
##############################Convert to HTML #################################################### 
 
ConvertTo-HTML -head $b -Body "<h1>$(Get-Date) Sharepoint Farm Database Content DB Report</h1> <br />  
<h2>SharePoint_ParentFarm $h1 SharePoint_IntranetFarm  $h2 SharePoint_GalwayFarm  $h3 SharePoint_TokyoFarm  $h4</h2>" |  
Out-File $TXTFile 
 
# Code to Send Mail  
Send-MailMessage -SmtpServer $SMTPServer -From $emailFrom -To $emailTo -Subject $subject -Body $emailBody -Attachment $TXTFile 
 
if ($error -ne $null) 
      { 
#SMTP Relay address 
$msg = new-object Net.Mail.MailMessage 
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
 
#Mail sender 
$msg.From = $emailFrom 
#mail recipient 
$msg.To.Add($emailTo) 
#$msg.To.Add($email2) 
$msg.Subject = "Sharepoint Content DB Script error" 
$msg.Body = $error 
$smtp.Send($msg) 
$error.clear() 
       } 
  else 
 
      { 
    Write-host "no errors till now" 
      } 
 
Stop-Transcript 
#*************************************************************************************************