Client Management Suite

Previsouly posted was

https://www.symantec.com/connect/forums/report-managed-software-category

with no anwser. A request to include the the Category for a Managed Software.

I just figured this out via a SQL query, and wanted to post just in case anybody else had this request.

select
    vsp.Name  [Software Product]
    ,co.name Company
    ,ver.Version
    ,cgt.name Category
from vsoftwareproduct vsp 
join ResourceAssociation ra1 on ra1.ParentResourceGuid = vsp.Guid  and ra1.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D'  -->> resource associaton for Company
join vitem co on co.Guid = ra1.ChildResourceGuid                  -->> Software Product Company
join ResourceAssociation ra2 on ra2.ParentResourceGuid = vsp.Guid and ra2.ResourceAssociationTypeGuid = 'AE4DBDF7-457B-4828-A83E-B64B91AE0E87'   -->> resource association for category
left join vsoftwaretype cgt on cgt.Guid = ra2.ChildResourceGuid   -->> Software Product Category
left join Inv_Software_Product_Version ver on ver._ResourceGuid = vsp.Guid   -->> Software Product Version

Regard :)

You can also add join to Inv_Software_Product_State to sort out them by Managed or Unmanaged software
 

SELECT

vsp.Name    AS [Software Product],
co.Name     AS [Company],
ver.Version,
cgt.Name    AS Category

FROM vSoftwareProduct vsp

join ResourceAssociation ra1 on ra1.ParentResourceGuid = vsp.Guid  
and ra1.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D'  -->> resource associaton for Company
join vItem co on co.Guid = ra1.ChildResourceGuid                  -->> Software Product Company
join ResourceAssociation ra2 on ra2.ParentResourceGuid = vsp.Guid
and ra2.ResourceAssociationTypeGuid = 'AE4DBDF7-457B-4828-A83E-B64B91AE0E87'   -->> resource association for category
left join vSoftwareType cgt on cgt.Guid = ra2.ChildResourceGuid   -->> Software Product Category
left join Inv_Software_Product_Version ver on ver._ResourceGuid = vsp.Guid   -->> Software Product Version
join Inv_Software_Product_State sps
   ON sps._ResourceGuid = vsp.Guid
   WHERE sps.IsManaged = 1 --- 1 = Managed Software, 2 = Unmanaged Software
   ORDER BY vsp.Name
   ASC

Thanks Igor,

Would you happen to know what that values of 0, 1, 2 represent from the vsoftwareproduct table ?

As far as I know:

• IsManaged = 0 ---( Newly discovered/undefined software category)
• IsManaged = 1 ---( Managed Software category)
• IsManaged = 2 ---( Unmanaged Software category)

Open SMP Console -> Manage -> Software Catalog -> you will see there on left side will be list of newly discovered software and on right side managed/unmanaged software products