Custom SCCM report to help debug MBAM client rollout

Ground control to Major Tom,

Okay, so this might not be the most common thing to be looking for but anyways…

I've just done a rollout of MBAM (Microsoft Bitlocker Administration and Monitoring, part of MDOP) for a customer and found an interesting scenario:

What is the best way to find out which MBAM clients have not contacted the MBAM server?

(For different reasons some of the clients did not register with the MBAM server, even though the client was successfully installed.)

As the customer in this case of course is using SCCM I created a custom SCCM report using the Report Builder that pulls data from the SCCM database containing computers that have the MBAM agent installed and compare this to the clients that have actually reported to the MBAM database.

This could be helpfull when you are doing a large scale rollout of MBAM and want a some help in pointing out the "problem clients".

You can of course change the "look and feel" of the report in the report builder, but here is an example:

I guess the important thing is the SQL queries I used so here we go:

First of all you need two datasources in Report Builder, one for each databaseserver/database.

I then created 4 datasets/queries.

Dataset_MBAM and Dataset_SCCM lists all MBAM clients registerted in the SCCM and MBAM database.

(For MBAM use the DataSource_MBAM and for SCCM DataSource_SCCM of course...)

Dataset_MBAM:

SELECT

ComplianceCore.MachineCompliance.Name

FROM

  ComplianceCore.MachineCompliance

Dataset_SCCM:

Select v_R_System.Netbios_Name0

FROM v_Add_Remove_Programs

JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID 

WHERE v_Add_Remove_Programs.DisplayName0 like '%MBAM%'

The result from these queries are put into two Report Builder parameters (@FromMBAM and @FromSCCM)

These parameters/variables are then used in the two datasets listing out the diffrences between the SCCM and MBAM databases:

Dataset_NotInMBAM: (Listing all MBAM clients that exists in SCCM but not in MBAM DB)

Select v_R_System.Netbios_Name0

FROM v_Add_Remove_Programs

JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID 

WHERE v_Add_Remove_Programs.DisplayName0 like '%MBAM%' AND v_R_System.Netbios_Name0 NOT IN (@FromMBAM)

Dataset_NotInSCCM: (Listing all MBAM clients that exists in MBAM but not in MBAM DB)

SELECT

ComplianceCore.MachineCompliance.Name

FROM

  ComplianceCore.MachineCompliance

WHERE ComplianceCore.MachineCompliance.Name NOT IN (@FromSCCM)