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"