11-02-2023 08:58 PM
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
11-03-2023 03:27 AM - edited 11-03-2023 03:38 AM
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'
11-03-2023 03:19 AM - edited 11-03-2023 03:38 AM
I'll try to create more simple query
11-03-2023 03:17 AM - edited 11-03-2023 03:35 AM
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'
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now