Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Query for all software with IP from NS server

Created: 11 Aug 2013 • Updated: 12 Aug 2013 | 2 comments
This issue has been solved. See solution.

Hi support,
Anyone can give me a query for all software report with ip.
I mean how can i get a report like what software has installed in a machine with IP address from NS server.

Operating Systems:

Comments 2 CommentsJump to latest comment

Sachin Sawant's picture

Hi,

please find this query,

DECLARE @v1_TrusteeScope nvarchar(max)

SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT DISTINCT comps.Guid [Guid]

               ,comps.Name [Computer Name]

                       ,sci.[Name]

                       ,isc.[Version]

                       ,company.[Name] [Company]

,Inv_AeX_AC_TCPIP.[Ip Address]

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

      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 Inv_AeX_AC_TCPIP

ON Inv_AeX_AC_TCPIP.[Host Name]=comps.Name

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]

SOLUTION
security_admin's picture
Hi PS_Mahi,
 
check this query software with IP
 
 
SELECT dbo.Inv_AeX_AC_Identification.[Name][Host Name]
, dbo.Inv_AeX_AC_TCPIP.[IP Address]
, dbo.Inv_AddRemoveProgram.[DisplayName],[Publisher][Company],[installDate]
 
FROM
dbo.Inv_AeX_AC_Identification,
dbo.Inv_AeX_AC_TCPIP,
dbo.Inv_AddRemoveProgram
 
WHERE 
dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_AeX_AC_TCPIP._ResourceGuid
AND dbo.Inv_AeX_AC_Identification._ResourceGuid=dbo.Inv_AddRemoveProgram._ResourceGuid