User rights or status

[dr_sah]

Hi, I need report to see what is the domain user status on PC, are they user or administrator?

thank you for great software

[dr_sah]

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroup.Username,
tblUsersInGroup.Domainname,
tblUsersInGroup.Groupname
From tblAssets
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Where tblUsersInGroup.Groupname = 'administrators'
Order By tblAssets.AssetName








this is all what i need.
thanks

[dr_sah]

or, at least if I can get out all users in PC with groups. I can sort later in excel.

[dr_sah]

Don't get any result.

(This report has no results!)

Most important info is: is user member of administrator group on his local PC. Have xxx PC, and need to check if I missed someone.

[Daniel_B · Accepted solution]

The following report lists domain users and the computer names on which the accounts themselves or the AD group they are direct member of is member in a local administrator group.

Select Distinct Top 1000000 tblADusers.Username,
  tblADusers.Userdomain,
  tblAssets.AssetID,
  tblAssets.AssetName
From tblADusers
  Left Join (Select tblUsersInGroup.Username,
    tblUsersInGroup.Domainname,
    tblUsersInGroup.AssetID
  From tblUsersInGroup
  Where tblUsersInGroup.Admingroup = 1) tUsersInGroupAdmin
    On tblADusers.Username = tUsersInGroupAdmin.Username And
    tblADusers.Userdomain = tUsersInGroupAdmin.Domainname
  Left Join (Select tblUsersInGroup.AssetID,
    tblADMembership.ChildAdObjectID
  From tblADMembership
    Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
      tblADGroups.ADObjectID
    Inner Join tblUsersInGroup On tblUsersInGroup.Username = tblADGroups.Name
  Where tblUsersInGroup.Admingroup = 1) tADgroupAdmin
    On tADgroupAdmin.ChildAdObjectID = tblADusers.ADObjectID
  Left Join tblAssets On tblAssets.AssetID = tUsersInGroupAdmin.AssetID Or
    tblAssets.AssetID = tADgroupAdmin.AssetID
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Order By tblADusers.Username,
  tblADusers.Userdomain,
  tblAssets.AssetName,
  tblAssets.AssetID