Using SCCM how to check if the user mailbox is migrated to exchange online (cloud) from on-prem exchange

 

Introduction:

we are in process of migrating users (mailbox) from on-prem to office 365 (Cloud).As part of this project ,one of the requirement is to deploy office 365 proplus (C2R) application to all users replacing old version of Microsoft Office. We use Powershell Application deployment kit which simplifies the complex scripting challenges of deploying applications in the enterprise, provides a consistent deployment experience and improves installation success rates.

Once users have got office 365 proplus and other office 365 components like Microsoft Teams,yammer,Onedrive etc ,there will be final task to migrate user mailbox to cloud. Mailbox migration can be the first or middle or last ,no sequence as it is independent task.

Deployment of office proplus and other components are done by SCCM hence we can create some nice dashboard /reports to monitor the progress of the deployments, but for some reason ,we are missing the mailbox migration status which happens from on-prem exchange server to exchange online (EOL).

How do we get the status of mailbox migration from on-prem to exchange online using SCCM ?

I am not exchange guy, hence i may not be able to provide much information about the theory behind this and if any questions around exchange online or mailbox migration ,you can reach out to TechNet forums or contact Microsoft support.

when the mailbox is moved (sync and cutover) from on-prem to exchange online ,there are couple of attributes that are set in Active directory .some of them are listed below.

msExchVersion
msExchRecipientDisplayType
msExchRecipientTypeDetails
msExchRemoteRecipientType
targetAddress

By default, then the user mailbox is on-prem ,the targetAddress attribute is set to empty (it does not contain any value). Once the user mailbox is moved to cloud ,this attribute is set with username@yourtenantname.mail.onmicrosoft.com

For example ,user email address is Demo1@eskor.com and after the migration ,targetAddress is set to Demo1@koneti.mail.onmicrosoft.com (where koneti is my tenant name).

Once this attribute is stamped with cloud email ,we can use SCCM to discover this attribute using AD user discovery and put that info in SSRS report.

A quick way to view an objects Active Directory targetAddress attribute is through the Active Directory Users and Computers panel. In AD Users and Computers, ensure that Advanced Features has been enabled under the View menu.

Go to the OU,locate the object that you are looking for ,right click on user properties ,choose attribute Editor ,locate targetAddress

How do we discover this attribute into SCCM ?

Go to your SCCM console ,Administration,Hierarchy configuration ,discovery method and choose Active Directory User Discovery.

From the available attributes ,choose targetAddress and click on Add ,click Ok

Once this is done, you will need to wait for the user discovery happen (delta discovery ) or you can force the discovery cycle by right click on discovery method.

After the discovery runs, you will have targetaddress0 in v_r_user SQL view to create nice SSRS reports.

couple of SQL views that i used to create SSRS report with office 365 proplus installation ,user mail,user name,cloud information and user group are listed below.

v_r_user

v_GS_OFFICE365PROPLUSCONFIGURATIONS

v_RA_User_UserGroupName

v_R_System

and finally SSRS report:

 

Even though the report is more customized as per the client requirement ,lot of people have requested in the comments section to post the SQL query so they can modify it as per their needs.

I listen all comments and posting the SQL code below:

 

with cte as
(
select u.User_Name0,u.Mail0,u.Full_User_Name0,u.Windows_NT_Domain0,u.targetaddress0
from v_RA_User_UserGroupName ug
inner join v_R_User u on u.ResourceID=ug.ResourceID
inner join v_FullCollectionMembership fcm on fcm.ResourceID=u.ResourceID
where (ug.User_Group_Name0 like '%O365Users'
      OR ug.User_Group_Name0 like '%IntuneUsers'
     OR ug.User_Group_Name0 like  '%O365LicenseGroup' )
      and fcm.CollectionID ='PS1004CD'
     group by u.User_Name0,u.Mail0,u.Full_User_Name0,u.Windows_NT_Domain0,u.targetaddress0
)
select cte.User_Name0 [SamAccount],cte.Mail0 [Mail],cte.Full_User_Name0 [Full Name],
sys.Name0 [PC Name],sys.operatingSystem0 [OS] ,
case when sys.name0 in (select sys1.name0 from v_r_system sys1
inner join v_GS_OFFICE365PROPLUSCONFIGURATIONS OFI on ofi.ResourceID=sys1.ResourceID and Platform0 is not NULL
) then 'Yes' else 'No' end as 'ProPlus Installed',
case when cte.targetaddress0 like '%.mail.onmicrosoft.com' then 'Yes' else 'No' end as 'Mailbox on Cloud',
case when sys.name0 in (select sys1.name0 from v_r_system sys1
inner join v_Add_Remove_Programs ARP on ARP.ResourceID=sys1.ResourceID and ARP.DisplayName0='Microsoft Azure information protection'
) then 'Yes' else 'No' end as 'AIP Installed',
case when sys.name0 in (select sys1.name0 from v_r_system sys1
inner join v_Add_Remove_Programs ARP on ARP.ResourceID=sys1.ResourceID and (ARP.DisplayName0='Microsoft workplace join for windows' OR sys1.operatingSystem0 like 'Windows 10%' )
) then 'Yes' else 'No' end as 'WJ Installed'
from cte
inner join v_R_System sys on sys.User_Name0=cte.User_Name0
where sys.Resource_Domain_OR_Workgr0='eskonr'
and cte.Windows_NT_Domain0='eskonr'

 

Few things to note here are:

O365 license is based on AD sec groups hence if the user is member of AD sec group then we treat user as o365 licensed.

Collection ID is user collection ID .

Proplus installation is stored in v_GS_OFFICE365PROPLUSCONFIGURATIONS .So if this class is not enabled of for in client settings ,you must enable it. In Current branch ,this is enabled by default .

Workplace join is utility that does hybrid azure AD join and for windows 10 ,it is inbuilt but there is GPO to be enabled for this .

Domain eskonr is domain name that need to specify.

Change the code as your needs

Hope it helps!