hey there guys i'm struggling to add extra columns to the "COMPUTER SUMMARY" (.jpg attached) I'm editing I can add on of the columns I need table "Inv_UG_User_Account" column "Full name".
But when i add it I get three duplicate entries.
All I need to add to add is the full name of the user; i found the details in the "Inv_UG_User_Account" table and I need to add the users location aswell.
I do recall find both the information inthe exchange information in ns6; just really struggling with ns7 at the moment.
Can anyone help
COMPUTER SUMMARY QUERY:
SELECT DISTINCT i.Guid,
CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name])) ELSE i.[Name] END [Name],
CASE WHEN i.Domain = '' AND CHARINDEX('.', i.[Name]) <> 0 THEN REPLACE (i.[Name],SUBSTRING (i.[Name],0,CHARINDEX ('.',i.[Name]))+ '.','') ELSE i.Domain END [Domain],
hs.[Vendor Name] [Manufacturer],
sn1.[Model] [Computer Model],
cpu.[Max Clock Speed (Mega-hertz)] [CPU Frequency],
cpu.[Family] [CPU Type],
hs.[Total RAM] / (1024 * 1024) [Memory (MB)],
ISNULL (i.[OS Name],os.[Name]) [Operating System],
d.[OS Version] [OS Version],
hs.[Total fixed drive size] / (1024) [Disk Size(MB)],
ld.[Free Space (Bytes)] [Free Space(MB)],
sn1.[Identifying Number] [Serial Number]
FROM dbo.vComputer i
JOIN dbo.ScopeMembership sm
ON sm.[ResourceGuid] = i.Guid
AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
LEFT JOIN dbo.Inv_AeX_AC_Identification d
ON d._ResourceGuid = i.Guid
JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = i.[Guid]
JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
LEFT JOIN dbo.vHWPhysicalMemory m1
ON m1.[_ResourceGuid] = i.Guid
LEFT JOIN dbo.vHWComputerSystem sn1
ON sn1.[_ResourceGuid] = i.Guid
LEFT JOIN dbo.vHWProcessor cpu
ON cpu.[_ResourceGuid] = i.[Guid]
LEFT JOIN (SELECT SUM ([Free Space (Bytes)] / (1024 * 1024)) [Free Space (Bytes)], _ResourceGuid
FROM dbo.vHWLogicalDisk GROUP BY _ResourceGuid) ld
ON i.Guid = ld._ResourceGuid
LEFT JOIN dbo.vOSOperatingSystem os
ON os._ResourceGuid = i.Guid
JOIN dbo.Inv_Operating_System_Summary oss
ON oss.ComputerResourceGuid = i.Guid
JOIN dbo.Inv_Hardware_Summary hs
ON hs.ComputerResourceGuid = i.Guid
WHERE 1 = 1
AND LOWER (i.[Name]) LIKE LOWER ('%ComputerName%')
AND i.Domain LIKE '%Domain%'
AND lower (it.[Guid]) LIKE lower ('%Filter%')
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'