Although the Windows Vista Hardware Assessment 2.1 tool is available, and extremely detailed, I wanted to use SMS 2003 to create a web report that could provide a quick assessment of our hardware environment as we prepare for migration to Microsoft Vista Enterprise. I began looking at the canned reports #13 and #15 to see if I could build on them and after a bit of web surfing, head scratching, and some very helpful tips and suggestions from Todd Hemsell and Garth Jones, I was able to put together a query that gets close to what I needed. The only thing that is really missing is the DirectX version data and Windows Aero requirements (where is the information that tells you exactly what to look for??). Garth mentioned that there is a DirectX class available in the Monster.mof but I haven't entered that into my system yet.
Here is the query statement for the web report:
SELECT DISTINCT
SYS.Netbios_Name0, fcm.SiteCode, CASE WHEN SYS.Operating_System_Name_and0 LIKE '% Server %' THEN '*' ELSE ' ' END AS OS,
OPSYS.Caption0 AS C054, OPSYS.Version0, CASE WHEN PROC1.MaxClockSpeed0 < '999' THEN '*' ELSE ' ' END AS P, PROC1.MaxClockSpeed0,
CASE WHEN MEM.TotalPhysicalMemory0 < '1000000' THEN '*' ELSE ' ' END AS M, ROUND(MEM.TotalPhysicalMemory0 / 1024.00, 0)
AS [Memory (MB)], CASE WHEN VC.AdapterRAM0 < '130000' THEN '*' ELSE ' ' END AS V, ROUND(VC.AdapterRAM0 / 1024.00, 2) AS [Video RAM (Mb)],
CASE WHEN VC.CurrentBitsPerPixel0 < '32' THEN '*' ELSE ' ' END AS B, VC.CurrentBitsPerPixel0 AS [Bits Per Pixel],
CASE WHEN LDISK.FreeSpace0 < '15000' THEN '*' ELSE ' ' END AS D, ROUND(LDISK.FreeSpace0 / 1024.00, 2) AS [Free Disk Space (GB)],
dbo.v_GS_WORKSTATION_STATUS.LastHWScan
FROM v_FullCollectionMembership fcm JOIN
v_R_System SYS ON fcm.ResourceID = SYS.ResourceID JOIN
v_GS_PROCESSOR PROC1 ON SYS.ResourceID = PROC1.ResourceID JOIN
v_GS_X86_PC_MEMORY MEM ON SYS.ResourceID = MEM.ResourceID JOIN
v_GS_OPERATING_SYSTEM OPSYS ON SYS.ResourceID = OPSYS.ResourceID JOIN
v_GS_VIDEO_CONTROLLER VC ON SYS.ResourceID = VC.ResourceID JOIN
v_GS_WORKSTATION_STATUS ON SYS.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID AND LDISK.DeviceID0 = SUBSTRING(OPSYS.WindowsDirectory0, 1, 2)
WHERE (FCM.CollectionID = @CollID) AND (SYS.Operating_System_Name_and0 NOT LIKE '% Server %') AND (VC.AdapterRam0 > '1') AND
(VC.CurrentBitsPerPixel0 > '1')
ORDER BY OS, P, M, V, B, D, SYS.Netbios_Name0 DESC
And here is the Prompt Query Information:
Name: CollID
Prompt Test: Enter Collection ID
Check "Provide a SQL statement
Prompt SQL statement:
begin
if (@__filterwildcard = '')
SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
else
SELECT DISTINCT CollectionID, Name FROM v_Collection
WHERE CollectionID like @__filterwildcard
ORDER BY Name
end