SCCM Dashboard Queries 4: Machine Types in a collection

So sometimes your manager might ask you "how many laptops/desktops are there at site X?"

You probably have a collection setup for site X but how do you answer your managers question quickly and easily?

Better yet, let him answer that question on his own!

This is a view I use to show how many Notebooks/Desktops there are in a collection based on the enclosure chassistypes attribute.

Your mileage may vary but have a look and see what you think.

View: Machine Types for Collection X
Query:

DECLARE @CollectionViewName As nvarchar(100)
SET @CollectionViewName = (SELECT MemberClassName FROM v_Collection WHERE Name = @ViewName)
EXEC ('SELECT CASE ChassisTypes0 WHEN ''8'' THEN ''Notebooks'' WHEN ''9'' THEN ''Notebooks'' WHEN ''10'' THEN ''Notebooks'' ELSE ''Desktops'' END AS "Workstation Type", count(distinct sys.name0) as ClientCount from
v_GS_SYSTEM_ENCLOSURE ENC INNER JOIN (SELECT DISTINCT ResourceId FROM dbo.' + @CollectionViewName + ') v ON ENC.ResourceId = v.ResourceId INNER JOIN v_R_System SYS ON ENC.ResourceID = SYS.ResourceID WHERE enc.chassistypes0 <> ''12'' and sys.client0=1 AND sys.obsolete0=0 AND active0=1 GROUP BY CASE ChassisTypes0 WHEN ''8'' THEN ''Notebooks'' WHEN
''9'' THEN ''Notebooks'' WHEN ''10'' THEN ''Notebooks'' ELSE ''Desktops'' END ORDER BY 2 desc')
 
 

I filter out chassistype 12 as that is a 'docking station' so I dont want to see those. I use a pie chart to display the data but tweak it as you like.

SCCM Dashboard Queries 3: AD Views

One of the more obscure uses for the SCCM dashboard is reporting on AD.

SCCM does afterall poll AD to collect your user accounts, groups and machine accounts.

With a bit of tweaking we can get AD reports from the SCCM dashboard quite easily.


Lets say your a good admin and your AD is fully populated with accurate user data like country, office etc we can use this to create charts of your AD user breakdown.




Lets look at my AD account, my country/region is specified as United Kingdom. This value is not collected by the SCCM User Discovery by default so lets go change it.



Go into your SCCM console>site management>siteID>site settings>discovery methods


If you double-click on the Active Directory User Discovery the properties box appears.


Next we want to click on the Active Directory Attribute Tab.


Click on the New Button (little yellow Star icon)


It will ask you for the Attibute name. AD exposes the Country/Region Value as 'C' (just the letter on its own) and click ok.


You should see it appear in your list now.



You can either now wait for your polling schedule to kick in or tick the box 'run discovery as soon as possible' to speed things up.



So now all our AD users have a country we can use that to build a SQL query for our dashboard.



This is a simple query:


SELECT

dbo.v_R_User.c0 as 'Country', count(*) AS 'Users'

FROM dbo.v_R_User

where dbo.v_R_User.c0 not like 'NULL'

group by dbo.v_R_User.c0
order by 'Users' desc





The country data is returned as the country short code, I present this data as a chart with a data grid below it but you can do as you please with it. You could use a CASE statement to translate the country code into something more elegant but for my purposes it works fine as is.

SCCM Dashboard Queries 2: Office Apps

So in part 1 I covered 3 basic computer summary views I use.

As in many businesses, my work like to keep an eye on the install count for various Microsoft apps like Office/Visio/Project for license reconciliation.

SCCM has some good reporting but sometimes its better to just give managers an up to date pretty chart or a nice table to look at, thus freeing up your own time to focus on important stuff.

Some of the App views I use in my SCCM Dashbaord are below, these are for Office and Visio/Project versions but can be adapted to any product displayname like Adobe Reader, AutoCAD etc

I normally use a datagrid with the product name and count from these:

View 1: Office Suite Versions
select
DisplayName0,
Count (Distinct arp.ResourceID) as 'Client Count'
From
dbo.v_Add_Remove_Programs ARP
Where
DisplayName0 in ( 'Microsoft Office Professional Edition 2003','Microsoft Office Standard Edition 2003','Microsoft Office Enterprise 2007','Microsoft Office Standard 2007','Microsoft Office Professional Plus 2007','Microsoft Office 2000 SR-1 Professional','Microsoft Office 2000 SR-1 Standard','Microsoft Office Professional Plus 2010','Microsoft Office Standard 2010','Microsoft Office Enterprise 2007')

Group by DisplayName0
Order by Count(*) desc


View 2: Visio/Project Version Count

select
DisplayName0,
Count (Distinct arp.ResourceID) as 'Client Count'
From
dbo.v_Add_Remove_Programs ARP
Where
DisplayName0 in ('Microsoft Visio Standard 2010','Microsoft Visio 2010 (Technical Preview)','Microsoft Office Visio Standard 2007','Microsoft Office Visio Standard 2003','Microsoft Office Visio Professional 2007 Trial','Microsoft Office Visio Professional 2007','Microsoft Office Visio Professional 2003','Microsoft Office Visio 2010','Microsoft Office Project Standard 2007 Trial','Microsoft Office Project Standard 2007','Microsoft Office Visio Professional 2007','Microsoft Office Project Professional 2010','Microsoft Office Project Professional 2007 Trial','Microsoft Office Project Professional 2007','Microsoft Office Project Professional 2003','Microsoft Office Visio Professional 2010','Microsoft Office Visio Professional 2010 Trial','Microsoft Office Project Standard 2010')

Group by DisplayName0
Order by Count(*) desc

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.

My first blog....

So its my first blog post......

Well what will be the pupose of this blog?

In my time as an Microsoft IT Pro I have used other peoples blogs for IT howto's, hints & tips, and all round general IT stuff. Sometimes spending ages looking through Google results trying to help find that alusive fix for a problem.

So I thought why not make my mark and contribute something to help others during their time of need.

I intend to keep this blog up to date with my experiences of using and troubleshooting mostly Microsoft products like AD, Exchange, Powershell, SCCM, SCOM and others

Hopefully the info you find helps you along your way.