Tag: SharePoint

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.

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.