Item-Level Auditing with SharePoint Server 2007

Summary: Learn about the auditing support built into Windows SharePoint Services 3.0 and Microsoft Office SharePoint Server 2007, and how to extend this support with the custom Item-Level Auditing solution. (29 printed pages)

Ted Pattison, Critical Path Training, LLC (Microsoft MVP)

Joanna Bichsel, Microsoft Corporation

Updated: March 2010

Applies to: 2007 Microsoft Office system, Microsoft Office SharePoint Server 2007, Windows SharePoint Services 3.0.

Download the code sample that accompanies this article: MOSSSampleItemLevelAuditing.exe.

Contents

  • Auditing with SharePoint Server 2007

  • Built-in Auditing Support in Windows SharePoint Services and Office SharePoint Server 2007

  • Item-Level Auditing: A Custom Auditing Solution

  • Displaying Entries from the Audit Log

  • Item-Level Auditing

  • Writing Custom Audit Entries

  • Office SharePoint Server 2007 Value-Added Support for Auditing

  • Office SharePoint Server 2007 Support for Reporting on Auditing

  • Generating an Excel Workbook with Audit Log Information

  • Server-Side Workbook Rendering Using Excel Services

  • Packaging the Item Auditing Solution for Deployment

  • Conclusion

  • Additional Resources

Auditing with SharePoint Server 2007

Many companies and government agencies have policies and regulations that require them to track carefully where and how users gain access to important records and documents. In particular, they need to maintain audit logs that detail events that track data such as which users viewed and updated records and documents, and when these events occurred.

Previously, Windows SharePoint Services 2.0 did not provide extensive audit support. For example, you could not use components to audit access to pages or access to items within lists. Furthermore, event handlers in Windows SharePoint Services 2.0 document libraries fired only when a document within a library was updated. Therefore, event handlers in Windows SharePoint Services 2.0 document libraries provided no support for auditing events that occur when users view documents.

In response to user requests, the latest version of Microsoft SharePoint Products and Technologies now provides many enhancements for auditing support, all included within the 2007 Microsoft Office system. Both Windows SharePoint Services 3.0 and Microsoft Office SharePoint Server 2007 provide support for auditing user access to pages, content, and documents, and include auditable events such as viewing and updating.

Built-in Auditing Support in Windows SharePoint Services and Office SharePoint Server 2007

Windows SharePoint Services 3.0 introduces built-in audit logging you can enable and configure at the scope of a site collection. When you enable auditing, Windows SharePoint Services writes audit event entries into an internal audit log table that is stored within the content database. The audit event entries for a site collection are stored with all other Windows SharePoint Services content such as list items, documents, and Web Part customizations.

Note

The following inaccurate statement was removed from the originally published (April 2007) version of this article: "When you back up a site collection, any information in the audit log is also backed up." For Office SharePoint Server 2007, audit log backup and restore is included with content database backup and restore. To protect an audit log only, we recommend that you export all audit log data to a Microsoft Office Excel file by running a custom audit report with no filtering.

While Windows SharePoint Services provides the basic mechanisms for auditing, it does not provide any built-in support for users to enable auditing. Because Windows SharePoint Services does not provide the functionality to see entries that are written to the audit log, a developer must write code to enable the Windows SharePoint Services audit logging facility. A developer must also supply code and a user interface to read entries from the audit log and display this data to the site’s users. The sample code that accompanies this article shows how to create this type of custom auditing solution.

While Windows SharePoint Services provides the basic auditing mechanisms, Office SharePoint Server 2007 adds value by supplying an administrative user interface that allows auditing at the site collection level. Office SharePoint Server 2007 goes even further and enables auditing at the list or document library level, and control over what types of events should be recorded in the audit log. Office SharePoint Server 2007 also provides a reporting function that uses Microsoft Office Excel workbooks to display and keep records of audit logs. Therefore, Office SharePoint Server 2007 enables you to take advantage of the Windows SharePoint Services auditing support without having to write any custom code.

Item-Level Auditing: A Custom Auditing Solution

This article is accompanied by a custom solution named Item-Level Auditing (MOSSSampleItemLevelAuditing.exe) that demonstrates techniques to extend the auditing support that is available in both Windows SharePoint Services and Office SharePoint Server 2007. First, the custom solution shows how to extend Windows SharePoint Services by enabling audit logging programmatically. It also provides a user interface component that displays contents of the audit log to Windows SharePoint Services site users. This custom solution also demonstrates how to extend Office SharePoint Server 2007 by adding greater support for reporting using the new Office Open XML Formats to generate Excel workbooks with auditing information. The Item-Level Auditing solution also shows how to solve a problem that is not currently addressed by either Windows SharePoint Services or Office SharePoint Server 2007. Specifically, this custom solution demonstrates how to display and report on the audit log entries that are associated with a single item in a list or a single document in a document library.

All the sample code for this solution is contained within a Microsoft Visual Studio 2005 project named ItemAuditing.csproj. Figure 1 shows a view of the various files that make up the solution. These project files use many new development techniques that are introduced in Windows SharePoint Services 3.0, such as custom application pages, features, and solution packages.

Figure 1. ItemAuditing project

Compiled ItemAuditing.dll assembly

Before you begin, ensure that the following are installed on your development computer:

  • Microsoft .NET Framework 3.0 (RTM version)

  • Microsoft Visual Studio 2005

  • Microsoft Windows SharePoint Services 3.0

  • Microsoft Office SharePoint Server 2007

Next, download the MOSSSampleItemLevelAuditing.exe file and extract the contents to a directory on your development computer. You can now follow along with the instructions in Setup Document.docx to get the sample solution running. After you complete the instructions in the setup document, you have the complete Visual Studio 2005 project on your computer. The remainder of this article explains the various pieces that make this solution work.

The Item-Level Auditing solution consists of the following:

  • A Windows SharePoint Services feature named Item Auditing

  • Two application pages named AuditLogViewer.aspx and ItemAudit.aspx

  • A custom handler named AuditLogWorkbook.ashx

  • An assembly named ItemAuditing.dll

We start by examining the Item Auditing feature and its feature.xml file.

<Feature Id="AA929AFF-4602-4d7f-A501-B80AC9A4BB52" 
   Title="Item Auditing"
   Description="This feature allows user to view the audit history of documents."
   ImageUrl="CALVIEW.GIF"
   Version="1.0.0.0"
   Scope="Site" 
   ReceiverAssembly="ItemAuditing, Version=1.0.0.0, Culture=neutral, PublicKeyToken=7fd3ed697555604d"
   ReceiverClass="ItemAuditing.ItemAuditingFeatureReceiver"
   xmlns="https://schemas.microsoft.com/sharepoint/">

    <ElementManifests>
      <ElementManifest Location="elements.xml" />
    </ElementManifests>

</Feature>

A Windows SharePoint Services 3.0 feature is always installed at server farm-level scope. However, you can set the activation scope for a feature to one of four levels. A feature can be designed to activate within the scope of a site, a site collection, a Web application, or a server farm. In our case, the Item Auditing feature was designed with its activation scope at the site collection level. You can see from the Feature element, described previously, that the Scope attribute is set to Site, which means it is activated within the scope of a site collection. If you want to create a feature that is activated within the scope of a site, rather than a site collection, you should assign the Scope attribute a value of Web instead of Site.

After the Item Auditing feature is installed within a Windows SharePoint Services server farm, it is available for activation within any of the site collections within the server farm. If you navigate to the Site Collection Features administration page, you should be able to activate the feature as shown in Figure 2.

Figure 2. Site Collection Features administration page

Activated Item Audting feature

If you examine the Feature element within the feature.xml file, you notice that there is a ReceiverAssembly attribute and a ReceiverClass attribute. This technique allows the feature to reference an assembly with a class that provides an event handler that fires whenever the feature is activated. This type of feature-specific event handling is made possible by authoring a custom class that inherits from the SPFeatureReceiver class, as shown in the ItemAuditingFeatureReceiver class in the following code.

using System;
using Microsoft.SharePoint;

namespace ItemAuditing {  

  public class ItemAuditingFeatureReceiver : SPFeatureReceiver {

    public override void FeatureActivated(SPFeatureReceiverProperties properties)  {
      // The feature activation code goes here.
    }

    public override void FeatureInstalled(SPFeatureReceiverProperties properties) { }
    public override void FeatureUninstalling(SPFeatureReceiverProperties properties) { }
    public override void FeatureDeactivating(SPFeatureReceiverProperties properties) { }

  }
} 

The feature receiver class that is created for the Item-Level Auditing solution provides code only within the FeatureActivated event handler method. However, three other methods can be written to provide event-handling behavior when a feature is installed, and when it is deactivating and uninstalling.

When the Item Auditing feature is activated, its FeatureActivated event handler method executes code that is required to turn on the Windows SharePoint Services auditing facility. The code within the event handler does this by obtaining a reference to an SPSite object for the current site collection, and then setting the proper audit flags on the Audit property of the SPSite object.

public override void FeatureActivated(SPFeatureReceiverProperties properties)  {

  using (SPSite siteCollection = (SPSite)properties.Feature.Parent) {
  // Turn on auditing flags.
  siteCollection.Audit.AuditFlags = SPAuditMaskType.All;
  siteCollection.Audit.Update();

  // Modify title of top-level site.
    using (SPWeb site = siteCollection.RootWeb) {
      site.Title += " (audited)";
      site.Update();

      SPListTemplate template = site.ListTemplates["Document Library"];
      Guid docLibID = site.Lists.Add("AuditLogs", "Library for Audit Log Workbooks", template);
      SPList docLib = site.Lists[docLibID];
      docLib.OnQuickLaunch = true;
      docLib.Update();
    }
  }
}

As the code shows, the FeatureActivated event handler also updates the Title of the top-level site to give you a visual indication that it has run successfully. There is also code to create a document library named Audit Logs in the top-level site. This document library is used as a repository for Office Excel workbooks that contain audit logging data. More information is included in later sections of this article.

For now, let us concentrate on code that enables logging at the site collection level. The Audit property exposes an SPAudit object, which provides an AuditFlags property. The AuditFlags property is based on the SPAuditMaskType enumeration and can simply be assigned with a value of All to turn on all auditing facilities. Also, notice that a call to the Update method must be made after modifying the audit flags on an SPAudit object.

siteCollection.Audit.AuditFlags = SPAuditMaskType.All;
siteCollection.Audit.Update();

You have just seen how to enable auditing for an entire site collection. However, you should understand that this can be a very expensive undertaking for a large site collection with a significant volume of traffic. For example, if you have a large site collection with many active users, or if you are auditing frequent operations such as page reads, then the audit log will fill with data very quickly.

In some cases, such as when you are designing a repository for classified documents in a government agency, you might want to record every auditable event. This might lead you to enable all auditing events at the site collection level. You might also consider partitioning classified documents out into their own site collection, so you do not have to record the same level of audit information when users are viewing non-classified documents.

There might be other scenarios when you want more control over what is audited within the scope of a single site collection. For example, you might want to audit only update operations but not read operations. Perhaps you are required to audit access to one document library but not another within the same site. Fortunately, Windows SharePoint Services provides you with the means to be more selective about how you enable auditing. For example, you can turn on auditing for a single document library.

using (SPSite siteCollection = new SPSite("http://LitwareServer1")) {
  using (SPWeb site = siteCollection.OpenWeb()) {
    SPList list = site.Lists["Proposals"];
    list.Audit.AuditFlags = SPAuditMaskType.All;
    list.Audit.Update();
  }
}

Also, notice that when you enable auditing, you do not have to record every type of auditable event. There are audit flags that let you control which types of auditing events you want Windows SharePoint Services to record. The enumeration values of SPAuditMaskType are bitwise flags that can be used together to configure the exact set of auditing events you would like to capture.

list.Audit.AuditFlags = SPAuditMaskType.View |
                        SPAuditMaskType.Update |
                        SPAuditMaskType.Delete;
list.Audit.Update();

The previous code is an example of a subset of event types you can include in the audit log. Following is a complete list of the different enumeration values that are available.

SPAuditMaskType.CheckIn
SPAuditMaskType.CheckOut
SPAuditMaskType.ChildDelete
SPAuditMaskType.Copy
SPAuditMaskType.Delete
SPAuditMaskType.Move
SPAuditMaskType.ProfileChange
SPAuditMaskType.SchemaChange
SPAuditMaskType.Search
SPAuditMaskType.SecurityChange
SPAuditMaskType.Undelete
SPAuditMaskType.Update
SPAuditMaskType.View
SPAuditMaskType.Workflow

Displaying Entries from the Audit Log

Now that you have seen how to enable audit logging, we can focus on how to display event information from the audit log to site users. The Item-Level Auditing solution provides an application page named AuditLogViewer.aspx. The purpose of this page is to show the entire contents of the site collection’s audit log to site administrators.

The Item-Level Auditing solution includes a file named elements.xml, which provides two CustomAction elements. The purpose of the CustomAction element is to add a menu item to the Site Actions menu that enables a site administrator to navigate to AuditLogViewer.aspx.

<!-- Add Command to Site Actions Dropdown -->
<CustomAction Id="SiteActionsToolbar"
  GroupId="SiteActions"
  Location="Microsoft.SharePoint.StandardMenu"
  Sequence="1001"
  Title="Audit Log Viewer"
  Description="This page allows you to see the audit log."
  RequireSiteAdministrator="True"
  ImageUrl="/_layouts/images/LTTXTBOX.GIF">
    <UrlAction Url="~sitecollection/_layouts/AuditLogViewer.aspx"/>
</CustomAction>

This CustomAction element was designed to add the menu item shown in Figure 3. The CustomAction element was defined with a RequiresSiteAdministrator attribute that was assigned a value of true. Because the hosting Item Auditing feature is scoped to the site collection, this means the menu item is shown only to users who are site collection owners. You can also see that the URL attribute of the UrlAction element was configured with a URL that contains the ~sitecollection token. That means that Windows SharePoint Services can dynamically parse together a URL to redirect the user to this application page based on the root path to the current site collection.

Figure 3. Custom menu item

Custom menu item added to the Site Actions menu

When site collection owners click the menu item to navigate to the Audit Log Viewer page, they are redirected to the custom application page named AuditLogViewer.aspx located in the virtual layouts directory. This custom application page reads all the event information from the site collection’s audit log and displays it by using an SPGridView control, as shown in Figure 4.

Figure 4. Audit Log Viewer page

Custom application page shows audit log entries

The creation of custom application pages such as AuditLogViewer.aspx provides a valuable technique for adding user interface components to a Windows SharePoint Services solution that contains custom code. Unlike site pages (such as default.aspx), a custom application page is deployed once per server farm and cannot be customized on a site-by-site basis. Because application pages run out of the virtual _layout directory and are compiled into a single assembly DLL used across all sites within the server farm, they perform better than site pages. Application pages—unlike site pages—also provide you with the ability to add code to them directly.

Custom application pages are often created to link to application.master, the main master page file used by the standard Windows SharePoint Services application pages. You should typically write application pages to inherit from a base class defined inside the Microsoft.SharePoint assembly named LayoutsPageBase. Here is a starting point with the basic layout for a custom application page such as AuditLogViewer.aspx.

<%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral,                  
                   PublicKeyToken=71e9bce111e9429c"%> 
<%@ Page Language="C" MasterPageFile="~/_layouts/application.master" 
         Inherits="Microsoft.SharePoint.WebControls.LayoutsPageBase"  %>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Import Namespace="System.Data" %>
<%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"
    Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<script >
    protected override bool RequireSiteAdministrator {
        get { return true;}
    }
    protected override void OnLoad(EventArgs e)  {
        // Code goes here.
    }    
</script>

<asp:Content ID="contentMain" ContentPlaceHolderID="PlaceHolderMain" >
    <asp:Button ID="cmdRefreshPage"  Text="Refresh Page" 
                    OnClick="cmdRefreshPage_Click" />
    <asp:Button ID="cmdDeleteAllEntires"  Text="Clear Audit Log" 
                    OnClick="cmdDeleteAllEntires_Click" />
    <hr /> 
    <SharePoint:SPGridView ID="SPGridView1"  AutoGenerateColumns="False" />
</asp:Content>

Code within this page overrides the RequireSiteAdministrator property and returns a value of true. This enables you to lock down an application page in a much more secure manner. After you override the RequireSiteAdministrator property in this manner, you have created a significantly more secure environment. When a user who is not a site administrator tries to navigate to this application page, Windows SharePoint Services redirects the user to its standard access denied page.

This definition of AuditLogViewer.aspx has a script block at the top with code that programs against the object model. When you are programming a page like this within Visual Studio 2005, you benefit from conveniences such as color-coding and IntelliSense. However, you must be sure to add the correct @Assembly directive to the top of the page. You should modify the @Assembly directive that is shown earlier with information for the Microsoft.SharePoint assembly.

Application pages are useful because they provide quick and easy access to the Windows SharePoint Services object. After you created an application page and provide an overridden implementation of the OnLoad method as shown earlier, you can obtain entry points into the Windows SharePoint Services object model in a site-specific context using the following code.

protected override void OnLoad(EventArgs e)  {
  using (SPSite siteCollection = SPContext.Current.Site) {
  SPWeb site = SPContext.Current.Web;
  }
}    

The ability to obtain site-relative context makes it possible to write far more powerful application pages. An application page can behave differently depending on your site access path. When you navigate to an application page through the context of one site, it typically behaves differently than if you navigated to the page through the context of another site.

We will now discuss code inside the OnLoad event that is defined within AuditLogViewer.aspx. The code is written to read the audit log of the current site collection by creating an SPAuditQuery object and then passing this object as a parameter in a call to the GetEntries method. A call to the GetEntries method returns an SPAuditEntryCollection object, which makes it possible to examine an entry in the target audit log using a simple for…each loop.

using (SPSite siteCollection = SPContext.Current.Site) {
  SPAuditQuery wssQuery = new SPAuditQuery(siteCollection);
  SPAuditEntryCollection auditCol = siteCollection.Audit.GetEntries(wssQuery);

  foreach (SPAuditEntry entry in auditCol) {
      // inspect entry
  }
}

As you examine the entire implementation of the OnLoad method inside AuditLogViewer.aspx, you see that it is written to display audit event information using the SPGridView control. This approach makes the display of audit entries consistent with the look and feel of other aspects of Windows SharePoint Services. Populating the SPGridView control with audit entries involves creating a Microsoft ADO.NET DataTable object and writing the audit log information inside it. The technique of creating and populating a DataTable object makes it easy to bind the information to the SPGridView control.

Notice that Windows SharePoint Services tracks the times for audit events based on Coordinated Universal Time (Greenwich Mean Time). After the code in the sample application retrieves time, it is converted to the local time of the Web server by making a call to the ToLocalTime method that is supplied by the DateTime class of the Microsoft .NET Framework. However, if, for example, your front-end Web server and your user are in different time zones, be aware that this sample application provides no support to translate audit event times to the time zone of the user.

As you examine the implementation of the code that reads information from the Windows SharePoint Services audit log, observe how it retrieves user names. The Windows SharePoint Services audit log tracks user IDs but not user names. After you obtain a user ID, you can retrieve the associated user name by using the GetByID method on the SiteUser collection, which is exposed as a property of the top-level site. Make sure you use the SiteUsers collection; do not confuse this with the User collection, which does not provide the same results.

Private string GetUserNameById(int UserId, SPWeb site) {
  return site.SiteUsers.GetByID(UserId).Name;
}

Some customers might want to export parts of their audit logs to other external stores such as a database or an Excel spreadsheet. The sample application also provides a command button that enables the user to clear out the Windows SharePoint Services audit log. This is done by calling the DeleteEntries method on the target SPAudit object and passing a parameter with the time and date before which you should clear all audited entries. Before the call to DeleteEntries is made, you can supply additional code to back up the audit data safely to an external store of your choosing to help ensure that no important auditing event data is lost.

We recommend that you do not use this button as currently implemented unless custom logic is added to OnClick to do the export before the delete happens. This logic was not added here because different organizations might want different types of backups.

// Add code here to export audit log into external store.
SPSite siteCollection = SPContext.Current.Site;
siteCollection.Audit.DeleteEntries(DateTime.Now.ToLocalTime().AddDays(1));
siteCollection.Audit.Update();

When you have a custom auditing solution running inside a large site collection with heavy traffic, saving the audit log into separate files and then clearing it out can be important. You can often manage space more effectively by using this kind of approach.

Item-Level Auditing

Now that you have seen the basic code to enable auditing in Windows SharePoint Services and to display auditing event information to users, we can examine how to add required support for users to see auditing information on a per-document or per-item basis. We start by creating a custom menu item within the Edit Control box (ECB) menu for all documents within the site collection where the Item Auditing feature is activated. The goal is for the ECB menu of each document within the site collection to provide a menu item that looks like this.

Figure 5. Custom menu item in the ECB menu

Custom menu item added to ECB menu

A custom menu item such as the one shown in Figure 5 was created by adding a second CustomAction element to the elements.xml file of the Item Auditing feature. The CustomAction element differs from the one shown earlier because it has a Location attribute that is assigned a value of EditControlBlock. This attribute value instructs Windows SharePoint Services to add the menu item to the ECB menu for a specific set of items or documents. The CustomAction element defines the target set for the menu item using a RegistrationType attribute with a value of List and a RegistrationID attribute with a value of 101, the identifier for standard Windows SharePoint Services document libraries. Notice that this sample application provides item-level auditing only for documents within document libraries. However, it could easily be extended to support item-level auditing on any other type of list.

<!-- Per Item Dropdown (ECB) Link -->
<CustomAction Id="ItemAuditing.ECBItemMenu"
  RegistrationType="List" 
  RegistrationId="101"
  ImageUrl="/_layouts/images/GORTL.GIF"
  Location="EditControlBlock"
  Sequence="300"
  Title="View Audit History">
    <UrlAction Url="~site/_layouts/ItemAudit.aspx?ItemId={ItemId}&amp;ListId={ListId}"/>
</CustomAction>

You should also observe the value of the URL attribute within the UrlAction element. The URL attribute is configured as follows.

~site/_layouts/ItemAudit.aspx?ItemId={ItemId}&amp;ListId={ListId}

First, notice that this URL was configured to redirect the user to the second custom application page named ItemAudit.aspx. You should also notice that there are three tokens inside this URL, which are dynamically replaced by Windows SharePoint Services at run time. The ~site token is replaced with the URL that points to the current site. The {ListId} token is replaced with the GUID that identifies the current document library. The {ItemId} token is replaced with the integer identifier of the document. These last two tokens are used to parse together a query string that can ItemAudit.aspx the information it needs to access the target document and the document library that contains it using the Windows SharePoint Services object model.

When users select the View Audit History menu from the ECB menu for a particular document, they are redirected to the custom application page named ItemAudit.aspx. They are then presented with a view of the document, which details audited events that were written to the log as shown in Figure 6.

Figure 6. Custom ItemAudit.aspx page

ItemAudit.aspx displays audit event information

Now that you have seen the user’s perspective of the ItemAudit.aspx page, we can examine the code behind it. The basic structure of this application page is similar to AuditLogViewer.aspx. However, unlike AuditLogViewer.aspx, ItemAudit,aspx was not designed for exclusive use by site administrators. Other users, depending on their requirements, can also be allowed to view this code.

First, let us examine the code that is required to extract the list item ID for the document and the GUID for the document library from the query string. As you can see from the earlier code, this is accomplished by using a standard programming technique involving the QueryString property of the ASP.NET Request object.

SPSite siteCollection = SPContext.Current.Site;
SPWeb site = SPContext.Current.Web;

string ListId = Request.QueryString["ListId"];
string ItemId = Request.QueryString["ItemId"];

SPList list = site.Lists[new Guid(ListId)];        
SPListItem item = list.Items.GetItemById(Convert.ToInt32(ItemId));

txtListId.Text = ListId;
txtItemTitle.Text = list.Title;
txtItemId.Text = ItemId;
txtItemTitle.Text = item.Title;

This code listing also shows that with a little extra code involving the Windows SharePoint Services object model, you can create an SPList object and an SPListItem object to program against the document library and the target document.

There is code written in ItemAudit.aspx that queries the audit log. This is different from the code that was shown earlier in AuditLogViewer.aspx because it only queries for the audit events that are specific to a single document. This is done by calling the RestrictToList method on the SPAuditQuery object before calling GetEntries and passing the SPListItem object that is associated with the target document.

SPAuditQuery wssQuery = new SPAuditQuery(siteCollection);
wssQuery.RestrictToListItem(item);            
SPAuditEntryCollection auditCol = site.Audit.GetEntries(wssQuery);

foreach (SPAuditEntry entry in auditCol) {
  // get info from audit log
}

Now that you have seen how to query the audit log for events that are specific to a single document, we can address one of the more complicated aspects of the Item Auditing feature. The page ItemAudit.aspx was explicitly designed and implemented to display audit information to all users including those who are not site administrators. However, this situation might be problematic, because the Windows SharePoint Services object model requires administrator privileges for any code that calls to the GetEntries method to query the audit log. Any code running under the identity of a user without site administrator privileges fails with an access denied error.

Most of the time, you probably want to prevent users who are not site administrators from viewing information within a site collections audit log. That is why the Windows SharePoint Services object model imposes this restriction. However, the Item Auditing feature enables you to create exceptions. In this particular scenario, we want to display auditing information to any site user. Therefore, the code within ItemAudit.aspx must use a special programming technique to elevate its privileges before reading from the Windows SharePoint Services audit log.

The code inside ItemAudit.aspx was written to interact with the SPSecurity class to make a call to a static method named RunWithElevatedPrivileges. This method accepts a single parameter, which is a delegate. If you want to write this code in a concise fashion, you can use anonymous methods as shown in the following code example.

SPSecurity.RunWithElevatedPrivileges( delegate()  {
  // Code to run with elevated privileges goes here.
  // This code runs as SHAREPOINT/SYSTEM account.
});

After you elevate the privileges of the code using this technique, you must create a new instance of the SPSite class and the SPWeb class. You cannot use the objects that are available through the Current property because they were already created under the security context of the current user. The following code shows how to create a new SPSite object and a new SPWeb object using the elevated security context.

SPSite siteColl = SPContext.Current.Site;
SPWeb site = SPContext.Current.Web;

SPSecurity.RunWithElevatedPrivileges(delegate()  {
  using (SPSite ElevatedSiteCollection = new SPSite(siteColl.ID)) {
    using (SPWeb ElevatedSite = ElevatedSiteCollection.OpenWeb(site.ID)) {
      // Now program against elevated SPSite object and SPWeb object.
    }
  }
});

The previous code also wraps the creation of the new SPSitex object and the new SPWeb object in the Microsoft Visual C# using construct so that objects are disposed of properly. Now, we examine a longer code listing that demonstrates how to query the audit log for the target document using elevated privileges.

SPSite siteColl = SPContext.Current.Site;
SPWeb site = SPContext.Current.Web;

SPSecurity.RunWithElevatedPrivileges(delegate()  {
  using (SPSite ElevatedSiteCollection = new SPSite(siteColl.ID)) {
    using (SPWeb ElevatedSite = ElevatedSiteCollection.OpenWeb(site.ID)) {
      
      SPList list = ElevatedSite.Lists[new Guid(ListId)];
      SPListItem item = list.Items.GetItemById(Convert.ToInt32(ItemId));
      
      SPAuditQuery wssQuery;
      SPAuditEntryCollection auditCol;

      wssQuery = new SPAuditQuery(ElevatedSiteCollection);
      wssQuery.RestrictToListItem(item);            
      auditCol = ElevatedSite.Audit.GetEntries(wssQuery);

      foreach (SPAuditEntry entry in auditCol)  {
        // Query audit log with elevated privileges.
      }
    }
  }
});

Writing Custom Audit Entries

You have already seen that the ItemAuditing.aspx page is able to display audit entries that are automatically written to the audit log by Windows SharePoint Services. However, there are times when you want to solve a particular problem by writing custom entries to the audit log. For example, you could add code to the OnLoad event for an application page, which examines the user's IP address, and then writes a custom audit log entry if the IP address is not recognized within the corporate local area network (LAN). As another example, you could supply code to write a custom audit log entry that records events for a custom workflow.

When you use the Item-Level Auditing solution, you must write custom audit entries to record when a user examines the audit log of a particular document. For example, if a user clicks on the View Audit History menu item and is redirected to ItemAudit.aspx, Windows SharePoint Services auditing does not record that event. Therefore, you must use the following code to write a custom audit entry.

SPList list = site.Lists[new Guid(ListId)];
SPListItem item = list.Items.GetItemById(Convert.ToInt32(ItemId));
item.Audit.WriteAuditEvent(SPAuditEventType.Custom, 
                           "CustomViewAuditEvent",  // SoureName
                           "<myXml>MyData</myXml>"  // Any arbitrary XML data
                           );

Earlier, you learned that code must run with elevated privileges to read from the audit log. However, the same is not true for code that is writing to the audit log, which does not require elevated permissions. Moreover, if you elevate permissions before writing to the audit log, the audit log shows the users as SHAREPOINT\SYSTEM instead of the current user.

After you write a custom audit entry, you can later read it from the event log as you would read any other standard audit event that is written by the Windows SharePoint Services auditing logging facility. However, you can parameterize custom events to store relevant information in the audit log. The earlier example demonstrated how to assign a Source Name and pass custom XML data when writing a custom event. This information is then available to you when reading audit events by using the SourceName property and the EventData property of the SPAuditEntry class.

foreach (SPAuditEntry entry in auditCol)  {
  if (entry.SourceName == "CustomViewAuditEvent") {
    string myXml = entry.EventData;
    // process custom event
  }
}

Office SharePoint Server 2007 Value-Added Support for Auditing

Office SharePoint Server 2007 provides two valuable dimensions to assist companies that need to audit user access to content within Windows SharePoint Services and Office SharePoint Server 2007 sites. First, Office SharePoint Server 2007 provides an administrative user interface that allows you to enable and configure auditing without having to write any code. Second, Office SharePoint Server 2007 provides a reporting aspect. More specifically, Office SharePoint Server 2007 makes it possible to generate Excel workbooks that contain the information about audit events within the Windows SharePoint Services audit log. The next few sections of this article describe how Office SharePoint Server 2007 provides auditing support at the site collection level and at more granular levels as well.

We start by examining the built-in Office SharePoint Server 2007 support for configuring auditing within a site collection. Go to the site setting page of any site within a server farm that has Office SharePoint Server 2007 installed. You find that many links are added here by Office SharePoint Server 2007-specific features. Inside the Site Collection Administration section, locate a link with the caption Site Collection audit settings as shown in Figure 7.

Figure 7. Site Collection Administration

Built-in support for configuring audit settings

Clicking the Site collection audit settings link opens an Office SharePoint Server 2007-specific application page named AuditSettings.aspx. This page provides a user interface (Figure 8) for enabling and configuring auditing settings for the current site collection. As shown in the Figure 8, the AuditSettings.aspx application page makes it possible to configure site collection auditing at various levels of granularity.

Figure 8. AuditSettings.aspx page

AuditSettings.aspx to configure audit settings

In addition to configuring auditing at the site collection level, Office SharePoint Server 2007 also enables you to configure auditing at a much more detailed level. This support is made possible through the information management policies feature that is included in Office SharePoint Server 2007.

An information management policy consists of a set of rules that a site administrator can define and then apply to a certain type of content. The rules for an information management policy are created and configured in terms of policy features. Features that are provided by default with Office SharePoint Server 2007 include support for auditing, expiration, document labels, and bar codes. The programming model for policy features is also extensible, which allows developers to create their own custom policy features. For developer resources, see the appendix at the end of this article.

Office SharePoint Server 2007 enables you to create and configure an information management policy for a specific instance of a list or document library. You can also create an information management policy for a content type, which then applies to list items and documents in any list or document library that is defined in terms of that content type. The ability to apply information management policies to content types is valuable because it provides more granular control than site collection level auditing. It also mitigates the need to configure audit settings at the level of list instances or document library instances.

Office SharePoint Server 2007 also enables you to define information management policies at the site collection level. This provides extra manageability, because you can define an information management policy once within a site collection, and then apply it to your choice of lists, document libraries, and content types within that site collection.

The Site Settings page has a link with the caption Site collection policies. If you click this link, it opens an application page named Policylist.aspx. This allows you to configure a custom policy, which is scoped to the current site collection. Figure 9 shows an example of a custom policy that was created to configure a standard set of audit events that can be applied to any list, document library, or content type.

Figure 9. Custom policy page

Custom policy to configure audit events

After you define a custom policy at the site collection level, you can then go to the List Settings page for a list or document library. You find a link with the caption of Information management policy settings, as shown in Figure 10. If you click this link, it redirects you to an application page named Policy.aspx, which enables you to create a new information policy. Alternatively, you can apply a policy that is already defined at the level of the site collection. On a server farm with Office SharePoint Server 2007 installed, the Windows SharePoint Services page that enables you to create and configure content types also provides a link that takes you to Policy.aspx. Here you can also create or apply policies.

Figure 10. List Settings page

Page for configuring information management policy

Figure 11 shows the options that are available through the application page policy.aspx. This is a simple way to apply a custom policy created at the site collection level. It also enables you to define and configure a unique policy that applies only to the current list, document library, or content type. In many cases it promotes a higher level of manageability. For example, you can create all of your policies at the site collection level, and then simply apply your policies to the appropriate lists, document libraries, and content types.

Figure 11. Applying custom policies

Applying custom policies

Office SharePoint Server 2007 Support for Reporting on Auditing

We saw how Office SharePoint Server 2007 and information management policies could help you configure Windows SharePoint Services auditing settings. Now let us consider the reporting side. Office SharePoint Server 2007 gives you the ability to generate several types of auditing reports. You can view different types that are available by clicking the link on the Site Setting page with the caption Audit log reports. This link opens an application page named Reporting.aspx as shown in Figure 12.

Figure 12. Reporting.aspx page

Reporting.aspx page

When you click one of the report links shown in Figure 12, Office SharePoint Server 2007 generates an XML file in response, which is returned to the user. The contents of this file are based on the XML language known as Excel Markup Language (ExcelML). When Office SharePoint Server 2007 generates an ExcelML document on the server and passes it back to the user, the user is then given the opportunity to open the ExcelML document as a standard Office Excel workbook.

Originally created for Microsoft Office Excel 2000, the ExcelML language was later enhanced for Microsoft Office 2003. One of the driving factors for the Office team to create ExcelML was to support server-side Excel workbooks without requiring the server to run a copy of Microsoft Excel. For example, a server-side component can use a standard XML parser to generate an XML document in the following form.

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook>
  <Worksheet>
    <Table>
      <Row>
        <Cell>
          <Data Type="String">Mission Critical Data Goes Here</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
</Workbook>

After the ExcelML document is generated, it can be opened directly within Microsoft Office Excel. The Office SharePoint Server 2007 team has used this technique for their audit reporting. When you click a link shown on the Reporting.aspx page to generate an auditing report, Office SharePoint Server 2007 returns an ExcelML document and the user is presented with the dialog box shown in Figure 13.

Figure 13. File Download dialog box

File Download dialog box

If you choose to open a report directly in Excel, you can view the auditing information as shown in Figure 14. At this point, you can also save the workbook using any of the supported Excel file formats. This approach makes it possible to open and view these audit reports using either Excel 2003 or Excel 2007.

Figure 14. Auditing information in Excel 2007

Opening and viewing audit reports in Excel

Office SharePoint Server 2007 provides standard reports, which list the users who have viewed and updated list items and documents, and which users modified other site components such as list definitions and security settings. Office SharePoint Server 2007 also provides considerable flexibility for parameterization, which is useful if you want to run informal reports. For example, you can run a custom report and fill in the criteria as shown in Figure 15.

Figure 15. Custom report

Run a custom report to specify auditing criteria

Generating an Excel Workbook with Audit Log Information

Prior to the release of the 2007 Microsoft Office system, Microsoft products such as Word, Excel, and PowerPoint relied on a default file structure based on binary format. These file formats were very hard to read and modify unless you used the object model of the hosting Office application such as Word or Excel. As a result, companies tried to run Office desktop applications on the server, which created problems with scalability and robustness.

Office 2000 and Office 2003 added a few capabilities for creating Excel workbooks and Word documents using XML, such as the ability to create Excel workbooks with ExcelML.

Now, with the 2007 Microsoft Office system, Microsoft has taken this improvement much further by adopting Office Open XML Formats for Word, Excel, and PowerPoint documents. Office Open XML Formats are new file standards for creating composite documents containing multiple inner XML files that factor out content from other aspects of the document, such as formatting instructions, data, and code.

The top-level file in the Office Open XML Formats is known as a package and is structured using standard .zip file technology. The internal files contained within a package are known as parts. Many parts within Word, Excel, and PowerPoint files contain XML structures in accordance with published XML schemas. Other parts within a package can consist of binary files for items such as graphics, audio clips, and videos.

A major goal of the Office Open XML Formats is to provide a standard approach for reading, manipulating, and generating documents in server-side scenarios, where you do not have the option of using the object model of a desktop application such as Word or Excel.

Consider a Windows SharePoint Services site scenario where you created and configured an event handler to fire whenever a user uploads a new Word document. The new Office Open XML Formats make it significantly easier to extract data or perform hygiene on the document such as removing comments and personal information. You can also leverage Office Open XML Formats to develop server-side components that generate Word documents and Excel workbooks on the fly using data pulled from content sources such as a SharePoint list or, in the case of our custom solution, the Windows SharePoint Service audit log.

To start working with the Office Open XML Formats, you need to learn how to program against the new .NET Framework 3.0 packaging API. This is the recommended API to use when opening and creating packages. You must also learn the specific package structure and XML schemas for your particular type of Office document. These details change as you move between Microsoft Office Word 2007, Office Excel 2007, and Microsoft Office PowerPoint 2007 documents. Microsoft has started a developer community effort around the Office Open XML Formats at Open XMLDeveloper.org.

The Item-Level Auditing solution provides code that generates Excel workbooks using the Office Open XML Formats. You can inspect this code within the source file named WorkbookFactory.cs. Inside this source file, you find a class named AuditLogWorkbook, which programs against the .NET Framework 3.0 packaging API and performs all the tasks required to add information from the Windows SharePoint Services audit log to a new Excel workbook.

Notice that the source code from WorkbookFactory.cs is compiled into the assembly named ItemAuditing.dll. To access the AuditLogWorkbook class, the page definition requires an assembly directive.

<%@ Assembly Name="ItemAuditing, Version=1.0.0.0, Culture=neutral,PublicKeyToken=7fd3ed697555604d" %>
<%@ Page Language="C" MasterPageFile="~/_layouts/application.master" 
         Inherits="Microsoft.SharePoint.WebControls.LayoutsPageBase"  %>

<%@ Import Namespace="ItemAuditing" %>

The custom application page ItemAudit.aspx provides a command button with the caption Generate Excel Workbook, which allows a user to generate an Excel workbook on demand with the audit event information for a specific document. This command button redirects the user to a custom handle in the _layouts directory named AuditLogWorkbook.ashx and passes the same query string with the ListId and ItemId. The custom handle AuditLogWorkbook.ashx also has the same assembly directive shown earlier so its code can be programmed against the ItemAuditing assembly as well. The code inside AuditLogWorkbook.ashx uses the AuditLogWork class to perform this work.

AuditLogWorkbook log = new AuditLogWorkbook();
log.WriteHeaderInfo(list.Title, item.Name, DateTime.Now);
SPAuditQuery wssQuery;
SPAuditEntryCollection auditCol;

wssQuery = new SPAuditQuery(ElevatedSiteCollection);
wssQuery.RestrictToListItem(item);
auditCol = ElevatedSite.Audit.GetEntries(wssQuery);

string eventType;
foreach (SPAuditEntry entry in auditCol) {
  if (entry.SourceName == "CustomViewAuditEvent") {
    eventType = "View Audit Log";
  }
  else {
    eventType = entry.Event.ToString();
  }
  log.WriteEntry(GetUserNameById(entry.UserId, site),
                  entry.Occurred.ToLocalTime().ToString(),
                  eventType,
                  ParseVersionNumber(entry.EventData));
}

After the code in AuditLogWorkbook.ashx generates the workbook using the AuditLogWorkbook class, it must return the workbook back to the user using the ASP.NET Response stream. The AuditLogWorkbook class provides a GetDocument method, which returns a MemoryStream object containing the package with the Excel workbook. The code in ItemAudit.aspx acquires a reference to the MemoryStream object and uses it to stream contents back to the user in a manner that is recognized as an Excel 2007 workbook.

Stream strDocument = log.GetDocument();

Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment; filename=AudutLog.xlsx");
Response.ContentType = @"application/vnd.-excel.document.12";
Response.ContentEncoding = System.Text.Encoding.UTF8;

strDocument.Position = 0;
BinaryWriter writer = new BinaryWriter(Response.OutputStream);
BinaryReader reader = new BinaryReader(strDocument);
writer.Write(reader.ReadBytes((int)strDocument.Length));
reader.Close();
writer.Close();
strDocument.Close();

Response.Flush();
Response.Close();

When the user clicks Generate Excel Workbook, the user has the option of opening the workbook directly in Microsoft Excel or saving it as an .xslx file. If the user chooses to open the file, the audit event information is displayed using the format shown in Figure 16.

Figure 16. Audit event information in Excel 2007

Audit Event Information

Server-Side Workbook Rendering Using Excel Services

Office SharePoint Server 2007 provides many new server-side capabilities for using Office Excel workbooks with Excel Services. Excel Services represents a server-side version of the traditional Excel calculation engine. This new engine is written on top of Windows SharePoint Services 3.0 so it can scale to meet the needs of a large audience.

Excel Services also provides a powerful rendering engine that can convert an Excel workbook into HTML so it can be displayed inside the browser. This allows a company to maintain a set of workbooks in a centralized Windows SharePoint Services document library, and makes the contents viewable even by users who do not have Excel installed on their desktops.

When you start using Excel Services, you will find you cannot load workbooks and render them as HTML unless they are first added to a document library configured with a Trusted File Location. The purpose of a Trusted File Locations is to give the central administrator for an Office SharePoint Server 2007 server farm control over which workbooks can be loaded into Excel Services and the types of data connections that are allowed.

A second command button on ItemAudit.aspx has the caption View Workbook with Excel Services. The event handler for this button also generates an Excel workbook using the Office Open XML Formats. Unlike the first command button, the event handler behind the second button is written to store the resulting Excel workbook inside the document library named Audit Logs. The user is then redirected to view this workbook using Excel Services. If you have properly configured a Trusted File Location, you should be able to click the button and view an HTML rendering of the workbook such as the example shown in Figure 17.

Figure 17. HTML rendering of workbook

HTML rendering of workbook

Be aware that if you are running the Item-Level Auditing sample application on a computer that has Windows SharePoint Services but not Office SharePoint Server 2007 installed, the command button with the caption View Workbook with Excel Services does not work properly because it redirects the user to an application that does not exist until Office SharePoint Server 2007 is installed.

Packaging the Item Auditing Solution for Deployment

Previously, we discussed all of the functionality built into the Item-Level Auditing solution. Now we can discuss how to take a solution such as Item-Level Auditing and package it for deployment. In this scenario the files that must be deployed on the front-end Web server include an assembly, two custom application pages, and the files that make up the Item Auditing feature.

Windows SharePoint Services 3.0 introduces a deployment mechanism known as solution packages. A solution package is a .cab file with a .wsp extension that contains all the files that must be deployed on the front-end Web server, along with other various XML-based instructions. Each solution package must contain a header file named manifest.xml that defines a Solution element. Consider the following manifest.xml file that was created for the Item Auditing solution.

<Solution SolutionId="44BE5F4A-D561-4981-A318-95ABC706364A" 
          xmlns="https://schemas.microsoft.com/sharepoint/">

  <FeatureManifests>
    <FeatureManifest Location="ItemAuditing\feature.xml" />
  </FeatureManifests>

  <TemplateFiles>
    <TemplateFile Location="LAYOUTS\AuditLogViewer.aspx"/>
    <TemplateFile Location="LAYOUTS\ItemAudit.aspx"/>
    <TemplateFile Location="LAYOUTS\AuditLogWorkbook.ashx"/>
  </TemplateFiles>

  <Assemblies>
    <Assembly DeploymentTarget="GlobalAssemblyCache" Location="ItemAuditing.dll" />
  </Assemblies>
 
</Solution>

The manifest.xml file defines which files are parts of the solution that must be deployed on the front-end Web server. After you define the manifest.xml file, the next step is to compile it along with all the other required files into a .cab file. This can be accomplished by using the MAKECAB.EXE tool. When you use this tool, you must define a .did file that tells MAKECAB.EXE what files to include in the output .cab file. Here is an example of a file named cab.ddf file used for building the ItemAuditing.wsp package file for the Item-Level Auditing solution.

.OPTION EXPLICIT ;Generate errors 
.Set CabinetNameTemplate=ItemAuditing.wsp     
.set DiskDirectoryTemplate=CDROM ; All cabinets go in a single directory
.Set CompressionType=MSZIP;** All files are compressed in cabinet files
.Set UniqueFiles="ON"
.Set Cabinet=on
.Set DiskDirectory1=Package

Solution\manifest.xml manifest.xml
TEMPLATE\FEATURES\ItemAuditing\feature.xml ItemAuditing\feature.xml
TEMPLATE\FEATURES\ItemAuditing\elements.xml ItemAuditing\elements.xml
TEMPLATE\LAYOUTS\AuditLogViewer.aspx LAYOUTS\AuditLogViewer.aspx
TEMPLATE\LAYOUTS\ItemAudit.aspx LAYOUTS\ItemAudit.aspx
TEMPLATE\LAYOUTS\AuditLogWorkbook.ashx LAYOUTS\AuditLogWorkbook.ashx
bin\Debug\ItemAuditing.dll ItemAuditing.dll

After you have defined the .ddf file, you can run the following command-line instruction to build the .wsp package solution file.

makecab /f Solution\cab.ddf

After you generate a solution package file such as ItemAuditing.wsp, you have what you need to deploy your solution within a Windows SharePoint Services or Office SharePoint Server 2007 server farm. Deploying a solution package is accomplished by using a two-step process. First, you must add the solution package, which copies the .wsp file into the configuration database. This can be done using the STSADM.EXE tool.

@SET SPDIR="c:\program files\common files\microsoft shared\web server extensions\12"
%SPDIR%\bin\stsadm -o addsolution -filename package\ItemAuditing.wsp

After you have installed the solution, it must still be deployed. In the second step, you must tell Windows SharePoint Services to deploy it to all the front-end Web servers in the server farm. This step can be accomplished through the user interface provided by the Windows SharePoint Services Central Administration application as described in SetupDocument.docx. Alternatively, this step can be accomplished by using the STSADM.EXE tool.

@SET SPDIR="c:\program files\common files\microsoft shared\web server extensions\12"
%SPDIR%\bin\stsadm -o deploysolution -name ItemAuditing.wsp -immediate –allowGacDeployment

You can instruct Windows SharePoint Services to deploy a solution immediately or later. In a production environment, it is a good practice to schedule solution package deployments during off hours to minimize the impact on users. The preceding example shows that the immediate attribute is used to instruct Windows SharePoint Services to install the solution package right away.

Also notice that the preceding command-line instruction to run the deploy solution operation from the STSADM.EXE tool includes the –allowGacDeployment parameter. This parameter is required whenever the solution package is adding an assembly to the global assembly cache.

Conclusion

This article explores the auditing support built into Windows SharePoint Services 3.0 and Office SharePoint Server 2007. It has also demonstrated how to extend this support with the custom Item-Level Auditing solution. You learned that Windows SharePoint Services provides the basic building blocks for auditing, and accomplishes this by providing an audit log scoped to the level of the site collection and a facility to write audit entries to this log whenever users view and update content. You also found that these building blocks are easy to extend with custom code that reads and writes from the audit log.

Office SharePoint Server 2007 extends the auditing support of Windows SharePoint Services by adding in an extensive layer, which makes it possible to enable and configure auditing without writing any custom code. The inclusion of information management policies makes it possible to create auditing policies at the site collection level and then apply these policies to lists, document libraries, and content types.

Office SharePoint Server 2007 also provides a reporting layer, which makes it possible to generate Excel workbooks with audit event information. These Excel workbooks are based on ExcelML and you can open them using either Microsoft Office Excel 2003 or Microsoft Office Excel 2007. You also learned that you could create a custom solution such as the Item-Level Auditing solution that uses the Office Open XML Formats to generate Excel workbooks containing audit event information. Such a solution also provides the option of using Excel Services so that you can display audit event information within the resulting workbooks using the browser. In this way, you reach even those users who do not have Excel installed on their desktops.

To learn more, visit the Microsoft SharePoint Products and Technologies site.

Additional Resources

For more information, see the following resources:

For Compliance

For Developers

How-To Resources

Resources for Microsoft Products