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