How can I report on software packages that are not associated to any managed delivery policies or quick delivery tasks?

Products

• IT Management Suite
• Software Management Solution

Issue/Introduction

As an administrator, I would like to know what software packages are currently not associated to a managed software delivery policy or a quick delivery task.

Environment

ITMS 8.5

Cause

N/A

Resolution

The following SQL query can be used to report on software packages that are not associated to a managed software delivery policy or a quick delivery task.

select vrmp.Guid, vrmp.Name
from vRM_Package_Item vrmp
left join ItemReference ir2 on ir2.ChildItemGuid = vrmp.Guid
and ir2.Hint in (
'manageddelivery uses software package',
'deliversoftwaretask uses swdpackage',
'swdadvertisement uses swdpackage')
where ir2.ChildItemGuid is null
and vrmp.Attributes = 0
and vrmp.Guid not in (
'6A670EC5-71BF-4288-86CC-64FBC29316D4',
'E68406C5-422C-441E-A407-810C597BEFA6',
'2AA5B1B5-9829-4389-B405-88F6723217ED',
'3D913AC4-3692-4DDA-A632-9C0D57B58333')

If you would like to delete these software packages, you can use the following SQL query:

Insert into ItemToDelete
select vrmp.Guid, GetDate()	
from vRM_Package_Item vrmp
left join ItemReference ir2 on ir2.ChildItemGuid = vrmp.Guid
and ir2.Hint in (
'manageddelivery uses software package',
'deliversoftwaretask uses swdpackage',
'swdadvertisement uses swdpackage')
where ir2.ChildItemGuid is null
and vrmp.Attributes = 0
and vrmp.Guid not in (
'6A670EC5-71BF-4288-86CC-64FBC29316D4',
'E68406C5-422C-441E-A407-810C597BEFA6',
'2AA5B1B5-9829-4389-B405-88F6723217ED',
'3D913AC4-3692-4DDA-A632-9C0D57B58333')

Keep in mind that if you have multiple administrators building software packages, there might be a software package that shows up as not associated to anything because the administrator is still building things.