Report of computers that do not have a specific user in the Administrator group

[Jeff]

On our system we have a 'Service Desk' group in Active Directory that should be in the local Administrator group on each workstation. I am trying to create a report of computers that do not have that group configured into the local Admin group.

The report below works to show computers that do have the Service Desk group added to it. If I change Like 'Service Desk%' to Not Like 'Service Desk%', the report is not what I want since it instead shows all the other accounts in the local Admin group instead of just the computers without 'Service Desk'. Is there another way to try to create the report? Thanks.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Description,
tblAssets.Domain As Computer_Domain,
tblADComputers.OU,
tblUsersInGroup.Groupname As [Group],
tblUsersInGroup.Domainname As User_Domain,
tblUsersInGroup.Username As User_Name,
tblComputersystem.Domainrole,
tblAssets.Lastseen
From tblAssets
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Full Outer Join tblComputersystem On tblAssets.AssetID =
tblComputersystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblADComputers.OU Like '%OU1%' And tblUsersInGroup.Groupname Like
'Admin%' And tblUsersInGroup.Username Like 'Service Desk%' And
tblComputersystem.Domainrole < 2 And tblUsersInGroup.Accounttype = 1
Order By tblAssets.AssetID,
User_Name,
[Group]

[Jeff]

Thanks, the report is working now and is just what we needed.

[Daniel_B · Accepted solution]

For this you need to directly modify the SQL code of your report. Since you want to list all assets which do not belong to the assets having the 'Service Desk' group an member in their local administrators group, you need to use a subquery in your report. Please find an example below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Description,
  tblAssets.Domain As Computer_Domain,
  tblADComputers.OU,
  tblComputersystem.Domainrole,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID Not In (Select tblUsersInGroup.AssetID
  From tblUsersInGroup
  Where tblUsersInGroup.Groupname Like 'Admin%' And
    tblUsersInGroup.Username Like 'Service Desk%') And
  tblADComputers.OU Like '%OU1%' And tblComputersystem.Domainrole < 2 And
  tblUsersInGroup.Accounttype = 1
Order By tblAssets.AssetName