Track SharePoint Site Growth

Facebooktwittergoogle_pluslinkedin

I have found that it is difficult to monitor SharePoint growth and usage.  The out of the box options don’t provide a good way to see what sites are growing the fastest, or what library in a site collection has the most documents.  There are many third party solutions that can be purchased that solve these limitations, but I found a easier way that is free.

By using a combination of a simple SQL database and some PowerShell script, I now have a way to record site usage and metrics on a scheduled basis.  The best part is, since the data is in SQL Server, I can easily query the data in an almost unlimited number of ways.  I can expand the tables to record additional metrics as needed.  I can export the data to Excel for further analysis, or I can even create a SQL Server Reporting Services report to display the data in a user friendly format.

This script solution will record the following items.

  • Site Collection – Note:  Some values require health and usage to be configured and enabled.
    • URL
    • Name
    • Average Resource Usage
    • Content Database Name
    • Last Content Modified Date
    • Last Security Modified Date
    • Owner
    • Secondary Contact
    • Site Count
    • Storage size
    • Discussion storage size
    • Bandwidth usage
    • Hits
    • Visits
  • Site
    • URL
    • Title
    • Author
    • Date Created
    • Last Item Modified Date
    • List Count
    • Site Count
  • List/Library
    • URL
    • Title
    • Author
    • Created
    • Index Count
    • Folder Count
    • Item Count
    • Last Item Delete Date
    • Last Item Modified Date
Create the Database

The first part of the solution is to create a SQL Server database.  The following SQL statement will create the database and three tables.

CREATE DATABASE [SP_Site_Growth_Logging]
ALTER DATABASE [SP_Site_Growth_Logging] SET COMPATIBILITY_LEVEL = 110
GO
USE [SP_Site_Growth_Logging]

-- Create the SiteCollection table
CREATE TABLE [dbo].[SiteCollection](
	[AuditDate] [datetime] NOT NULL,
	[SiteID] [uniqueidentifier] NOT NULL,
	[URL] [varchar](1000) NOT NULL,
	[PortalName] [varchar](512) NOT NULL,
	[AverageResourceUsage] [decimal](18, 0) NOT NULL,
	[ContentDatabase] [varchar](256) NOT NULL,
	[LastContentModifiedDate] [datetime] NOT NULL,
	[LastSecurityModifiedDate] [datetime] NOT NULL,
	[Owner] [varchar](512) NOT NULL,
	[SecondaryContact] [varchar](512) NOT NULL,
	[SiteCount] [int] NOT NULL,
	[UsageStorage] [int] NOT NULL,
	[UsageDiscussionStorage] [int] NOT NULL,
	[UsageBandwidth] [int] NOT NULL,
	[UsageHits] [int] NOT NULL,
	[UsageVisits] [int] NOT NULL,
 CONSTRAINT [PK_SiteCollection] PRIMARY KEY CLUSTERED 
(
	[AuditDate] ASC,
	[SiteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Create the Site table
CREATE TABLE [dbo].[Site](
	[AuditDate] [datetime] NOT NULL,
	[SiteID] [uniqueidentifier] NOT NULL,
	[WebID] [uniqueidentifier] NOT NULL,
	[URL] [varchar](1000) NOT NULL,
	[Title] [varchar](512) NOT NULL,
	[Author] [varchar](255) NOT NULL,
	[Created] [datetime] NOT NULL,
	[LastItemModifiedDate] [datetime] NOT NULL,
	[ListCount] [int] NOT NULL,
	[SiteCount] [int] NOT NULL,
 CONSTRAINT [PK_Site] PRIMARY KEY CLUSTERED 
(
	[AuditDate] ASC,
	[SiteID] ASC,
	[WebID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Create the List table
CREATE TABLE [dbo].[List](
	[AuditDate] [datetime] NOT NULL,
	[SiteID] [uniqueidentifier] NOT NULL,
	[WebID] [uniqueidentifier] NOT NULL,
	[ListID] [uniqueidentifier] NOT NULL,
	[URL] [varchar](1000) NOT NULL,
	[Title] [varchar](512) NOT NULL,
	[Author] [varchar](255) NOT NULL,
	[Created] [datetime] NOT NULL,
	[IndexCount] [int] NOT NULL,
	[FolderCount] [int] NOT NULL,
	[ItemCount] [int] NOT NULL,
	[LastItemDeleteDate] [datetime] NOT NULL,
	[LastItemModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_List] PRIMARY KEY CLUSTERED 
(
	[AuditDate] ASC,
	[SiteID] ASC,
	[WebID] ASC,
	[ListID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
Making SQL Calls from PowerShell

There are a couple of ways to execute SQL Server statements from PowerShell.  I have found the easiest way is to use the following PowerShell functions.  The script below contains two function for executing SQL commands.  The first function would be used to get data from SQL Server in a dataset.  The second function is used to execute SQL commands that don’t return data, such as Insert and Delete.  It is the second command that we will be using.

function Invoke-SQLQuery {
    param(
        [string] $Server = $(throw "Missing server parameter.  Please specify a SQL Server."),
        [string] $Database = $(throw "Missing database parameter. Please specify a SQL database."),
        [string] $Query = $(throw "Missing query parameter.  Please provide a SQL command to execute.")
    )

    $connString = "Data Source=$Server; Integrated Security=SSPI; Initial Catalog=$Database"

    $conn = New-Object System.Data.SqlClient.SqlConnection($connString)
    $cmd = New-Object System.Data.SqlClient.SqlCommand($Query, $conn)
    $conn.Open()

    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | Out-Null

    $conn.Close()
    $conn.Dispose()
 
    $dataset
}


function Invoke-SQLNonQuery {
    param(
        [string] $Server = $(throw "Missing server parameter.  Please specify a SQL Server."),
        [string] $Database = $(throw "Missing database parameter. Please specify a SQL database."),
        [string] $Query = $(throw "Missing query parameter.  Please provide a SQL command to execute.")
    )

    $connString = "Data Source=$Server; Integrated Security=SSPI; Initial Catalog=$Database"

    $conn = New-Object System.Data.SqlClient.SqlConnection($connString)
    $cmd = New-Object System.Data.SqlClient.SqlCommand($Query, $conn)
    $conn.Open()

    if ($cmd.ExecuteNonQuery() -eq -1) {
        $results = $false
    }
    else {
        $results = $true
    }

    $conn.Close()
    $conn.Dispose()

    $results
}
Collect and Record Site Metrics

The following PowerShell command is used to collect site collection, web site and list/library metrics.  The script will write the information to the SQL Server database that was created earlier.

There are a couple of key variables that you’ll need to set.

  • $webAppURL:  URL of a web application on your farm
  • $SQLServer: Name or DNS to your SQL Server
  • $database: Name of the site growth logging database
  • $includeWebs: Set to true if you want to track web site metrics
  • $includeLists: Set to true if you want to track list metrics
  • $ignoreListArray:  Array of lists I’m not interested in tracking metrics for.
Clear-Host

. "C:\Scripts\SQLCommands.ps1"

$webAppURL = "https://intranet.contoso.com"
$SQLServer = "ContosoSQL"
$database = "SP_Site_Growth_Logging"
$includeWebs = $true
$includeLists = $true

$ignoreListArray = @("appdata", "Access Requests", "Cache Profiles", "Composed Looks", "Project Policy Item List", "Solution Gallery", "Style Library", "Theme Gallery", "Translation Packages", "Translation Status", "User Information List", "Variation Labels", "Web Part Gallery", "wfpub", "Workflow Tasks", "Master Page Gallery", "Pages", "Workflow History", "List Template Gallery", "Site Assets", "Site Pages", "Content and Structure Reports", "Content type publishing error log", "Converted Forms", "Device Channels", "Form Templates", "Long Running Operation Status", "Quick Deploy Items", "MicroFeed", "Notification List", "Relationships List", "Suggested Content Browser Locations", "Reusable Content", "TaxonomyHiddenList", "Variation Logs")

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

$auditDate = Get-Date
$auditDate = $auditDate.ToUniversalTime()

$webApp = Get-SPWebApplication $webAppURL
foreach($site in $webApp.Sites)
{
    if ($site -ne $null)
    {
        ########################################
        # Site Collections

        Write-Host -ForegroundColor Gray "Processing site collection $site"

        $siteID = $site.ID
        $URL = $site.Url
        $portalName = $site.PortalName
        $averageResourceUsage = $site.AverageResourceUsage
        $contentDatabase = $site.ContentDatabase
        $lastModifiedDate = $site.LastContentModifiedDate
        $lastSecurityDate = $site.LastSecurityModifiedDate
        $owner = $site.Owner
        $secondaryContact = $site.SecondaryContact
        $siteCount = $site.AllWebs.Count
        $usageStorage = $site.Usage.Storage
        $usageDiscussionStorage = $site.Usage.DiscussionStorage
        $usageBandwidth = $site.Usage.Bandwidth
        $usageHits = $site.Usage.Hits
        $usageVisits = $site.Usage.Visits

        $sql = "INSERT INTO SiteCollection " +
               "(AuditDate, SiteID, URL, PortalName, AverageResourceUsage, ContentDatabase, LastContentModifiedDate, LastSecurityModifiedDate, Owner, SecondaryContact, SiteCount, UsageStorage, UsageDiscussionStorage, UsageBandwidth, UsageHits, UsageVisits)" +
               "VALUES " +
               "('$auditDate', '$siteID', '$URL', '$portalName', $averageResourceUsage, '$contentDatabase', '$lastModifiedDate', '$lastSecurityDate', '$owner', '$secondaryContact', $siteCount, $usageStorage, $usageDiscussionStorage, $usageBandwidth, $usageHits, $usageVisits)"

        $results = Invoke-SQLNonQuery -Server $SQLServer -Database $database -Query $sql
        if ($results -eq $false) {
            Write-Host -ForegroundColor Red "Failed to write site collection audit record"
            Write-Host -ForegroundColor Red $sql
        }


        $webs = Get-SPWeb -Site $site -Limit All
        foreach ($web in $webs)
        {
            ########################################
            # Web Sites
            Write-Host -ForegroundColor Green "    Processing web site $web"

            if ($includeWebs) {
                $webID = $web.ID
                $URL = $web.Url
                $title = $web.Title
                $author = $web.Author
                $created = $web.Created
                $lastModifiedDate = $web.LastItemModifiedDate
                $listCount = $web.Lists.Count
                $siteCount = $web.Webs.Count

                $sql = "INSERT INTO Site " +
                       "(AuditDate, SiteID, WebID, URL, Title, Author, Created, LastItemModifiedDate, ListCount, SiteCount) " +
                       "VALUES " +
                       "('$auditDate', '$siteID', '$webID', '$URL', '$title', '$author', '$created', '$lastModifiedDate', $listCount, $siteCount)"

                $results = Invoke-SQLNonQuery -Server $SQLServer -Database $database -Query $sql
                if ($results -eq $false) {
                    Write-Host -ForegroundColor Red "Failed to write site audit record"
                    Write-Host -ForegroundColor Red $sql
                }
            }

            if ($includeLists) {
                foreach ($list in $web.Lists)
                {
                    $processList = $true
                    foreach ($ignoreList in $ignoreListArray) {
                        if ($list.Title -eq $ignoreList) {
                            $processList = $false
                            break;
                        }
                    }

                    if ($processList) {
                        ########################################
                        # Lists
                        Write-Host -ForegroundColor Green "        Processing list $list"

                        $listID = $list.ID
                        $URL = $list.rootfolder.Url
                        $title = $list.Title
                        $author = $list.Author
                        $created = $list.Created
                        $indexCount = $list.FieldIndexes.Count
                        $folderCount = $list.Folders.Count
                        $itemCount = $list.ItemCount
                        $lastDeleteDate = $list.LastItemDeletedDate
                        $lastModifiedDate = $list.LastItemModifiedDate

                        $sql = "INSERT INTO List " +
                               "(AuditDate, SiteID, WebID, ListID, URL, Title, Author, Created, IndexCount, FolderCount, ItemCount, LastItemDeleteDate, LastItemModifiedDate) " +
                               "VALUES " +
                               "('$auditDate', '$siteID', '$webID', '$listID', '$URL', '$title', '$author', '$created', $indexCount, $folderCount, $itemCount, '$lastDeleteDate', '$lastModifiedDate')"

                        $results = Invoke-SQLNonQuery -Server $SQLServer -Database $database -Query $sql
                        if ($results -eq $false) {
                            Write-Host -ForegroundColor Red "Failed to write list audit record"
                            Write-Host -ForegroundColor Red $sql
                        }
                    }
                }
            }
            $web.Dispose()
        }
        $webs.Dispose()
        $site.Dispose()
    }
}
Schedule the Script

You can manually run the script, but it is easer to use Windows Task Scheduler or some other job scheduler to run the script on a regular basis.  I like running it weekly.

Note:  The script needs to be scheduled to run with an account that has access to the SQL Server database.

Facebooktwittergoogle_pluslinkedin

Leave a Reply

Your email address will not be published. Required fields are marked *