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.

SQL Report - CMS7.1 - Add IP address to report.

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

Hello. This is a query to show computers with the Symantec Management Agent.

The SQL query is below, I would like to add a IP address columb to this.

Please can someone assist. Thanks.

SELECT
vc.Name, pu.[User], vc.[OS Name], vc.[System Type], vc.IsManaged, vc.CreatedDate
FROM
vComputer vc
INNER JOIN
Inv_AeX_AC_Primary_User pu ON pu._ResourceGuid = vc.Guid

Comments 2 CommentsJump to latest comment

zman33's picture

hi,

you could use something like this:

SELECT
vc.Name, pu.[User], vc.[OS Name], vc.[System Type], vc.IsManaged, vc.CreatedDate, pu.Month, pu.Year, tcp.[IP Address]
FROM
vComputer vc
INNER JOIN
Inv_AeX_AC_Primary_User pu ON pu._ResourceGuid = vc.Guid
INNER JOIN Inv_AeX_AC_TCPIP tcp ON tcp._ResourceGuid = vc.Guid

but it is very basic version. Also added Month;Year from primary user as it gives a good indication on how much the primary user info is current.

HTH

Z.

network101's picture

Thanks very much I will try your code. I managed to get this code below which is a great report for everyone who ventures down this path.

vc.Name, pu.[User], vc.[OS Name], vc.[System Type], vc.IsManaged, vc.CreatedDate

,vc.[IP Address], s.Model

,dbo.fnInv_LocalizeEnum(ch.[Chassis Package Type],'en-US') AS [Form Factor]

FROM

vComputer vc

INNER JOIN

Inv_AeX_AC_Primary_User pu

ON pu._ResourceGuid = vc.Guid

LEFT OUTER JOIN vHWComputerSystem s

ON s.[_ResourceGuid] = vc.Guid

LEFT OUTER JOIN vHWChassis ch

ON ch._ResourceGuid = vc.Guid

SOLUTION