Video Screencast Help

Report on unmanaged software

Created: 04 Mar 2012 • Updated: 05 Mar 2012 | 6 comments
Benny_Yu's picture
This issue has been solved. See solution.

We are currently working on the report of the unmanaged software in the software catalog view.
Newly discovered software will become managed and unmanaged inside the software catalog view.
We can find IsMnanged = 1 is managed software, but can't find the solution to define unmanaged software. 
We have try  IsMnanged = 0 but it will included newly discoved software.

Anyone know how to create a report on unmanaged software in the software catalog view? Thanks!

 

Comments 6 CommentsJump to latest comment

Andrew Bosch's picture

Unmanaged software is defined as IsManaged = 2.  So essentially, it's...

SELECT *
FROM RM_ResourceSoftware_Product sp
JOIN Inv_Software_Product_State sps
   on sps._ResourceGuid = sp.Guid
WHERE sps.IsManaged = 2

I writing this off the top of my head so table and column names may not match up perfectly.  Another thing to note, the IsManaged column that is part of the base Software Product resource table (RM_ResourceSoftware_Product) doesn't match what is found in Inv_Software_Product_State.  This is a known descrepency so for now, trust what's in the product state table.

Cheers,

Andrew

------------------------------------
Sr. Principal SQA Engineer
Symantec

Benny_Yu's picture

Thanks Andrew!
I have tried and it can handle the unmanaged software product.
But in the operation, we will just drop the software component/installed software into the unmanaged software, is it possible to have a report on the unmanaged (installed) software?

Andrew Bosch's picture

Here's a start.  What columns are you looking for, specifically?

 

SELECT DISTINCT sp.Name, 'Software Product' AS [Type]
FROM RM_ResourceSoftware_Product sp
JOIN Inv_Software_Product_State sps
   ON sps._ResourceGuid = sp.Guid
JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid = sp.Guid
   AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
WHERE ra.ChildResourceGuid IN (SELECT _SoftwareComponentGuid FROM Inv_InstalledSoftware WHERE InstallFlag = 1) 
AND sps.IsManaged = 2
UNION
SELECT DISTINCT sci.Name, 'Software Component' AS [Type]
FROM vRM_Software_Component_Item sci
LEFT JOIN Inv_Software_Component_State scs
   ON sci.Guid = scs._ResourceGuid
WHERE sci.Guid IN (SELECT _SoftwareComponentGuid FROM Inv_InstalledSoftware WHERE InstallFlag = 1) 
AND sci.Guid NOT IN (SELECT ChildResourceGuid FROM ResourceAssociation WHERE ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483')
AND ISNULL(scs.IsManaged, 0) = 2

------------------------------------
Sr. Principal SQA Engineer
Symantec

SOLUTION
Benny_Yu's picture

I am looking for the unmanaged software columns in my attached picture(Software catalog view).
I have tried your SQL and I think you have solved our porblem, Thanks Andrew!

Benny_Yu's picture

is it able to join Vcomputer or vAllComputerInfo in your SQL  to display all computer that installed unmanaged software?

Andrew Bosch's picture

 

SELECT DISTINCT sp.Name, 'Software Product' AS [Type], spv.[Version], company.CompanyName, inst.ComputerName
FROM RM_ResourceSoftware_Product sp
JOIN Inv_Software_Product_State sps
   ON sps._ResourceGuid = sp.Guid
LEFT JOIN Inv_Software_Product_Version spv
   ON spv._ResourceGuid = sp.Guid
JOIN (SELECT ra.ParentResourceGuid AS ProductGuid, vc.Name AS ComputerName
      FROM ResourceAssociation ra
      JOIN Inv_InstalledSoftware iss 
         ON iss._SoftwareComponentGuid = ra.ChildResourceGuid
         AND InstallFlag = 1
      JOIN vComputer vc
         ON vc.Guid = iss._ResourceGuid
         AND vc.IsManaged = 1
      WHERE ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483') inst
   ON inst.ProductGuid = sp.Guid
LEFT JOIN (SELECT ra.ParentResourceGuid AS ProductGuid, ci.Name AS CompanyName
           FROM vRM_Company_Item ci
           JOIN ResourceAssociation ra
              ON ra.ChildResourceGuid = ci.Guid
              AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D') company
   ON company.ProductGuid = sp.Guid
WHERE sps.IsManaged = 2
UNION
SELECT DISTINCT sci.Name, 'Software Component' AS [Type], sc.[Version], company.CompanyName, inst.ComputerName
FROM  vRM_Software_Component_Item sci
LEFT JOIN Inv_Software_Component_State scs
   ON scs._ResourceGuid = sci.Guid
LEFT JOIN Inv_Software_Component sc
   ON sc._ResourceGuid = sci.Guid
JOIN (SELECT iss._SoftwareComponentGuid AS scguid, vc.Name AS ComputerName
      FROM Inv_InstalledSoftware iss
      JOIN vComputer vc
         ON vc.Guid = iss._ResourceGuid
         AND vc.IsManaged = 1
      WHERE iss.InstallFlag = 1) inst
   ON inst.scguid = sci.Guid
LEFT JOIN (SELECT ra.ParentResourceGuid AS ComponentGuid, ci.Name AS CompanyName
           FROM vRM_Company_Item ci
           JOIN ResourceAssociation ra
              ON ra.ChildResourceGuid = ci.Guid
              AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16') company
   ON company.ComponentGuid = sci.Guid
WHERE ISNULL(scs.IsManaged, 0) = 2
AND sci.Guid NOT IN (SELECT ChildResourceGuid FROM ResourceAssociation WHERE ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483')

------------------------------------
Sr. Principal SQA Engineer
Symantec