Dear gurus,
I need some help with subqueries of AD group membership, and I haven't found a suitable example yet, so I hope one of you gurus can help me with this.
We use AD groups with names like "GRP_ROLE_XXX" and "GRP_DPT_XXX" to manage our users, to allow users to be placed in roles and departments, and then AD permissions and software installations to be automatically assigned to these groups.
When our computers are deployed but not immediately assigned to a user, they are Managed By a dummy user with ID 51521. They are also assigned to a dummy Role and Department.
So what I need is a subquery (or 2), to look up the matching "GRP_ROLE_XXX" and "GRP_DPT_XXX" for the Username of the last user of that computer, and display these in extra columns on the report. If for some reason there is no assignment, there should be a blank entry in the column.
Sample Output
| AssetName | Domain | UserName | Firstname | Lastname | Role | Department |
| Comp-123 | abc.com | User1234 | ABC | DEF | GRP_ROLE_GHI | GRP_DPT_JKL |
| Com-456 | abc.com | User5678 | MNO | PQR | | GRP_DPT_VWX |
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.Domain,
tblassets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.EmployeeID,
tblassetcustom.Model,
tblassets.Version As [OS Build],
tblassets.Memory,
tblassets.IPAddress,
tsysIPLocations.IPLocation,
tblassets.Firstseen,
tblassets.Lastseen,
tblADComputers.ManagerADObjectId
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tblADComputers On tblassets.AssetID = tblADComputers.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblassets.LocationID
Left Join tblADusers On tblADusers.Username = tblassets.Username And
tblADusers.Userdomain = tblassets.Userdomain
Where tblADComputers.ManagerADObjectId = '51521' And tblassetcustom.State = 1
Order By tsysIPLocations.IPLocation,
tblassets.AssetName
