Close Menu
    Facebook X (Twitter) Instagram
    Monday, July 14
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»Using SCCM how to check if the user mailbox is migrated to exchange online (cloud) from on-prem exchange

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

    Eswar KonetiBy Eswar KonetiMarch 16, 7:40 pm5 Mins Read CM2012 3,348 Views
    Share
    Facebook Twitter LinkedIn Reddit

     

    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.

    advanced-features

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

    image

    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

    image

    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.

    SNAGHTML1ac8d93b

    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:

    SNAGHTML1ace9c0c

     

    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!

    configmgr exchange online report office proplus deployment psadt report for mailbox migration SCCM targetAddress user SCCM to check mailbox on cloud or not
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    SCCM SQL Report – Compare Installed Apps on TWO Different Computers

    July 13, 10:35 am

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    10 Comments

    1. Tiaan Naude on May 14, 2018 11:48 PM

      Hi Eswar, Did you post the report somewhere for us to look at?

      Reply
      • Eswar Koneti on June 2, 2018 12:22 AM

        No not yet but i will remove the company customization that i am working for and post the SQL code very soon to this post.

        Regards,
        Eswar

        Reply
    2. Tiaan Naude on May 14, 2018 11:44 PM

      Hi Eswar, Did you post the query somewhere? We also user AD groups for our licensing and would like to use a report like this to track the status.

      Reply
      • Eswar Koneti on June 2, 2018 12:23 AM

        will post it very soon in next couple of days.

        Regards,
        Eswar

        Reply
    3. arif on March 19, 2018 10:52 PM

      hi Eswar Koneti, as always>great article...
      Would you please give little more detail on reporting especially USER LICENSED and MAILBOX ON CLOUD? or share your sql reporting

      thanks again on this article.

      Reply
      • Eswar Koneti on March 19, 2018 11:02 PM

        Hi Arif,
        Sure ,i can try to explain more about how the report is made of and what basis i would consider user is licensed and mailbox is on cloud. I will try to update the blogpost in this week mostly.
        Yes ,i do have SQL code but it has lot of customizations based on AD sec group for licensing if user part of the license group then consider user as o365 license else No which may not be the case for everyone so i did not post the sql code but i try to explain so you can create based on your needs.

        Regards,
        Eswar

        Reply
    4. Sweta on March 17, 2018 2:51 AM

      Hi Eswar , Do you have SQL query also ? I am looking for SQL query. Are you willing to share ?

      Reply
      • Eswar Koneti on March 19, 2018 11:00 PM

        Yes ,i do have but it has lot of customizations based on AD sec group for licensing if user part of the license group then consider user as o365 license else No which may not be the case for everyone so i did not post the sql code.

        Regards,
        Eswar

        Reply
        • Sweta on March 20, 2018 12:43 AM

          Thanks for reply, It would be great if you are able to share with me. I already changed and configured all required AD settings. General query is already structured but stuck in mail box summary. If possible please send me query on my mail.

          Thanks in advance.

          Reply
    5. Rajesh GK on March 17, 2018 2:09 AM

      Awesome report Eswar...Hope it will definitely help for many SCCM admins to help in migration of on-prem exchange server to cloud...

      Reply

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.