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

Basic SharePoint List Information 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.

Property Value Description
Author sharepoint\user1 The user who created the list
Created 10/2/2009 9:22:55 AM The creation date and time for the list
Last Modified 12/30/2009 3:38:56 PM The time that the list was last modified
Template DocumentLibrary The list definition type on which the list is based
Version 9 The version number of the list
Total items 16 The total number of items in the list

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>"));

SharePoint List Alerts Information and Statistics Report


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.

Property Value Description
Total Alerts 3 The number of users actually subscribe to the list
All Change Type 2 All events pertaining to the list or list item
Add Change Type 1 Additions to the list or list item
Modify Change Type 0 All changes made in a list or list item
Delete Change Type 0 Deletion of a list or list item
Discussion Change Type 0 Changes in Web discussions


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>"));

SharePoint List Usage Report


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.

File Total Hits Most Recent Day
document1.doc 15 29-12-2009
document2.doc 7 29-12-2009

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>"));


SharePoint List Change Report


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

Item Change Type Time
document1.doc Add 12/29/2009 2:38:21 PM
document2.doc Update 12/29/2009 2:39:34 PM
document4.doc Add 12/29/2009 2:40:35 PM
document4.doc Update 12/29/2009 2:41:02 PM
document4.doc Update 12/30/2009 12:33:39 PM
document5.doc Add 12/30/2009 2:17:12 PM
document6.doc Add 12/30/2009 2:17:58 PM
document7.doc Add 12/30/2009 3:30:12 PM
document8.doc Add 12/30/2009 3:37:33 PM
document8.doc Update 12/30/2009 3:38:56 PM


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>"));

SharePoint List Users Information and Statistics Report


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.

Property Value Description
Total users 12 The number of users actually subscribe to the list
Total checked files 3 The total files who has checked out by list users
Total versions of files 25 The total number of versions of all files created by list users
Top users sharepoint\user3 (45 Activities)
sharepoint\user1 (22 Activities)
sharepoint\user5 (19 Activities)
sharepoint\user2 (13 Activities)
sharepoint\user4 (11 Activities)
The top 5 users on the list

SharePoint List Storage Report


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

Property Value Description
Total files size 12.58 MB This is the amount of disk space taken up by the files in your list
Total versions size 8.84 MB This is the amount of disk space taken up by the versions in your list
Total folder size 21.42 MB This is the total amount of disk space taken by the 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>"));

Remarks

- 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!

Conclusion


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


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

By Alon Havivi   Popularity  (12733 Views)
Picture
Biography - Alon Havivi

Alon Havivi is a Microsoft Certified Technology Specialist, working as SharePoint Consultant / Developer at e-office. Specialized in SharePoint 2007/2010 and SharePoint Online (Office 365). With more than 10 years of experience in analysis, design and development complex Internet and Intranet portals using the latest Microsoft tools and practices, such as C# .NET 4.0, Silverlight and Windows Azure platform. Besides professional work, I write articles/blog and publish open source projects on CodePlex
View Alon Havivi's professional profile on LinkedIn. View Alon Havivi's projects on CodePlex. View Alon Havivi's articels on Eggheadcafe. Follow Alon Havivi on Twitter Connect with Alon Havivi via Facebook View Alon Havivi's Blog Subscribe to Alon Havivi RSS Feed