SCCM Configmgr 2012 SSRS Report Clients assigned to what Management points

Brief information about proxy MP before we jump into the subject. Proxy Management Point(On Secondary Site) used mainly to avoid the excessive bandwidth usage over Slow Network links between clients located in the boundaries of a secondary site

By Default,Proxy management Point and Distribution Point role will be enabled and installed with secondary site. More information about Proxy Management Point,read Technet and msdn blog

The concept of Boundaries are changed in SCCM 2012 comparing with its previous version SCCM 2007.You should configure the Boundaries and Boundary group correctly .To know more ,refer Jason Blog explaining about Secondary Site and boundary Groups

Recently ,I come across request asking for ,how many clients,specific secondary site is supporting /responsible for.

Did you ever come across ,knowing the Client secondary Site Proxy management or Resident Management point ? How to get the Secondary Site code for clients residing in its boundaries into Database ?

As you know,you can not create report or collection based on the Secondary site Code because the site code for clients residing in boundaries of secondary always show as  Primary Site Code(PRI) .

If you look at the Configuration manager applet of client which is residing in the secondary site boundary, you can see proxy Management ,Resident Management Point and its Assigned Management Point information.

Updated: This blog is updated to apply this for Configmgr 2007 with MOF Code (only if you do not have client status reporting tool installed) and also a simple way to get the clients assigned* to Management Point.

For Configmgr 2012:

If you remember in SCCM 2007,v_CH_ClientSummary View handles information about client status like reporting,health etc.This view will be created if you had installed client status reporting tool.

In Configmgr 2012, Client status and client activity information is integrated ,you don’t need to install status reporting tool as you do in Configmgr 2007.

Below is the SQL Query which you can run from SQL Management studio to know the count of clients assigned to Management Points (could be assigned MP or Proxy MP)

Just use the below SQL query to create SSRS Report or use in in SQL management Studio:

select UPPER(SUBSTRING(lastmpservername, 1, CHARINDEX('.', lastmpservername) -1 )) as 'Management Point',
count(lastmpservername) as 'Total Clients'
from v_CH_ClientSummary
group by lastmpservername
order by lastmpservername desc

How simple isn’t it ?

If you want to know the proxy MP or Resident MP etc,then you should consider reading below information.

From the configuration manager applet shown above ,we are sure that,this information is pulling from wmi isn't it ? .we need to find out the Namespace and classes which can be enabled in hardware inventory classes.

Client properties information is stored in root\ccm namespace.You will find interesting information from this name space about client like its client ID,Client ID Change Date, Installed Components, User Logon events etc, which is not available by default in Configmgr Database.To know more about available classes,you can run wbemtest and connect to \\root\ccm name space,click enum classes,recursive and explore the existing classes like SMS_LOCALMP and SMS_MPProxyInformation.

How do we get this information into SCCM Database ?as usual,through MOF file ? Nope ,now we are in 2012 not in 2007.SCCM 2012 made it very easy to enable custom inventory classes.

Go to your primary Site console,administration node—>Client settings—>Click on the properties of Default client settings.(You can not import any custom inventory classes to custom Client agent settings,you should always enable them in default Client agents),click on set Classes from hardware inventory

Click on Add

Click on connect,you can connect to wmi of any SCCM Client computer to get the required WMI classes

connecting to the computer which has sccm client installed.

if you are interested in other classes,you can select them as well.Click ok,ok

Before you confirm the changes,make sure what information do you really required.Click ok and ok on the Default Client agent settings.

These changes are tracked by dataldr.log(F:\sccm\logs) for the creation of views in Database.You see something like this.

It takes a while to load the policy to the client machines(based on machine policy interval) ,send client inventory information to site server to process into Database.

If you need quick results,trigger machine policy and hardware inventory policy on the local server,wait for results to come up in the Database.

The two SQL views created are: V_GS_LOCALMP and V_GS_SMS_MPPROXYINFORMATION
Below is SQL Query to know the count of computers under the Secondary Sites.

select LM.name0 [Secondary Site],LM.sitecode0 [Site Code],LM.mastersitecode0[Master Site],
Count(*) [Total Clients] from v_gs_sms_localmp  LM,v_R_System sys
where sys.ResourceID=lm.resourceid
and sys.Active0=1 and lm.index0=1
group by lm.name0,lm.sitecode0,LM.mastersitecode0

For Configmgr 2007:

If you are running on SCCM 2007 and want to know count of clients how they are assigned ,add the below lines to the bottom of your sms_def.mof file on the central Site(changes will flow down to its child sites).

//-------------------------------Management Point Info---------------//////////////

[ SMS_Report (TRUE),
SMS_Group_Name ("SMS_LocalMP"),
SMS_Class_ID ("MICROSOFT|SMS_LOCALMP|1.0"),
Namespace ("root\\\\ccm") ]
class SMS_LocalMP : SMS_Class_Template
{
[ SMS_Report (TRUE) ]
String     Capabilities;
[ SMS_Report (TRUE) ]
UInt32     Index;
[ SMS_Report (TRUE) ]
String     MasterSiteCode;
[ SMS_Report (TRUE) ]
String     Name;
[ SMS_Report (TRUE) ]
String     Protocol;
[ SMS_Report (TRUE) ]
String     SiteCode;
[ SMS_Report (TRUE) ]
UInt32     Version;
};

//--------------------------End here---------------------//

Note: make sure you replace the quotes (“)with original as blog convert them to fancy otherwise you will get into trouble .

After you save the file,monitor the logs for any errors as said above,wait for sometime and you are ready to create report. You can Use above SQL query .