How do I purge Audit Inventory data from the database?
search cancel

How do I purge Audit Inventory data from the database?

book

Article ID: 176920

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

The Symantec Management Console does not provide a way to purge old Audit Inventory (Inv_Audit) information. How do I remove old and unwanted data from the database?

Environment

ITMS 8.x

Cause

The Inv_Audit table contains records of items or resources (computers, software, dataclasses, etc) that have been changed as well as the user that changed them, but not what was changed.  The Audit data for an item can be viewed by right clicking an item and selecting Properties, then selecting the Audit tab.  Purging Inv_Audit will only remove the data located on this tab.

Removal of this data will not impact the inventory process or any existing inventory data.

Resolution

Applies to Symantec Management Platform 8.x

In SMP 8.5+ Console browse to Settings > Notification Server > Core Settings

By default the Audit Purge functionality is disabled. In order to make it enabled, there is a value in Core Settings called AuditPurgeMaxAge which is -1 (Disabled). In order to enable this functionality, a positive value like 15 (number of days) should be assigned to the parameter. This value is the Number of days used to delete records which have AuditDate less than or equal to current date minus maxAge.

Once the NS.Daily schedule runs, the old records in the Inv_Audit table will be purged.

 

Applies to all versions of Symantec Management Platform

You can use a manual process to purge the unwanted data. The query shown below purges data from the Inv_Audit table. You can modify this query to suit your requirements, and then run it against the database to remove the appropriate data.

The example below removes all records that are older than a specified number of days (the current setting is 10 days). You can also modify this query to remove records based on resource type, or on the user ID of the user running the query.

-- Identify the resources which are going to have some data purged.
select distinct _ResourceGuid
into #resources
from Inv_Audit 
where DateDiff(d, Inv_Audit.AuditDate, GETDATE()) > 10
 
declare @DataClassGuid uniqueidentifier
set @DataClassGuid = '57beb323-f925-4689-872f-1bf3aa3f7632'      
 
declare @Guid uniqueidentifier
declare cur_Guid CURSOR FOR
      select _ResourceGuid from #resources
 
-- Purge data older than 10 days
delete from Inv_Audit
where DateDiff(d, Inv_Audit.AuditDate, GETDATE()) > 10
and Inv_Audit._ResourceGuid in (select r._ResourceGuid from #resources r)
 
if(@@error = 0 AND @@rowcount > 0)
begin
    -- Ensure resource update summary and history are correct after the fact.
      open cur_Guid
      fetch next from cur_Guid into @Guid
      while @@FETCH_STATUS = 0
      begin
            exec spResourceUpdateSummary @Guid, null, @DataClassGuid
            exec spResourceGenerateHistorySnapshot @Guid, @DataClassGuid
            fetch next from cur_Guid into @Guid
      end
      close cur_Guid
      deallocate cur_Guid
end
drop table #resources

 

Purging data by age

The above query is set up to purge data that is older than 10 days old from the Inv_Audit table. If you want to specify a different number of days, change both instances of the number 10 to the appropriate number.

 

Purging data by resource type

If you want to purge data based on its resource type, replace both DateDiff(d, Inv_Audit.AuditDate, GETDATE()) > 10 expressions with the following:

Inv_Audit._ResourceGuid in (select Guid from ItemResourceType where ResourceType = ’<resourceTypeGuidGoesHere>’)

 

Purging data by user ID

If you want to purge data based on the user who is performing the action (for example, to remove all entries where the user is the service account), replace both DateDiff(d, Inv_Audit.AuditDate, GETDATE()) > 10 expressions with the following:

Inv_Audit.UserId = ’<UserNameGoesHere>’