Video Screencast Help
Scheduled Maintenance: Symantec Connect is scheduled to be down Saturday, April 19 from 10am to 2pm Pacific Standard Time (GMT: 5pm to 9pm) for server migration and upgrades.
Please accept our apologies in advance for any inconvenience this might cause.

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