Video Screencast Help

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