cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
EStarshinov
Engaged Sweeper II
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"?
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
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