Sharepoint 2007 Auditing - Top Viewed Documents in a Site Collection

Introduction - 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.

Below Sample Code retrieves top 10 most viewed documents for a site collection, using audit event entries. Based on similar concepts, Webparts to show user-based recently viewed documents or updated, deleted etc. can be built. Administrator tools can be created to view audit data based on event type like view, update, delete etc.

Note: Auditing Tables are populated based on Site Collection Audit Settings, available in site collection administration section in site settings page. Admistrators would be able to configure various audit events like view, update, delete, check-in & check-out etc.

Sample Code -  

//C# Function
private void GetTopViewedDocuments(string targetSiteID)
{

      DataSet auditDataSet = null;
      if (targetSiteID != null)
      {
          SPSite targetSiteCollection = new SPSite(new Guid(targetSiteID));

          string contentDBName = targetSiteCollection.ContentDatabase.Name.ToString();


          //GetTopViewedDocumentsDataSet - Function is DAL function which make sql connection and calls the SP.
          auditDataSet = GetTopViewedDocumentsDataSet(contentDBName,targetSiteID,3);
       }
}

//Stored Procedure for getting audit data
//Paramters - ContentDBName for the sitecollection
//Dynamic SQL using ContentDBName and referring AuditData table for corresponding DB
CREATE PROCEDURE [dbo].[GetTopViewedDocuments]
 @ContentDBName varchar(200),
 @SiteID varchar(50),
        @Event int
AS
DECLARE
 @DynamicSQL varchar(4000),
 @WhereClause varchar(4000)

BEGIN

  SET NOCOUNT ON;

-- Where clause : limiting on SiteId and audit event and displaying data for few file extensions.
SET @WhereClause = 'AUDITDATA.SITEID = ''' + cast(@SiteID as varchar(200)) + ''' AND
    AUDITDATA.EVENT = ' + cast(@Event as varchar(10)) + 'AND
    DOCS.EXTENSION IN (''doc'',''docx'',''xls'',''xlsx'',''ppt'',''pptx'',''txt'')'

  SET @DynamicSQL =
  'SELECT
 TOP 10
 DOCS.LEAFNAME as "DOCUMENT NAME",
 DOCS.DIRNAME AS "DOCUMENT PATH",
 COUNT(ITEMID) as "OPERATION COUNT"
 FROM '
  + @ContentDBName + '.dbo.AUDITDATA AUDITDATA
 INNER JOIN '
  + @ContentDBName + '.dbo.DOCS DOCS
 ON
  AUDITDATA.ITEMID = DOCS.ID
 AND
  AUDITDATA.SITEID = DOCS.SITEID
 WHERE ' +
  @WhereClause +
 ' GROUP BY
  AUDITDATA.ITEMID,
  DOCS.LEAFNAME,
  DOCS.DIRNAME
 ORDER BY
  [OPERATION COUNT] DESC'

 EXEC(@DynamicSQL)
END

Audit Tables -

NOTE: Never update any SharePoint database directly. Always use the SharePoint API (Object Model) for any updates.

Database Table -

- AuditData - Table that holds information about all the auditing related data
- AuditData - Important Columns -
-- SiteId - Site Collection Id
-- ItemId - Id for item on which auditing is recorded
-- UserId - UserId for the user performing the auditing action
-- DocLocation - Location of Auditing Document
-- Occurred - Date and Time for the auditing event
-- Event - Event Id based on enumeration

Here are some common queries that we can run against the content databases for auditing records -

-- Query for getting auditing data for a particular document with user details

SELECT TP_TITLE,TP_LOGIN,DOCLOCATION,OCCURRED,EVENT
FROM AUDITDATA INNER JOIN USERINFO
ON AUDITDATA.USERID = USERINFO.TP_ID AND AUDITDATA.SITEID
= USERINFO.TP_SITEID
WHERE AUDITDATA.DOCLOCATION LIKE @SearchDocument AND USERINFO.TP_ISACTIVE =1

-- Query for getting auditing data for a particular site collection

SELECT AUDITDATA.DOCLOCATION,AUDITDATA.OCCURRED,
AUDITDATA.EVENT,SITES.ID
FROM AUDITDATA INNER JOIN SITES
ON AUDITDATA.SITEID = SITES.ID
WHERE SITES.ID = @SiteID

Important - SPAuditEventType Enumeration
- CheckOut = 1
- CheckIn = 2
- View = 3
- Delete = 4
- Update = 5
- Undelete = 10
- Copy = 12
- Move = 13