I'm going off memory and didn't have a database to confirm but the dbo.Inv_AeX_Ac_TCPIP table should have what you need.
I made the changes (noted in red) to your report. It hasn't been tested though.
Hope it helps
Code:
SELECT DISTINCT
i._ResourceGuid [Guid],
i.Name [Host Machine (Target)],
z.[IP Address],
ISNULL(p.[pcA_Version_Major],'') +
CASE WHEN ISNULL(p.[pcA_Version_Minor],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Minor] END +
CASE WHEN ISNULL(p.[pcA_Version_Patch],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Patch] END +
CASE WHEN ISNULL(p.[pcA_Version_Build_Number],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Build_Number] END AS [pcAnywhere Host],
i.[OS Name] [OS]
FROM dbo.Inv_Symantec_pcAnywhere_Host_State hs
JOIN Inv_Symantec_pcAnywhere_Product_Info p
ON p._ResourceGuid = hs._ResourceGuid
JOIN Inv_AeX_AC_Identification i
ON i._ResourceGuid=p._ResourceGuid
JOIN Inv_AeX_AC_TCPIP z
ON z.[IP Address] = i._ResourceGuid
JOIN CollectionMembership cm
ON i._ResourceGuid = cm.ResourceGuid
JOIN vCollection c
ON cm.CollectionGuid = c.Guid
WHERE upper(c.Guid) LIKE upper('%Collection%')
ORDER BY i.Name,
ISNULL(p.[pcA_Version_Major],'') +
CASE WHEN ISNULL(p.[pcA_Version_Minor],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Minor] END +
CASE WHEN ISNULL(p.[pcA_Version_Patch],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Patch] END +
CASE WHEN ISNULL(p.[pcA_Version_Build_Number],'') = '' THEN '' ELSE '.'+ p.[pcA_Version_Build_Number] END,
i.[OS Name]
