SCCM Query to Get Screen Resolutions

2014-08-04 ConfigMgr SQL
Share on:

I wrote the following queries to find the video resolutions used by clients reporting into SCCM. They currently only look at Windows Workstations 6.x (excludes servers, and also anything older than Windows Vista or whatever v7 ends up being), and only those who have submitted a hardware inventory in the last 30 days. Feel free to steal & modify.

This query lists every card with resolution:

 1/* List of Video Cards with Resolutions */
 2select vid.AdapterCompatibility0
 3     , vid.name0
 4	 , vid.CurrentHorizontalResolution0
 5	 , vid.CurrentVerticalResolution0
 6	 , cast(vid.CurrentHorizontalResolution0 as varchar) + 'x' + cast(vid.CurrentVerticalResolution0 as varchar) as ResolutionFinal
 7	 , sys.Operating_System_Name_and0
 8  from v_r_system sys
 9  join v_GS_VIDEO_CONTROLLER as vid
10    on sys.resourceid = vid.ResourceID
11  join v_GS_WORKSTATION_STATUS hw
12    on sys.resourceid = hw.ResourceID
13 where vid.CurrentHorizontalResolution0 is not null
14   and vid.CurrentVerticalResolution0 is not null
15   and sys.Operating_System_Name_and0 like '%Workstation%6.%'
16   and hw.LastHWScan >= GetDate()-30

This query sums up the resolutions reported in the previous query:

 1/* Summary of Video Card Resolutions */
 2select cast(vid.CurrentHorizontalResolution0 as varchar) + 'x' + cast(vid.CurrentVerticalResolution0 as varchar) as ResolutionFinal
 3     , count(*)
 4  from v_r_system sys
 5  join v_GS_VIDEO_CONTROLLER as vid
 6    on sys.resourceid = vid.ResourceID
 7  join v_GS_WORKSTATION_STATUS hw
 8    on sys.resourceid = hw.ResourceID
 9 where vid.CurrentHorizontalResolution0 is not null
10   and vid.CurrentVerticalResolution0 is not null
11   and sys.Operating_System_Name_and0 like '%Workstation%6.%'
12   and hw.LastHWScan >= GetDate()-30
13 group by cast(vid.CurrentHorizontalResolution0 as varchar) + 'x' + cast(vid.CurrentVerticalResolution0 as varchar)
14 order by count(*) desc;

No comments