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
i was doing somethin glike this i ma getting cannto executte store procedure . permissiondenied. i copeid the sp into content db.
ReplyDeletethe users down ot have permissions.
Hello Sir,
ReplyDeleteThis is really a fantastic document regarding auditing by means of coding and the database queries are also too good.
Thanks
Amol Ghuge
Interesting approach to query popular documents, but doesn't creating custom procedures leave your content database and SharePoint installation in an unsupported state?
ReplyDeleteI 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
Hello,
ReplyDeleteI 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
Great blog you have this blog information is very useful to us, because we are also providing the same service.
ReplyDeleteDubai auditing accounts
Very useful, given that there is little or nothing from MS regards to what's what in the Content DB!
ReplyDelete