Explanation about Job History Cleanup Setting

Products

• CA Client Automation - IT Client Manager
• CA Client Automation
• CA Client Automation - Software Delivery

Issue/Introduction

Configuration policy parameter DSM/Software Delivery/Manager/Maintenance: Job history cleanup setting could have 3 possible values : 0,1 and 2 (default)

 

When the JobHistoryCleanUpTime timer has elapsed, the following records for jobs in OK or error state are cleaned up in the computer jobs folder.

Allowed values:

0 = Clean up all records for no longer installed/delivered software only,

1 = Clean up all non-installation records for installed software and all records for no longer installed/delivered software,

2 = Clean up all failed non-installation records for installed software and all records for no longer installed/delivered software.

 

TaskMan executes the Job History Cleanup task every 24 hours and delete the old jobs older than 180 days (number of days is configurable).

It uses some SQL Queries to find the list of job to delete.

 

Which SQL Queries are used for every value of JobHistoryCleanUp ?

Environment

Client Automation 14.5

Resolution

For the 3 values (0,1 and 2) following SQL Query is used to find old SD Jobs for no longer installed/delivered software

 

select * from usd_applic T  
where T.uninstallstate = 2 AND T.objectid in 
( SELECT I.installation FROM usd_applic I 
WHERE (datediff(ss,convert(datetime,'19700101'),getutcdate()) - I.completiontime) >= 15552000
AND(CAST(I.task as INT) & 0x00000002 = 0x00000002 OR CAST(I.task as INT) & 0x00000020 = 0x00000020) )

 

15552000 is 180 days in seconds

Task 0x2 means "Uninstall Task"
Task 0x20 means "Remove SW"

 

 

- For JobHistoryCleanUp=2 following SQL Query is executed :

select * from usd_applic T , usd_applic I 
where T.installation = I.objectid AND 
(datediff(ss,convert(datetime,'19700101'),getutcdate()) - I.completiontime) >= 15552000
and I.uninstallstate = 0 and T.status in (5, 6, 10, 14, 15, 16)

 

Status 5 means SW Delivery Error
Status 6 means SW Already Delivered
Status 10 means Job Execution Error
Status 14 means SW Removal Error
Status 15 means SW Already Installed
Status 16 means Manipulation not allowed

 

 

- For JobHistoryCleanUp=1 following SQL Query is executed :

select * from usd_applic T , usd_applic I 
where T.installation = I.objectid AND 
(datediff(ss,convert(datetime,'19700101'),getutcdate()) - I.completiontime) >= 15552000
and I.uninstallstate = 0 and (T.status in (5, 6, 10, 14, 15, 16)

or ((T.task = 0x00000004 or T.task = 0x00000008) and T.status = 9))

 

Task 0x4 means "Configure"
Task 0x8 means "Activate"

 

 

 

- For JobHistoryCleanUp=0 following SQL Query is executed :

select * from usd_applic T

where (datediff(ss,convert(datetime,'19700101'),getutcdate()) - T.completiontime) >= 15552000

and T.status in (5, 6, 10, 14, 15, 16)

and (CAST(T.task as INT) & 0x00000001 = 0x00000001 or CAST(T.task as INT) & 0x00000010 = 0x00000010)

 

Task 0x1 means "Install Task"
Task 0x10 means "DeliverSW"