Create a tool or an interface to delete the Audit log from Content DB of an auditing enabled SharePoint site

Before starting this post, I would like to share an information about this facility in SharePoint. There was no way of deleting the audit log entries in SharePoint till the release of infrastructure update for SharePoint. Once you install this update, then you will get a STSADM command Trimauditlog and using this we can delete the unwanted audit logs.

 

I would recommend to refer this post if you want to implement a custom mechanism to delete the audit entries in your SharePoint site.

 

Consider, you have enabled Auditing in your SharePoint site, and there are more than 10,000 users and 3000 – 4000 users are accessing the site at the same time. Then your application will audit the information and it will store all the audit log information in the AuditData table in the content DB. If the data in that table exceeds some millions and if you access the audit report page then it may take some minuets to pull the data from the DB.

As a work-around we can remove the unwanted audit entries from the AuditData table. But direct interaction with the content DB is not supported L, so what we can do in this situation?

There you will get the help by using SharePoint object model J.

You can use SPAuditQuery & SPAuditEntryCollection classes which are there in the Microsoft.SharePoint.dll, to accomplish this requirement. Below code is a sample code for a .NET console based application, which will take a date as input and you can delete the audit log till that particular date.

I am taking a backup of already deleted audit log in a text file and it will save inside the bin\debug directory.

<code>

using

System;

using

System.IO;

using

System.Text;

using

Microsoft.SharePoint;

using

System.Collections;

using

System.Collections.Generic;

namespace

DeleteAuditEntries

{

class Program

{

static void Main(string[] args)

{

Console.WriteLine("Example Deletion of Audit Entries");

Console.WriteLine("Enter a date below. In the root site collection, all audit entries created before the date entered will be deleted.");

SPSite site = new SPSite("https://localhost");

Console.Write("Enter Month: ");

int month = Convert.ToInt32(Console.ReadLine());

Console.Write("Enter Day: ");

int day = Convert.ToInt32(Console.ReadLine());

Console.Write("Enter Year: ");

int year = Convert.ToInt32(Console.ReadLine());

DateTime deleteBeforeDate = new DateTime(year, month, day);

//Let's query the log to get a report of all the entries we are deleting

SPAuditQuery newQuery = new SPAuditQuery(site);

newQuery.SetRangeEnd(deleteBeforeDate);

//This ensure we scope the query to just the logs about to be deleted

SPAuditEntryCollection report = site.Audit.GetEntries(newQuery);

TextWriter reportfile = new StreamWriter("auditreport.txt");

foreach (SPAuditEntry i in report)

{

reportfile.WriteLine(i.ToString());

}

reportfile.Close();

//This is the call that actually deletes the entries.

site.Audit.DeleteEntries(deleteBeforeDate);

Console.WriteLine("Complete. auditreport.txt contains a list of all audit entries that were deleted.");

return;

}

}

}

</code>

If you want to integrate this functionality in your SharePoint site, then you can create a custom aspx page you can implement this same functionality there. If you want to do that, then you can just go through the below MSDN link.

 

https://msdn2.microsoft.com/en-us/library/bb397403.aspx

 

The above MSDN link is for creating a custom aspx for a different functionality with Auditing, but you can follow the steps in this article to create a user interface for deleting the auditing log entries and archiving the data in an another custom DB or in a text file.

 

1. Sample image 1

 

For E.g.: First you can create a custom action to your Site Actions to add a link to redirect the Administrator to the AuditData deletion page.

 

 

2. Sample image 2

After clicking that custom menu item, you can redirect the user to your custom aspx page which you need to keep in your Layouts folder.

Drive:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS

            

Comments

• Anonymous
  July 08, 2012
  Hi, Is there a way to pass it a batch size? If don't want to a have long running transaction on my db. For example, I want it to delete rows for a specific date but in a smaller batches... As per table dependcies, Audit table has no direct relationship with any other table. Does not it make sense to just trunctate the table if  audit entries are not required. I have 120 Million rows to delete. It makes no sense to me to delete them row by row.... or chunks of millions... I would appreciate your input..

• Anonymous
  July 10, 2012
  Hi Najim, You can pull the audit data based upon the date parameters. If it is in 2010 we have a timer job which scheduled to run in a monhtly basis to clean the audit information of a site if it is marked for trimming. Thanks, Sowmyan