Client Management Suite

 View Only

  • 1.  Report on unmanaged software

    Posted Mar 05, 2012 01:19 AM

    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!

     



  • 2.  RE: Report on unmanaged software

    Posted Mar 05, 2012 10:31 AM

    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



  • 3.  RE: Report on unmanaged software

    Posted Mar 05, 2012 08:31 PM

    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?



  • 4.  RE: Report on unmanaged software
    Best Answer

    Posted Mar 05, 2012 10:39 PM

    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


  • 5.  RE: Report on unmanaged software

    Posted Mar 05, 2012 11:12 PM

    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!



  • 6.  RE: Report on unmanaged software

    Posted Mar 05, 2012 11:18 PM

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



  • 7.  RE: Report on unmanaged software

    Posted Mar 06, 2012 11:26 AM

     

    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')