Disabling "Enable Verbose Reporting of Package Status Events" on all existing Packages

Products

• IT Management Suite

Issue/Introduction

You noticed a lot of deadlocks in your NS logs. After enabling SQL flag trace (per  KB 180941), you found out that the deadlocks were caused when sp_Inv_AeX_SWD_Status_Summary_clean was executed. After looking on the stored procedure, it was noticed that the table been used was Inv_Aex_SWD_Status_Summary. In this case it was too large. Purging Maintenance was not enabled to purge this table, and multiple events were coming from each package that was enable (Software Delivery and Patch Management were the most common ones).
One of the events that causes to overpopulate this table is Verbose Reporting of Package Status Events.

Is there a way to disable the "Verbose Reporting of Package Status Events"?

Environment

ITMS 7.x, 8.x

Resolution

AeX SWD Package events are sent usually when the following occur:

• New Package
• Package Updated
• Package To Be Removed
• Package Removed
• Unable To Check Package
• Insufficient Disk To Download
• Download Complete
• Package Download Blocked

The AeX SWD Package events are not required and, in environments where the SMP Server has a lot of clients, can cause unneeded burden on the SMP Server. So in some large environments, the AeX SWD Package events are disabled on the SMP Server. 

At this point there are two things that we need to consider: One, how we can stop of getting these events sent from any new package enabled? and two, how we can disable these events from all packages already created?.

A) For new packages:

There are two commom settings that we could disable to avoid these events.
1. Under Settings>All Settings>Software>Managed Delivery Settings>Run tab>Reporting section, uncheck 'Enable Verbose Reporting of Package Status Events'. It should stop those verbose reporting events from all the Software Delivery packages.
2. Under Settings>All Settings>Software>Patch Management>Microsoft Settings>Microsoft>Programs tab, uncheck 'Send Package Events'

B) For already created packages:

Run the following query (after making sure that you have a current backup of your database. This query should not be a risk but we suggest having backups when changes are made directly in the database):

update item
set State = replace( cast([state] as varchar(8000)),'<statusEventsEnabled>True</statusEventsEnabled>','<statusEventsEnabled>False</statusEventsEnabled>'),
ModifiedDate = getdate()
where guid in
 (
  select v.guid from vItem v
  join class c on v.classguid = c.guid
  where c.type = 'Altiris.Inventory.Policies.SoftwareInventoryPolicy'
  or c.type = 'Altiris.SoftwareManagementSolution.SoftwareDelivery.ManagedDeliveryPolicy'
  or c.type = 'Altiris.PatchManagementCore.Resources.PackageInfo'
 )
and guid not in
 (
  select Guid
  from item
  where datalength (state) > 8000
 )

update RM_ResourceSoftware_Package
set [State] = replace( cast([state] as varchar(8000)),'statusEvents enabled="true"','statusEvents enabled="false"'),
ModifiedDate = getdate()
where guid not in
            (
            select Guid
            from RM_ResourceSoftware_Package
            where datalength (state) > 8000
            )
and ProductUninstalled = 0
and Deleted = 0

Then you should be able to reduce the size of the Inv_AeX_SWD_Package_Summary table by small portions. You can use the following query:

Note: The following is a primitive SQL script can be used on SQL server 2000, 2005 or 2008. You would need to enter the table name directly into the script and replace %TableName%. We recommend that you don't use this query for every table. Some tables contains data that is required for certain processes.You can also change the variable @DaysToKeep to the needed value (default is 90 days) and/or adjust the rowcount value (this value limits the number of rows to delete on each run) to a bigger/ smaller value (default value is 10000) depending on the execution time of each run. You can run this delete script over and over till the results are 0 row affected.  Please test the script on a temp table first, so you understand it. You can then use the last query to see how many rows are left in that table.

-- Delete older row entries from an event table
set rowcount 10000
declare @DaysToKeep int
set @DaysToKeep = 90

delete from [%TableName%]
where datediff(day,[_eventTime],getdate()) > @DaysToKeep

-- Shows the table row count (run separate from the above script)
select count(*) from [%TableName%]