Through this blog, it is my effort to share experiences with the community and make the journey enjoyable.

Saturday, November 24, 2007

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
Share:

Search This Blog

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway. All posts are provided "AS IS" with no warranties, and confers no rights. In addition, my thoughts and opinions often change, and as a weblog is intended to provide a semi-permanent point in time snapshot you should not consider out of date posts to reflect my current thoughts and opinions.

Popular Posts

Total Pageviews

Blogger Tutorials

Blogger Templates

Sample Text