Domain Users il Local Admin group

[EStarshinov]

Hi all! I have request for "All local Admins":

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblUsers.Name,
tsysOS.Image As icon
From tblAssets
Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblUsers.SID Like '%-500' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

How to make request for "All domain users in Local Admin group"?

[Daniel_B · Accepted solution]

This group membership is stored in tblUsersInGroup. The following report lists all AD user accounts which are member of an admin group of assets.

Select Top 1000000 tblADusers.Username,
  tblADusers.Userdomain,
  tblAssets.AssetID,
  tblAssets.AssetName
From tblADusers
  Inner Join tblUsersInGroup On tblADusers.Username = tblUsersInGroup.Username
    And tblADusers.Userdomain = tblUsersInGroup.Domainname
  Inner Join tblAssets On tblAssets.AssetID = tblUsersInGroup.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1 And tblUsersInGroup.Admingroup = 1
Order By tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.AssetName