Tuesday 13 July 2010

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.

No comments:

Post a Comment