SCCM Collection: Dynamically Identifying Users that have had their mailbox migrated to O365

One of the challenges I’ve recently had is identifying users that have had their mailbox migrated to Office365. This becomes quite a task if like me you’re using InTune integrated with SCCM but need separate mail profiles for mobile users. Now this isn’t an issue if you’re happy to manually maintain an AD group which can then be referenced by SCCM, however I want this to dynamically be updated and not rely on Humans.

John Bailey, has written an excellent article on some key AD attributes which can be used to identify whether the mailbox is on-prem or in O365. For my environment I can simply extend my Active Directory User Discovery to include the attribute ‘msExchRecipientDisplayType’ and then use a WQL query to identify the users that have been migrated.

Common values:

Value

Object Type

1

User Mailbox

2

Linked Mailbox

4

Shared Mailbox

2048

Dynamic Distribution Group

2147483648

Remote Mailbox (O365)

The query for where the mailbox has been migrated to Office365:

SELECT *
FROM
SMS_R_User
WHERE
SMS_R_User.msExchRecipientTypeDetails = 2147483648
Advertisements

Most used WQL Queries

Here are some of my most used WQL Queries for collection creation.

User is in AD group

SELECT
SMS_R_USER.ResourceID,
SMS_R_USER.ResourceType,
SMS_R_USER.Name,
SMS_R_USER.UniqueUserName,
SMS_R_USER.WindowsNTDomain
FROM
SMS_R_User
WHERE
SMS_R_User.UserGroupName = "DOMAIN\\GROUP"

Device is in AD Group

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client 
FROM 
SMS_R_System 
WHERE
SMS_R_System.SystemGroupName = "DOMAIN\\GROUP"

Device is in OU

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client 
FROM 
SMS_R_System 
WHERE 
SMS_R_System.SystemOUName = "DOMAIN.LOCAL/OU/OU"

Devices where the Primary User is in a specific AD Group

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client 
FROM
SMS_R_System 
JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.ResourceName 
JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName 
WHERE 
SMS_UserMachineRelationship.Types=1 AND 
SMS_R_User.UserGroupName="DOMAIN\\USER GROUP"

Specific Application is installed

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client 
FROM 
SMS_R_System inner
JOIN SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId 
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId 
WHERE 
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Name from Add/Remove Programs" OR 
SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Name from Add/Remove Programs"

Specific Application is installed but has not been used in >= 90 days

I always limit this collection with a Specific Application is installed collection.

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client
FROM
SMS_R_System
INNER JOIN SMS_G_System_CCM_RECENTLY_USED_APPS on SMS_G_System_CCM_RECENTLY_USED_APPS.ResourceID = SMS_R_System.ResourceId
WHERE
SMS_G_System_CCM_RECENTLY_USED_APPS.ExplorerFileName = "SameEXEasMetering.EXE" AND
DATEDIFF(day, LastUsedTime, GETDATE()) > 90 order by SMS_R_System.Name

By Architecture

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client
FROM
SMS_R_System
INNER JOIN SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID
WHERE SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC/X64-based PC"

All Workstations

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client 
FROM
SMS_R_System 
WHERE
SMS_R_System.operatingSystem NOT LIKE "%Server%"

All Servers

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client
FROM
SMS_R_System
WHERE
SMS_R_System.operatingSystem LIKE"%Server%"

Clients with specific CCM version

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client
FROM
SMS_R_System
WHERE
SMS_R_System.ClientVersion LIKE "5.00.8325%"