By default Software Releases and Software Components created from Inventory data are separate. Software releases are created when a software package is imported into the Software Catalog, either via the Software Library or other source. When Inventory is received from client computers software components are created for each application found in the environment, specific for each version. How can I locate software components that have both inventory and a software package?
The situations when you can have inventory and a software package are as follows:
When you deploy software via a Managed Software Delivery Policy the detection checks will send inventory data that attaches to the created Software Release, if that data matches (company, name, version).
You run Targeted Software Inventory that has software releases selected in the policy.
The following query returns these results. This query is provided "as is". Feel free to use it and modify as needed.
declare @InstalledCountAtLeast as int = 5--Must be installed in the environment with a minimum count, 0 = all
declare @ComponentsInProducts as table (Guid uniqueidentifier) insert into @ComponentsInProducts select distinct ra.ChildResourceGuid from ResourceAssociation ra where ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483’--Software Product Contains Software Component
declare @sc as table (Guid uniqueidentifier, compcount int) insert into @sc select _SoftwareComponentGuid,count (iis._ResourceGuid) from Inv_InstalledSoftware iis group by _SoftwareComponentGuid
select a.scGuid as _ItemGuid ,a.Type ,a.[Software Component\Release\Product] ,a.Version ,a.[Package Name] ,a.compcount as 'Computer Count' from ( select distinct sc.Guid as scGuid, 'Software Component' as [Type], sc.Name as 'Software Component\Release\Product', isc.Version, pack.Name 'Package Name', iis.compcount, 1 as _OrderBy from vRM_Software_Component_Item sc left join Inv_Software_Component isc on isc._ResourceGuid = sc.Guid left join @ComponentsInProducts cip on cip.Guid = sc.Guid left join @sc iis on sc.Guid= iis.Guid left join ResourceAssociation ra on ra.ChildResourceGuid = sc.Guid and ra.ResourceAssociationTypeGuid = '4486DFB2-D504-4493-B5B0-DF950352AB05'--Software Package Installs Software Component left join vRM_Software_Package_Item pack on pack.Guid = ra.ParentResourceGuid where cip.Guid is null and (@InstalledCountAtLeast <= iis.compcount or @InstalledCountAtLeast = 0) and sc.ProductGuid <> 'B1338338-5575-4A27-9808-23BEC40D79FA' and (@InstalledCountAtLeast <= iis.compcount or @InstalledCountAtLeast = 0)
select p.Guid, 'Software Product', p.Name, isp.Version, pack.Name, count (iis._ResourceGuid), 2 from vRM_Software_Product_Item p left join Inv_Software_Product_Version isp on isp._ResourceGuid = p.Guid join ResourceAssociation ra on ra.ParentResourceGuid = p.Guid and ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'--Software Product Contains Software Component left join Item i on i.Guid = ra.ResourceAssociationTypeGuid left join vRM_Software_Release_Item sr on sr.Guid = ra.ChildResourceGuid left join ResourceAssociation sr2pack on sr2pack.ChildResourceGuid = ra.ChildResourceGuid and sr2pack.ResourceAssociationTypeGuid = '4486DFB2-D504-4493-B5B0-DF950352AB05'--Software Package Installs Software Component left join vRM_Software_Package_Item pack on pack.Guid = sr2pack.ParentResourceGuid left join Inv_InstalledSoftware iis on iis._SoftwareComponentGuid = ra.ChildResourceGuid group by p.Name, p.Guid, sr.Name, pack.Name, isp.Version ) a where a.[Software Component\Release\Product] not like '%Update for%' and a.[Software Component\Release\Product] not like '%.NET Framework%' order by a._OrderBy, a.[Software Component\Release\Product]
Subscribing will provide email updates when this Article is updated. Login is required.