Assessing And Trimming SP 2007 AuditData table

In SharePoint 2007, the content database is where SharePoint stores the audit data, in a table named AuditData. It can grow quickly, and there is no automated OOTB way to keep only a certain number of days or up to a certain size. This has to be done manually.

First, running a query or two on the AuditData table can help confirm that the data is no longer needed.

SELECT MIN(Occurred) as 'Earliest'
,MAX(Occurred) as 'Latest'
,COUNT(*) as 'EventCount'
FROM AuditData

This gives us some basic info:

Earliest	Latest	EventCount
2009-06-19 20:12:13.000	2014-01-02 22:02:09.000	273213041

First we can look at how many events are happening per day:

SELECT CONVERT(date,Occurred) as 'EventDate'
  ,COUNT(*) as 'EventCount'
FROM AuditData
GROUP BY CONVERT(date,Occurred)
ORDER BY CONVERT(date,Occurred)

That will skip dates with 0 events. If you want to include every date, you'd need to do something like this:

DECLARE @dateRange TABLE (dateValue DATE)
DECLARE @date DATE, @EndDate DATE
SELECT @date='2009-06-18', @EndDate='2014-01-03'
WHILE @date <= @EndDate
BEGIN
  INSERT INTO @dateRange VALUES (@date)
  SET @date = DATEADD(d,1,@date)
END
SELECT dr.dateValue as 'EventDate'
  ,ISNULL(a.EventCount,0)
FROM @dateRange dr
LEFT OUTER JOIN (
  SELECT CONVERT(date,Occurred) as 'EventDate'     ,COUNT(*) as 'EventCount'

  FROM AuditData  GROUP BY CONVERT(date,Occurred)
) as a  ON a.EventDate = dr.dateValue
ORDER BY dr.dateValue

We can see the sum of events per type per year:

SELECT ad.EventId
  ,YEAR(Occurred) as 'EventYear'
  ,COUNT(*) as 'EventCount'
FROM AuditData ad
GROUP BY ad.EventId
  ,YEAR(Occurred)
ORDER BY YEAR(Occurred), ad.EventId

If we need to pivot that data:

SELECT pvt.EventId
  ,ISNULL([2009],0) AS '2009'
  ,ISNULL([2010],0) AS '2010'
  ,ISNULL([2011],0) AS '2011'
FROM (
  SELECT EventId
    ,CONVERT(date,Occurred) as 'EventDate'
    ,YEAR(Occurred) as 'EventYear'
  FROM AuditData ad
) as SourceTable
PIVOT (
  COUNT(EventDate)
  FOR EventYear IN ([2009],[2010],[2011])
) as pvt
ORDER BY pvt.EventId

Then, if we want to get fancy, we can make this more human readable by replacing some IDs with actual values.

For the Event Types:

DECLARE @EventTypes TABLE (Id int, Name nvarchar(100))
INSERT INTO @EventTypes
SELECT 1, 'CheckOut' UNION ALL
SELECT 2, 'CheckIn' UNION ALL
SELECT 3, 'View' UNION ALL
SELECT 4, 'Delete' UNION ALL
SELECT 5, 'Update' UNION ALL
SELECT 6, 'ProfileChange' UNION ALL
SELECT 7, 'ChildDelete' UNION ALL
SELECT 8, 'SchemaChange' UNION ALL
SELECT 10, 'Undelete' UNION ALL
SELECT 11, 'Workflow' UNION ALL
SELECT 12, 'Copy' UNION ALL
SELECT 13, 'Move' UNION ALL
SELECT 14, 'AuditMaskChange' UNION ALL
SELECT 15, 'Search' UNION ALL
SELECT 16, 'ChildMove' UNION ALL
SELECT 30, 'SecGroupCreate' UNION ALL
SELECT 31, 'SecGroupDelete' UNION ALL
SELECT 32, 'SecGroupMemberAdd' UNION ALL
SELECT 33, 'SecGroupMemberDel' UNION ALL
SELECT 34, 'SecRoleDefCreate' UNION ALL
SELECT 35, 'SecRoleDefDelete' UNION ALL
SELECT 36, 'SecRoleDefModify' UNION ALL
SELECT 37, 'SecRoleDefBreakInherit' UNION ALL
SELECT 38, 'SecRoleBindUpdate' UNION ALL
SELECT 39, 'SecRoleBindInherit' UNION ALL
SELECT 40, 'SecRoleBindBreakInherit' UNION ALL
SELECT 50, 'EventsDeleted' UNION ALL
SELECT 100, 'Custom' 

For the Item Types:

DECLARE @ItemTypes TABLE (Id int, Name nvarchar(100))
INSERT INTO @ItemTypes
SELECT 1, 'Page/File' UNION ALL
SELECT 3, 'List Item' UNION ALL
SELECT 4, 'List' UNION ALL
SELECT 5, 'Folder' UNION ALL
SELECT 6, 'Web' UNION ALL
SELECT 7, 'Site Collection'

For User Info, you'll need to get that info from the UserInfo table, for what you need:

DECLARE @UserInfo TABLE (
  SiteId uniqueidentifier
  ,UserId int  ,UserLogin nvarchar(255))
INSERT INTO @UserInfo
SELECT DISTINCT ad.SiteId  ,ad.UserId  ,ui.tp_Login
FROM AuditData ad
LEFT OUTER JOIN UserInfo ui   ON ad.UserId = ui.tp_ID     AND ad.SiteId = ui.tp_SiteID

So your Yearly Pivoted data could be:

SELECT et.Id
,et.Name
  ,ISNULL([2009],0) AS '2009'
  ,ISNULL([2010],0) AS '2010'
  ,ISNULL([2011],0) AS '2011'
FROM (
  SELECT EventId
    ,CONVERT(date,Occurred) as 'EventDate'
    ,YEAR(Occurred) as 'EventYear'
  FROM AuditData ad
) as SourceTable
PIVOT (
  COUNT(EventDate)
  FOR EventYear IN ([2009],[2010],[2011])
) as pvt
RIGHT OUTER JOIN @EventTypes et
ON pvt.EventId = et.Id
ORDER BY et.Id

Now for getting rid of it.

If this is a non-production environment, then modifying the SharePoint database is a possibility, and quicker to just truncate the table.

Otherwise, use PowerShell to call STSADM 1 day at a time (to prevent transaction logs and such from filling drive and failing).

########################################
# TrimAuditLog via PowerShell and STSADM
#
# Authored: 2016/06/12
# Author: Steve Cervenak
########################################
# Set these variables to appropriate values
$startDate = [datetime]"6/1/2009"
$endDate = [datetime]"1/3/2014"
$url = "http://siteUrl"
$logFile = "C:\ps\trimLog.log"
##################################################
set-alias STSADM "${env:commonprogramfiles}\Microsoft Shared\Web Server Extensions\12\BIN\STSADM.EXE"
$timer = [system.diagnostics.stopwatch]::StartNew()
$subtimer = [system.diagnostics.stopwatch]::StartNew()
$logmsg = "$($timer.Elapsed.ToString()): Starting '$startDate' through '$endDate'"
$logmsg | Out-File $logFile -Append
Write-Host $logmsg
$date = $startDate
while ($date -le $endDate)
{
    $subtimer.Reset()
    $subtimer.Start()
    $dateStr = $date.ToString('yyyyMMdd')
    Write-Host "$($timer.Elapsed.ToString()): Trimming through '$dateStr'"
    STSADM -o trimauditlog -url $url -date $dateStr
    $logmsg = "$dateStr,$($subtimer.Elapsed.TotalSeconds)"
    $logmsg | Out-File $logFile -Append  
    Write-Host "$($timer.Elapsed.TotalSeconds) secs: $logmsg seconds`n`n" -f green
 
    $date = $date.AddDays(1);
}
$logmsg = "$($timer.Elapsed.ToString()): Completed '$startDate' through '$endDate'"
$logmsg | Out-File $logFile -Append
Write-Host "$logmsg `nCOMPLETE"

Here's a link to a PowerShell script that can export the audit data to a csv if that is needed: http://sharepoint.stackexchange.com/questions/119915/how-to-archive-the-auditdata-table-on-a-contentdb

Copied here:

$tabName = "AuditLog"

#Create Table object
$table = New-Object system.Data.DataTable “$tabName”

#Define Columns
$col1 = New-Object system.Data.DataColumn SiteUrl,([string])
$col2 = New-Object system.Data.DataColumn SiteID,([string])
$col3 = New-Object system.Data.DataColumn ItemName,([string])
$col4 = New-Object system.Data.DataColumn ItemType,([string])
$col5 = New-Object system.Data.DataColumn UserID,([string])
$col6 = New-Object system.Data.DataColumn UserName,([string])
$col7 = New-Object system.Data.DataColumn Occurred,([DateTime])
$col8 = New-Object system.Data.DataColumn Event,([string])
$col9 = New-Object system.Data.DataColumn Description,([string])
$col10 = New-Object system.Data.DataColumn EventSource,([string])
$col11 = New-Object system.Data.DataColumn SourceName,([string])
$col12 = New-Object system.Data.DataColumn EventData,([string])
$col13 = New-Object system.Data.DataColumn MachineName,([string])
$col14 = New-Object system.Data.DataColumn MachineIP,([string])

#Add the Columns
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)
$table.columns.add($col6)
$table.columns.add($col7)
$table.columns.add($col8)
$table.columns.add($col9)
$table.columns.add($col10)
$table.columns.add($col11)
$table.columns.add($col12)
$table.columns.add($col13)
$table.columns.add($col14)

$site = Get-SPSite -Identity http://SiteCollectionName
$wssQuery = New-Object -TypeName Microsoft.SharePoint.SPAuditQuery($site)
$auditCol = $site.Audit.GetEntries($wssQuery)
$root = $site.RootWeb

for ($i=0; $i -le ($auditCol.Count)-1 ; $i++)
{
    #Get the Entry Item from the Collection
    $entry = $auditCol.item($i)

    #Create a row
    $row = $table.NewRow()

    #find the Current UserName 
    foreach($User in $root.SiteUsers)
    {
        if($entry.UserId -eq $User.Id)
        {
             $UserName = $User.UserLogin
        }
    }   

    #find the Item Name
    foreach($List in $root.Lists)
    {
        if($entry.ItemId -eq $List.Id)
        {
             $ItemName = $List.Title
        }
    }   

    #Define Description for the Event Property
     switch ($entry.Event)
    {
        AuditMaskChange{$eventName = "The audit flags are changed for the audited object."}
        ChildDelete {$eventName = "A child of the audited object is deleted."}
        ChildMove {$eventName = "A child of the audited object is moved."}
        CheckIn {$eventName = " A document is checked in."}
        'Copy' {$eventName = "The audited item is copied."}
        Delete {$eventName = "The audited object is deleted."}
        EventsDeleted {$eventName = "Some audit entries are deleted from SharePoint database."}
        'Move' {$eventName = "The audited object is moved."}
        Search {$eventName = "The audited object is searched."}
        SecGroupCreate {$eventName = "A group is created for the site collection. (This action also generates an Update event.See below.)"}
        SecGroupDelete {$eventName = "A group on the site collection is deleted."}
        SecGroupMemberAdd {$eventName = "A user is added to a group."}
        SecGroupMemberDelete {$eventName = "A user is removed from a group."}
        SecRoleBindBreakInherit {$eventName = "A subsite's inheritance of permission level definitions (that is, role definitions) is severed."}
        SecRoleBindInherit {$eventName = "A subsite is set to inherit permission level definitions (that is, role definitions) from its parent."}
        SecRoleBindUpdate {$eventName = "The permissions of a user or group for the audited object are changed."}
        SecRoleDefCreate {$eventName = "A new permission level (a combination of permissions that are given to people holding a particular role for the site collection) is created."}
        SecRoleDefDelete {$eventName = "A permission level (a combination of permissions that are given to people holding a particular role for the site collection) is deleted."}
        SecRoleDefModify {$eventName = "A permission level (a combination of permissions that are given to people holding a particular role for the site collection) is modified."}
        Update {$eventName = "An existing object is updated."}
        CheckOut {$eventName = " A document is checked Out."}
        View {$eventName = "Viewing of the object by a user."}
        ProfileChange {$eventName = "Change in a profile that is associated with the object."}
        SchemaChange {$eventName = "Change in the schema of the object."}
        Undelete {$eventName = "Restoration of an object from the Recycle Bin."}
        Workflow {$eventName = "Access of the object as part of a workflow."}
        FileFragmentWrite {$eventName = "A File Fragment has been written for the file."}
        Custom {$eventName = "Custom action or event."}
        default {$eventName = "The Event could not be determined."}
    }

    #Enter data in the row
    $row.SiteUrl = $site.Url
    $row.SiteID = $entry.SiteID
    $row.ItemName = $ItemName
    $row.ItemType = $entry.ItemType
    $row.UserID = $entry.UserID
    $row.UserName = $UserName
    $row.Occurred = $entry.Occurred
    $row.Event = $entry.Event
    $row.Description = $eventName
    $row.EventSource = $entry.EventSource
    $row.SourceName = $entry.SourceName
    $row.EventData = $entry.EventData
    $row.MachineName = $entry.MachineName
    $row.MachineIP = $entry.MachineIP

    #Add the row to the table
    $table.Rows.Add($row)

}

$date = get-date -format "d-M-yyyy"
$sDtae = [string]$date
$FileName = "AuditLogReport_For_" + $sDtae
#Export the CSV File to Folder Destination
$tabCsv = $table | export-csv C:\$FileName.csv -noType