IT Management Suite

 View Only
  • 1.  Installed Software by Computer w/IP and Domain

    Posted Jun 22, 2016 08:42 AM

    Hi,

    Currently I need to produce a report that list the software installed on some servers. The issue is all the servers have the same name but are in different domains. Essentially I would like to take the pre-existing "Installed Software by Computer report and add 2 more columns, one for the IP address and another to show what domain the computer is in. I've tried scraping together query into this one (from other posts) but they get a data error.

    This is the query thats currently being used in the built in report that i want to add the extra info into.. Thanks for any help.

    DECLARE @v1_TrusteeScope nvarchar(max)
    SET @v1_TrusteeScope = N'%TrusteeScope%'
     
    IF ('%Type%' = 'Managed Software')
    SELECT DISTINCT comps.Guid [Guid]
           ,comps.Name [Computer Name]
                      ,prods.[Name]
                      ,spv.[Version]
                      ,company.Name [Company]
    FROM vRM_Software_Component sc
    JOIN Inv_InstalledSoftware inst
       ON inst._SoftwareComponentGuid = sc.Guid
       AND inst.InstallFlag = 1
    JOIN (SELECT ra.ChildResourceGuid, spi.Guid, spi.Name 
          FROM vRM_Software_Product_Item spi
          JOIN ResourceAssociation ra
             ON ra.ParentResourceGuid = spi.Guid
                                  AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
                   )prods
       ON prods.ChildResourceGuid = sc.Guid
    JOIN dbo.Inv_Software_Product_State sps
        ON sps._ResourceGuid = prods.Guid
        AND sps.IsManaged = 1
    JOIN dbo.Inv_Software_Product_Version spv
                                  ON spv._ResourceGuid = prods.Guid
    JOIN (SELECT vci.Guid, vci.Name
          FROM vRM_Computer_Item vci
          LEFT JOIN ResourceAssociation resAssoc
             ON vci.Guid = resAssoc.ParentResourceGuid
                        AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
                                  JOIN ScopeMembership sm ON sm.ResourceGuid = vci.Guid
          JOIN fnGetTrusteeScopeCollections (@v1_TrusteeScope) tsc ON tsc.ScopeCollectionGuid = sm.ScopeCollectionGuid
          WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
          --AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
          ) comps
       ON comps.Guid = inst._ResourceGuid
    LEFT JOIN (SELECT ra.ParentResourceGuid AS softProdGuid, rc.Name
               FROM RM_ResourceCompany rc
               JOIN ResourceAssociation ra
                  ON ra.ChildResourceGuid = rc.Guid
                                        AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D'
                                     ) company
       ON company.softProdGuid = spv._ResourceGuid
                                  
    WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))
    ORDER BY prods.Name, comps.Name
     
    ELSE
    IF ('%Type%' = 'All Software')       
    SELECT DISTINCT comps.Guid [Guid]
                   ,comps.Name [Computer Name]
                              ,sci.[Name]
                              ,isc.[Version]
                              ,company.[Name] [Company]
    FROM vRM_Software_Component_Item sci
    JOIN Inv_InstalledSoftware inst
       ON inst._SoftwareComponentGuid = sci.[Guid]
       AND inst.InstallFlag = 1
    JOIN (SELECT vci.Guid, vci.Name
          FROM vRM_Computer_Item vci
          LEFT JOIN ResourceAssociation resAssoc
             ON vci.Guid = resAssoc.ParentResourceGuid
                        AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
          JOIN ScopeMembership sm ON sm.ResourceGuid = vci.Guid
          JOIN fnGetTrusteeScopeCollections (@v1_TrusteeScope) tsc ON tsc.ScopeCollectionGuid = sm.ScopeCollectionGuid
                     WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
          --AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))  
          ) comps
       ON comps.Guid = inst._ResourceGuid
    JOIN dbo.Inv_Software_Component isc
       ON isc._ResourceGuid = sci.[Guid]
    LEFT JOIN (SELECT vc.Name, ra.ParentResourceGuid AS SoftCompGuid
               FROM RM_ResourceCompany vc
                                     JOIN ResourceAssociation ra
                                     ON vc.Guid = ra.ChildResourceGuid
                                     AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company
       ON company.SoftCompGuid = sci.[Guid]
     
    WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))
    ORDER BY sci.Name, comps.Name
    

     



  • 2.  RE: Installed Software by Computer w/IP and Domain

    Posted Jul 12, 2016 05:19 AM

    Here is a modified query which should give you desired extra columns:

     

    DECLARE @v1_TrusteeScope nvarchar(max)
    SET @v1_TrusteeScope = N'%TrusteeScope%'
     
    IF ('%Type%' = 'Managed Software')
    SELECT DISTINCT comps.Guid [Guid]
           ,comps.Name [Computer Name]
           ,comps.Domain
                   ,comps.[IP Address]
                      ,prods.[Name]
                      ,spv.[Version]
                      ,company.Name [Company]
    FROM vRM_Software_Component sc
    JOIN Inv_InstalledSoftware inst
       ON inst._SoftwareComponentGuid = sc.Guid
       AND inst.InstallFlag = 1
    JOIN (SELECT ra.ChildResourceGuid, spi.Guid, spi.Name
          FROM vRM_Software_Product_Item spi
          JOIN ResourceAssociation ra
             ON ra.ParentResourceGuid = spi.Guid
                                  AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
                   )prods
       ON prods.ChildResourceGuid = sc.Guid
    JOIN dbo.Inv_Software_Product_State sps
        ON sps._ResourceGuid = prods.Guid
        AND sps.IsManaged = 1
    JOIN dbo.Inv_Software_Product_Version spv
                                  ON spv._ResourceGuid = prods.Guid
    JOIN (SELECT vci.Guid, vci.Name, ide.Domain, tcp.[IP Address]
          FROM vRM_Computer_Item vci
          LEFT JOIN dbo.Inv_AeX_AC_Identification AS ide ON ide._ResourceGuid = vci.Guid
          LEFT JOIN dbo.vTcpIpAddress AS tcp ON tcp._ResourceGuid = vci.Guid
          LEFT JOIN ResourceAssociation resAssoc
             ON vci.Guid = resAssoc.ParentResourceGuid
                        AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
                                  JOIN ScopeMembership sm ON sm.ResourceGuid = vci.Guid
          JOIN fnGetTrusteeScopeCollections (@v1_TrusteeScope) tsc ON tsc.ScopeCollectionGuid = sm.ScopeCollectionGuid
          WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
          --AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
          ) comps
       ON comps.Guid = inst._ResourceGuid
    LEFT JOIN (SELECT ra.ParentResourceGuid AS softProdGuid, rc.Name
               FROM RM_ResourceCompany rc
               JOIN ResourceAssociation ra
                  ON ra.ChildResourceGuid = rc.Guid
                                        AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D'
                                     ) company
       ON company.softProdGuid = spv._ResourceGuid
                                  
    WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))
    ORDER BY prods.Name, comps.Name
     
    ELSE
    IF ('%Type%' = 'All Software')       
    SELECT DISTINCT comps.Guid [Guid]
                   ,comps.Name [Computer Name]
                   ,comps.Domain
                   ,comps.[IP Address]
                              ,sci.[Name]
                              ,isc.[Version]
                              ,company.[Name] [Company]
    FROM vRM_Software_Component_Item sci
    JOIN Inv_InstalledSoftware inst
       ON inst._SoftwareComponentGuid = sci.[Guid]
       AND inst.InstallFlag = 1
    JOIN (SELECT vci.Guid, vci.Name, ide.Domain, tcp.[IP Address]
          FROM vRM_Computer_Item vci
          LEFT JOIN dbo.Inv_AeX_AC_Identification AS ide ON ide._ResourceGuid = vci.Guid
          LEFT JOIN dbo.vTcpIpAddress AS tcp ON tcp._ResourceGuid = vci.Guid
          LEFT JOIN ResourceAssociation resAssoc
             ON vci.Guid = resAssoc.ParentResourceGuid
                        AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
          JOIN ScopeMembership sm ON sm.ResourceGuid = vci.Guid
          JOIN fnGetTrusteeScopeCollections (@v1_TrusteeScope) tsc ON tsc.ScopeCollectionGuid = sm.ScopeCollectionGuid
                     WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
          --AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))  
          ) comps
       ON comps.Guid = inst._ResourceGuid
    JOIN dbo.Inv_Software_Component isc
       ON isc._ResourceGuid = sci.[Guid]
    LEFT JOIN (SELECT vc.Name, ra.ParentResourceGuid AS SoftCompGuid
               FROM RM_ResourceCompany vc
                                     JOIN ResourceAssociation ra
                                     ON vc.Guid = ra.ChildResourceGuid
                                     AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company
       ON company.SoftCompGuid = sci.[Guid]
     
    WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))
    ORDER BY sci.Name, comps.Name

     

    The easiest (but maybe a bit less accurate) would be just to replace vRM_Computer_Item by vComputer and add the extra columns