Tracking Users added to AD Group

[malbanese1]

I am in the process of migrating users to Office 365 from an on premise Exchange system. As I migrate users, I add them to an AD group named "O365 Users". I would like to have a report that lists all my users and shows a True / False value if they are in the group. This way I can track who has been moved. 

I have been trying to write this but I have been unsuccessful. Thanks in advance. 

Michael 

[Mister_Nobody]

More  simple query but it seems more correct:

Select Top 1000000 tblADObjects.domain,
  tblADObjects.sAMAccountName,
  Case
    When tblADusers.ADObjectID Is Not Null Then 'User'
    When tblADGroups.ADObjectId Is Not Null Then 'Group'
    When tblADComputers.ADObjectID Is Not Null Then 'Computer'
    Else 'Trash'
  End type,
  (Select tblADObjects.domain From tblADObjects
    Where tblADObjects.ADObjectID = tblADGroups1.ADObjectID) As Group_Domain,
  tblADGroups1.Name,
  tblADGroups1.Description,
  Case tblADGroups1.GroupType
    When -2147483646 Then 'Security - Global'
    When -2147483644 Then 'Security - Local'
    When -2147483643 Then 'Built-in'
    When -2147483640 Then 'Security - Universal'
    When 2 Then 'Distribution - Global'
    When 4 Then 'Distribution - Local'
    When 8 Then 'Distribution - Universal'
  End As ADGroupType
From tblADObjects
  Inner Join tblADMembership On tblADObjects.ADObjectID =
      tblADMembership.ChildAdObjectID
  Inner Join tblADGroups tblADGroups1 On tblADMembership.ParentAdObjectID =
      tblADGroups1.ADObjectID
  Left Join tblADusers On tblADObjects.ADObjectID = tblADusers.ADObjectID
  Left Join tblADGroups On tblADObjects.ADObjectID = tblADGroups.ADObjectId
  Left Join tblADComputers On tblADObjects.ADObjectID =
      tblADComputers.ADObjectID
Where tblADGroups1.Name Like 'O365 Users'

[Mister_Nobody]

I'll try to create more simple query

[Mister_Nobody]

We use such report:

Select Top 1000000 tblADObjects.sAMAccountName,
  tblADusers.email,
  tblAssets.AssetName,
  tblOperatingsystem.Caption,
  tblAssets.Processor,
  tblADusers.Info,
  tblADusers.Division,
  ex.group_name
From (Select Distinct em.ChildAdObjectID,
      Stuff((Select ', ' + gg.group_name As [text()] From (Select io.ADObjectID,
              Coalesce(ig.Name, io.sAMAccountName, 'unknown') group_name
            From tblADGroups ig
              Inner Join tblADObjects io On io.ADObjectID = ig.ADObjectID
            Where Coalesce(ig.Name, io.sAMAccountName, 'unknown') Like 'O365 Users'
              And io.domain = 'your_domain') gg Inner Join tblADMembership m On
              gg.ADObjectID = m.ParentAdObjectID
        Where m.ChildAdObjectID = em.ChildAdObjectID Order By gg.group_name,
          m.ChildAdObjectID For Xml Path('')), 1, 1, '') group_name
    From tblADMembership em) ex
  Inner Join tblADObjects On tblADObjects.ADObjectID = ex.ChildAdObjectID And
      ex.group_name <> ''
  Inner Join tblADusers On tblADObjects.ADObjectID = tblADusers.ADObjectID
  left Join tblAssets On tblADusers.Username = tblAssets.Username
left Join tblOperatingsystem On
      tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblADusers.Userdomain = 'your_domain'