Client Management Suite

Hi,
I'm looking to add two fields [Manufacturer] and [Model] from table dbo.vHWComputers to an existing report (Systems with Windows 7 Capable Hardware). 

Any help appreciated - Thanks

DECLARE @v1_TrusteeScope nvarchar(max)

SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT DISTINCT i.Guid Guid,

i.[Name] [Computer],

i.[OS Name] [Operating System],

d.[OS Revision]  [Service Pack],

CASE WHEN LOWER (i.[System Type]) LIKE 'win32' THEN '32-bit' ELSE '64-bit' END 'Architecture [32/64-bit]',

hp.[Max Clock Speed (Mega-hertz)] / (1000) [CPU Speed (GHz)],

CAST (hpm.[Total Physical Memory (Bytes)] / (1024.0 * 1024.0) AS DECIMAL (10,2)) [Memory (MB)],

a.[Hard DISK Size] [Hard Drive Total (GB)],

ISNULL (b.[Hard Disk Free Space], 0) [Hard Drive Free (GB)],

ISNULL (CAST (c.[Video Memory] AS NVARCHAR (MAX)),0) [Video memory (MB)]

FROM dbo.vComputer i

JOIN dbo.Inv_AeX_AC_Identification d

                ON d._ResourceGuid = i.Guid

JOIN dbo.ScopeMembership sm

                ON sm.[ResourceGuid] = i.Guid

                AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))

JOIN dbo.Inv_HW_Processor hp

                ON hp._ResourceGuid = i.Guid

JOIN dbo.vHWComputerSystem hpm   

                ON hpm._ResourceGuid = i.Guid

JOIN (SELECT CAST (SUM ([Max Media Size (Kilobytes)] / (1024.0 * 1024.0))  AS DECIMAL (10,2)) [Hard DISK Size],_ResourceGuid FROM vHWStorage WHERE [Media Type] = 29 GROUP BY _ResourceGuid) a

                ON a._ResourceGuid = i.Guid

JOIN (SELECT MAX (t.[Hard Disk Free Space]) [Hard Disk Free Space], t.Guid FROM (SELECT ld._ResourceGuid AS Guid,

                CAST (SUM (ld.[Free Space (Bytes)] / (1024.0 * 1024.0 * 1024.0))  AS DECIMAL (10,2)) AS 'Hard Disk Free Space',

                ld.[Device ID]

                FROM dbo.vHWLogicalDisk ld

                JOIN dbo.vHWLogicalDiskBasedOnPartition ld1

                                ON ld._ResourceGuid = ld1._ResourceGuid

                                AND ld.[Device ID] = ld1.Dependent

                JOIN dbo.vHWStorageToDiskPartition s1

                                ON s1._ResourceGuid = ld._ResourceGuid         

                                AND (s1.Dependent = ld1.Antecedent OR s1.Dependent = ld.[Device ID])

                JOIN dbo.vHWStorage sh

                                ON sh._ResourceGuid = s1._ResourceGuid        

                                AND sh.[Device ID] = s1.Antecedent

                                AND sh.[Media Type] = 29

                GROUP BY ld._ResourceGuid, ld.[Device ID])t

                GROUP BY t.Guid) b

                ON b.Guid = i.Guid

LEFT JOIN (SELECT CAST (MAX ([Adapter RAM (Bytes)]) / (1024.0 * 1024.0)  AS DECIMAL (10,2)) [Video Memory], _ResourceGuid FROM vHWDisplayController GROUP BY _ResourceGuid)c

                ON c._ResourceGuid = i.Guid

WHERE LOWER (i.[System Type]) LIKE 'win%'

AND i.[OS Name] NOT LIKE '%7%'

AND i.Domain LIKE '%Domain%'

AND LOWER (i.[Name]) LIKE LOWER ('%Computer Name%')

AND LOWER (i.[System Type]) LIKE '%OS Architecture%'

AND hp.[Max Clock Speed (Mega-hertz)] / (1000.0) >= '%CPU%'

AND CAST (hpm.[Total Physical Memory (Bytes)] / (1024.0 * 1024.0) AS DECIMAL (10,2)) >= '%Memory%'

AND ISNULL (b.[Hard Disk Free Space], 0) >= '%Free Space%'

Attachment(s)

Windows 7 compatiable.docx   14 KB 1 version

Model and Manufacturer are already in the view vHWComputerSystem, which is joined as hpm.

So it should be as easy as Selecting hpm.Model and hpm Manufacturer additional to the rest.

Yeah you just need to insert the two fields into the select since the table is already being joined...

---