SharePoint List Usage and Statistics

This article will show you how to gather SharePoint List usage information and statistics.

Many site administrators using the usage reporting to monitor and gather information about their sites. This feature allows you as site administrator to view the storage used by the site, pages hits, where does the traffic come and other necessary information for site administrators. This helps to identify the important sites and to manage their environment properly. In this article we will go one step further. With the help of the SharePoint Object Model we will gather SharePoint List usage information and statistics. This article includes the following information:

• Basic SharePoint List Information Report
• SharePoint List Alerts Information and Statistics Report
• SharePoint List Users Information and Statistics Report
• SharePoint List Usage Report
• SharePoint List Change Report
• SharePoint List Storage Report

Before we begin with the real stuff, let's gather some basic information regarding to the list. SPList object has some valuable properties that we can use to retrieve basic information over our list, like: Who created the list and when? How many items the list contains? Etc.

To generate the list information basic report we can use the SPList object as displayed in this code example:

SPList ist = SPContext.Current.Web.Lists[LIST_NAME];
this.Controls.Add(new LiteralControl("<h3>Basic SharePoint List Information Report</h3>"));
this.Controls.Add(new LiteralControl("<table><tr><td><b>Property</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Author</td><td>" + list.Author.LoginName + "</td><td>The user who created the list </td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Created</td><td>" + list.Created.ToString() + "</td><td>The creation date and time for the list</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Last Modified</td><td>" + list.LastItemModifiedDate + "</td><td>The time that the list was last modified</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Template</td><td>" + list.BaseTemplate.ToString() + "</td><td>The list definition type on which the list is based</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Version</td><td>" + list.Version.ToString() + "</td><td>The version number of the list</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Total items</td><td>" + list.ItemCount.ToString() + "</td><td>The total number of items in the list</td></tr>"));
this.Controls.Add(new LiteralControl("</table>"));

One way to know which list is popular in your site is to check the total alerts in the list made by the users. We can use the SPAlertCollection property from SPUser to check if the user subscribed to alerts from this list.

To generate the list alerts report we need to run through each SPUser in SPWeb.AllUsers as displayed in this code example:

this.Controls.Add(new LiteralControl("<h3>SharePoint List Alerts Information and Statistics Report</h3>"));
int totalUsers = 0;
int changeTypeAll = 0;
int changeTypeAdd = 0;
int changeTypeDelete = 0;
int changeTypeDiscussion = 0;
int changeTypeModify = 0;
foreach (SPUser user in SPContext.Current.Web.AllUsers)
{
foreach (SPAlert alert in user.Alerts)
{
if (list.ID == alert.List.ID)
{
totalUsers++;
switch (alert.EventType)
{
case SPEventType.All:
changeTypeAll++;
break;
case SPEventType.Add:
changeTypeAdd++;
break;
case SPEventType.Delete:
changeTypeDelete++;
break;
case SPEventType.Discussion:
changeTypeDiscussion++;
break;
case SPEventType.Modify:
changeTypeModify++;
break;
default:
break;
}
}
}
}
this.Controls.Add(new LiteralControl("<table><tr><td><b>Property</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Total Alerts</td><td>" + totalUsers.ToString() + "</td><td>The number of users actually subscribe to the list</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>All Change Type</td><td>" + changeTypeAll.ToString() + "</td><td>All events pertaining to the list or list item</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Add Change Type</td><td>" + changeTypeAdd.ToString() + "</td><td>Additions to the list or list item</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Modify Change Type</td><td>" + changeTypeModify.ToString() + "</td><td>All changes made in a list or list item</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Delete Change Type</td><td>" + changeTypeDelete.ToString() + "</td><td>Deletion of a list or list item</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Discussion Change Type</td><td>" + changeTypeDiscussion.ToString() + "</td><td>Changes in Web discussions</td></tr>"));
this.Controls.Add(new LiteralControl("</table>"));

To keep track of who is accessing our list, what items were accessed by the users and when. We can use the SPWeb.GetUsageData* method to find this information.

To generate the list usage report we need to filter the DataTable and display only item from our list as displayed in this code example:

this.Controls.Add(new LiteralControl("<h3>SharePoint List Usage Report</h3>"));
this.Controls.Add(new LiteralControl("<table><tr><td><b>File</b></td><td><b>Total Hits</b></td><td><b>Most Recent Day</b></td></tr>"));
DataTable dt = SPContext.Current.Web.GetUsageData(SPUsageReportType.url, SPUsagePeriodType.lastMonth);
foreach (DataRow dr in dt.Rows)
{
if (dr["Folder"].ToString().ToLower() == list.RootFolder.Url.ToLower())
{
this.Controls.Add(new LiteralControl("<tr><td valign=\"top\">" + dr["Page"].ToString() + "</td><td valign=\"top\">" + dr["Total Hits"].ToString() + "</td><td valign=\"top\">" + dr["Most Recent Day"].ToString() + "</td></tr>"));
}
}
this.Controls.Add(new LiteralControl("</table>"));

The change report display all changes made on the list in the last 30 days

To generate the list change report we can use the SPChangeToken objects, pass SPChangeCollection.CollectionScope.List as the constructor as displayed in this code example:

this.Controls.Add(new LiteralControl("<h3>SharePoint List Change Report</h3>"));
this.Controls.Add(new LiteralControl("<table><tr><td><b>Item</b></td><td><b>Change Type</b></td><td><b>Time</b></td></tr>"));
SPChangeToken changeToken = new SPChangeToken(SPChangeCollection.CollectionScope.List, list.ID, DateTime.Today.AddDays(-30));
SPChangeCollection changeCollection = list.GetChanges(changeToken);
foreach (SPChange change in changeCollection)
{
if (change.GetType().ToString() == "Microsoft.SharePoint.SPChangeItem")
{
SPChangeItem changeItem = (SPChangeItem)change;
try
{
SPListItem item = list.GetItemByUniqueId(changeItem.UniqueId);
if (item != null)
{
this.Controls.Add(new LiteralControl("<tr><td>" + item.Name + "</td><td>" + changeItem.ChangeType.ToString() + "</td><td>" + changeItem.Time.ToString() + "</td></tr>"));
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
this.Controls.Add(new LiteralControl("</table>"));

Now to my favorite part, the users number reports. Here we gather the actually users using this list. We check how many files are checked out by list users and how many versions files does the list have.

The storage report check the amount of disk space taken up by the files and their versions in your list

For the last two reports we run through all the items in the list, as displayed in this code example (long code part).
I also use two help classes, the ListUser class and GenericComparer, you can find these classes code here.

List<ListUser> listUsers = new List<ListUser>();
List<ListUser> listFindUsers = new List<ListUser>();
int checkedFiles = 0;
int totalVersions = 0;
long filesSize = 0;
long versionsSize = 0;
foreach (SPListItem item in list.Items)
{
listFindUsers.Clear();
if (item["Author"] != null)
{
string userValue = item["Author"].ToString();
int index = userValue.IndexOf(';');
int id = Int32.Parse(userValue.Substring(0, index));
SPUser author = SPContext.Current.Web.SiteUsers.GetByID(id);
listFindUsers = listUsers.FindAll(delegate(ListUser listUser)
{
if (listUser.User.LoginName != author.LoginName)
{ return false; }
listUser.Activities += 1;
return true;
});
if (listFindUsers.Count == 0)
{
listUsers.Add(new ListUser(author, 1));
}
}
listFindUsers.Clear();
if (item["Editor"] != null)
{
string userValue = item["Editor"].ToString();
int index = userValue.IndexOf(';');
int id = Int32.Parse(userValue.Substring(0, index));
SPUser editor = SPContext.Current.Web.SiteUsers.GetByID(id);
listFindUsers = listUsers.FindAll(delegate(ListUser listUser)
{
if (listUser.User.LoginName != editor.LoginName)
{ return false; }
listUser.Activities += 1;
return true;
});
if (listFindUsers.Count == 0)
{
listUsers.Add(new ListUser(editor, 1));
}
}
if (item.File != null)
{
filesSize += item.File.Length;
listFindUsers.Clear();
if (item.File.Author != null)
{
listFindUsers = listUsers.FindAll(delegate(ListUser listUser)
{
if (listUser.User.LoginName != item.File.Author.LoginName)
{
return false;
}
listUser.Activities += 1;
return true;
});
if (listFindUsers.Count == 0)
{
listUsers.Add(new ListUser(item.File.Author, 1));
}
}
listFindUsers.Clear();
if (item.File.ModifiedBy != null)
{
listFindUsers = listUsers.FindAll(delegate(ListUser listUser)
{
if (listUser.User.LoginName != item.File.ModifiedBy.LoginName)
{ return false; }
listUser.Activities += 1;
return true;
});
if (listFindUsers.Count == 0)
{
listUsers.Add(new ListUser(item.File.ModifiedBy, 1));
}
}
listFindUsers.Clear();
if (item.File.CheckedOutBy != null)
{
checkedFiles = +1;
listFindUsers = listUsers.FindAll(delegate(ListUser listUser)
{
if (listUser.User.LoginName != item.File.CheckedOutBy.LoginName)
{ return false; }
listUser.Activities += 1;
return true;
});
if (listFindUsers.Count == 0)
{
listUsers.Add(new ListUser(item.File.CheckedOutBy, 1));
}
}
if (list.EnableVersioning)
{
bool currentVersion = true;
foreach (SPListItemVersion version in item.Versions)
{
if (!currentVersion)
{
totalVersions += 1;
listFindUsers = listUsers.FindAll(delegate(ListUser listUser)
{
if (listUser.User.LoginName != version.CreatedBy.User.LoginName)
{ return false; }
listUser.Activities += 1;
return true;
});
if (listFindUsers.Count == 0)
{
listUsers.Add(new ListUser(version.CreatedBy.User, 1));
}
if (version.ListItem.File != null)
{
versionsSize += version.ListItem.File.Length;
}
}
currentVersion = false;
}
}
}
}
listUsers.Sort(new GenericComparer<ListUser>("Activities", GenericComparer<ListUser>.SortOrder.Descending));
this.Controls.Add(new LiteralControl("<h3>SharePoint List Users Information and Statistics Report</h3>"));
this.Controls.Add(new LiteralControl("<table><tr><td><b>Property</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Total users</td><td>" + listUsers.Count.ToString() + "</td><td>The number of users actually subscribe to the list</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Total checked files</td><td>" + checkedFiles.ToString() + "</td><td>The total files who has checked out by list users</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Total versions of files</td><td>" + totalVersions.ToString() + "</td><td>The total number of versions of all files created by list users</td></tr>"));
string topUsers = "";
int i = 0;
foreach (ListUser listUser in listUsers)
{
i++;
if (i > 5) break;
topUsers += listUser.User.LoginName + " (" + listUser.Activities.ToString() + " Activities)<br/>";
}
this.Controls.Add(new LiteralControl("<tr><td valign=\"top\">Top users</td><td valign=\"top\">" + topUsers + "</td><td valign=\"top\">The top users on the list</td></tr>"));
this.Controls.Add(new LiteralControl("</table>"));
this.Controls.Add(new LiteralControl("<h3>SharePoint List Storage Report</h3>"));
this.Controls.Add(new LiteralControl("<table><tr><td><b>Property</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Total files size</td><td>" + (ConvertBytesToMegabytes(filesSize)).ToString("#0.00") + " MB</td><td>This is the amount of disk space taken up by the files in your list</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Total versions size</td><td>" + (ConvertBytesToMegabytes(versionsSize)).ToString("#0.00") + " MB</td><td>This is the amount of disk space taken up by the versions in your list</td></tr>"));
this.Controls.Add(new LiteralControl("<tr><td>Total folder size</td><td>" + ((ConvertBytesToMegabytes(versionsSize) + ConvertBytesToMegabytes(filesSize))).ToString("#0.00") + " MB</td><td>This is the total amount of disk space taken by the list</td></tr>"));
this.Controls.Add(new LiteralControl("</table>"));

- The GetUsageData method returns only 2000 records from the usage data for the site.
- The Object Model only stores the last 31 days of statistics.
- If the GetUsageData retun null make sure you activate the SharePoint usage reporting. For more information see this MSDN article
- These processes are expensive it is better to create a timer job to generate these reports and dont forget to dispose!

More and more companies using SharePoint primarily for file sharing. That's why it is important to understand which list is popular and which list is only using expensive resources. I hope these reports will help you to make better decisions regarding your SharePoint list usage.

Download the complete source code of all reports - SharePoint List Usage Web Part