SharePoint Auditing - Behind Screen and Remedy

From past few days I was on my way (as usual) and exploring SharePoint Server 2007 Features and came across Auditing. As the name suggests, Auditing in MOSS 2007 is basically keeping an eye over your target users actions. What I mean is you can audit the events like what end users have viewed, downloaded, checked in and checked out from your site. And more granularly this can be done at the Item Level and so you call ItemLevelAuditing in MOSS 2007. You can have a look at this link for primary idea how and what Auditing and reporting is done.

So I came to know that, the Audited data gets saved in the Content db of the site collection so I headed forward and opened SQL Server Management Studio. There you can see the db Table Name: AuditData. What was surprising to me is that if one configures MOSS to audit all the OOB supported events then this might blot up the size of content db and can cause some trouble for public facing sites where you don't know how many end users you are going to have for your site.

When you open AuditData table and try to see some entries then SharePoint adds the event types and item types in some columns as numbers.

I opened up faithful buddy reflector to see what those really are and here you can find those

Item Types
Document = 1,
Folder = 5,
List = 4,
ListItem = 3,
Site = 7,
Web = 6

Now, suppose each audit entry requires space (appx 64Kb of size per request) then supposing 10000 users per day, so at the end of the day content db size only for AuditData table will be at least 64000KB(64MB) which is quite large. There need to be something to have control over this one: and Microsoft has provided a remedy. There is something called TrimAuditLogs. This is a stsadm operation and gives control to farm administrators to maintain the audit entries. Administrators can run this command and trim the audit logs older than provided date.

Stsadm -o trimauditlog -date 20080704 -databasename WSSContent123456

Note that: This operation is available after the Infrastructure Update for Microsoft Office Servers is installed.

So what If you don't have installed the required update?

Nothing to worry .. developer can always find a way .. :)

Microsoft's SharePoint Server object model provides number of APIs where you can do the stuff as you want.

Here is something small that I tried to delete all the old audit log entries in the db

try
{
      using (SPSite site = new SPSite("http://yoursite"))
       {
            SPAudit auditE = site.Audit;

            auditE.DeleteEntries(DateTime.Now.AddMilliseconds(1));    //Microsoft Recommended
            Console.WriteLine("Entries Deleted");
       }
}
 catch (Exception ex)
  {
       Console.WriteLine(ex.Message);
   }

Note that : SPAudit.DeleteEntries is an audit event so delete Audit Log will be written in db even If you delete all entries and this entry will not be deleted

You can play further with these APIs and have fun :)

Reference: here