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:

6 comments :

  1. i was doing somethin glike this i ma getting cannto executte store procedure . permissiondenied. i copeid the sp into content db.
    the users down ot have permissions.

    ReplyDelete
  2. Hello Sir,

    This is really a fantastic document regarding auditing by means of coding and the database queries are also too good.

    Thanks
    Amol Ghuge

    ReplyDelete
  3. Interesting approach to query popular documents, but doesn't creating custom procedures leave your content database and SharePoint installation in an unsupported state?

    I have recently completed development on a product that uses SharePoint's Audit API to manage and view auditing across a SharePoint farm.

    See http://www.muhimbi.com/blog/2009/05/managing-sharepoints-audit.html

    ReplyDelete
  4. Hello,

    I am facing some issues related to SharePoint Auditing.

    Problem Description:
    ---------------------
    Not able to get the auditing reports. When we clicked on "content Viewing" report then it asked for credentials and after entering the username and password then it keeps the popup window as it is and when we click for the third time then it gives 403 unauthorized error message.

    Problematic Reports:
    ---------------------
    1. Content Viewing
    2. Conent Modification

    Troubleshooting Done:
    -----------------------
    1. Backup-Restore completed but no RESULTS
    2. Tried opening in Mozilla as well as in Firfox but no RESULTS
    3. Export-Import complete but no RESULTS
    4. Deactivated the reporting feature by means of site collection and turned on again but no RESULTS

    Could you please guide as how i will get the mentioned reports ? Thanks

    ReplyDelete
  5. Great blog you have this blog information is very useful to us, because we are also providing the same service.
    Dubai auditing accounts

    ReplyDelete
  6. Very useful, given that there is little or nothing from MS regards to what's what in the Content DB!

    ReplyDelete

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