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.
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