SharePoint Content Database Report using Powershell

#Enter a URL in the function at the bottom of this script Example: Report-SharePointDatabase -ReportURL "http://team/sites/reports"
#The first time you run the script, the script will create a custom list with the report fields in a datasheet view
#Each subsequent time you run the script, it will add to the existing list, but with a new report date

function Report-SharePointDatabase{
param(
[string]$ReportURL
)
$varReportDate = get-date
$varReportWeb = get-spweb $ReportURL
$varReportList = $varReportWeb.lists["SharePoint Database Report"]
if(!$varReportList)
{
$varListTemplate = $varReportWeb.listtemplates["Custom List"]
$varReportList = $varReportWeb.lists.add("SharePoint Database Report",
"SharePoint Database Report",$varListTemplate)
$varReportList = $varReportWeb.lists["SharePoint Database Report"]
$varAddField = $varReportList.fields.add("Server", "Text", $null)
$varAddField = $varReportList.fields.add("Status", "Text", $null)
$varAddField = $varReportList.fields.add("Warning Site Count", "Number", $null)
$varAddField = $varReportList.fields.add("Current Site Count", "Number", $null)
$varAddField = $varReportList.fields.add("Maximum Site Count", "Number", $null)
$varAddField = $varReportList.fields.add("Web Application","Text",$null)
$varAddField = $varReportList.fields.add("Attached to Farm","Text",$null)
$varAddField = $varReportList.fields.add("Storage Size GB","Number",$null)
$varAddField = $varReportList.fields.add("Storage Size TB","Number",$null)
$varAddField = $varReportList.fields.add("Needs Upgrade","Text",$null)
$varAddField = $varReportList.fields.add("Database Type","Text",$null)
$varAddField = $varReportList.fields.add("Report Date","DateTime",$null)

$varFields = new-object system.collections.specialized.stringcollection
$varViewField = $varFields.add("Title")
$varViewField = $varFields.add("Server")
$varViewField = $varFields.add("Current Site Count")
$varViewField = $varFields.add("Web Application")
$varViewField = $varFields.add("Storage Size GB")
$varViewField = $varFields.add("Storage Size TB")
$varViewField = $varFields.add("Needs Upgrade")
$varViewField = $varFields.add("Database Type")
$varViewField = $varFields.add("Report Date")
$varView = $varReportList.views.add("AllData", $varFields, $null, 50, $true, $true, "GRID", $false)
$varView.update()
$varView = $varReportList.views["AllData"]
$varView.aggregations = "
"
$varView.aggregationsstatus="On"
$varView.query = '
'
$varView.update()
}

$colContentDatabases = get-spdatabase
foreach($varContentDatabase in $colContentDatabases)
{
$varNewItem = $varReportList.items.add()
$varNewItem["Title"] = $varContentDatabase.name
$varNewItem["Server"] = $varContentDatabase.server
$varNewItem["Status"] = $varContentDatabase.status
$varNewItem["Warning Site Count"] = $varContentDatabase.warningsitecount
$varNewItem["Web Application"] = $varContentDatabase.webapplication.name
$varNewItem["Current Site Count"] = $varContentDatabase.currentsitecount
$varNewItem["Storage Size GB"] = [Math]::round(($varContentDatabase.disksizerequired/1GB),4)
$varNewItem["Storage Size TB"] = [Math]::round(($varContentDatabase.disksizerequired/1TB),4)
$varNewItem["Attached to Farm"] = $varContentDatabase.isattachedtofarm
$varNewItem["Needs Upgrade"] = $varContentDatabase.needsupgrade
$varNewItem["Maximum Site Count"] = $varContentDatabase.maximumsitecount
$varNewItem["Database Type"] = $varContentDatabase.type
$varNewItem["Report Date"] = $varReportDate
$varNewItem.update()
}
write-host "==Script complete=="
$varReportWeb.dispose()
}

Report-SharePointDatabase -ReportURL "http://team/sites/reports"

Advertisements
This entry was posted in PowerShell, SharePoint 2010 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s