SCCM Dashboard Queries 1

So I recently went through the process of setting up the SCCM Dashboard from the MS Solution Accelerator team. I wont bore you with the setup as its well documented elsewhere.

By default the dashboard only has a few built in XML files for things like Update compliance, software deployment status etc

There wasnt much documentation to acompany the dashboard until recently when I saw this blog from this guy http://blogs.msdn.com/b/shitanshu/archive/2010/04/08/part-1-how-microsoft-it-using-configuration-manager-dashboard.aspx

So after viewing that blog I thought to myself, what sort of views do I think would be good in my Dashboard.

Below are some of the views I have setup for Computer summary data.

I am by no means a SQL guy so if any of these queries are really inefficient then post a comment with your better suggestions.

View 1: Top 10 Computer Models

select top 10 machine.model0 as 'Computer Model', count (*) as 'Client Count'

from SCCM_Ext.vex_GS_COMPUTER_SYSTEM machine

group by machine.model0
order by count (*) desc

View 2: Machine Count per SMS Site

SELECT
CASE sms_assigned_sites0
WHEN 'AAA' THEN 'Site1'
WHEN 'BBB' THEN 'Site2'
END AS "Region and SiteCode", count(distinct name0) as ClientCount
FROM v_RA_System_SMSAssignedSites sas join v_R_System sys on sas.resourceID=sys.resourceID
WHERE client0=1 AND obsolete0=0 AND Active0=1
GROUP BY CASE sms_assigned_sites0

WHEN 'AAA' THEN 'Site1'
WHEN 'BBB' THEN 'Site2'

END
ORDER by 'ClientCount' desc

View 3: Computer Chasis type (We only manage desktops/notebooks)

SELECT
CASE ChassisTypes0
WHEN '10' THEN 'Notebooks'
ELSE 'Desktops'
END AS "Workstation Type", count(distinct sys.name0) as ClientCount from
v_GS_SYSTEM_ENCLOSURE ENC
INNER JOIN
v_R_System SYS ON ENC.ResourceID = SYS.ResourceID
WHERE
sys.client0=1 AND sys.obsolete0=0 AND active0=1
GROUP BY
CASE ChassisTypes0
WHEN '10' THEN 'Notebooks'
ELSE 'Desktops'
END
ORDER BY 2 desc

Thats all for this post, next time I will cover software summary data for Microsoft Office Apps.