We have a few reports that we run to check who is a member of the local administrators group on a number of servers. Report is fairly straight forward and is as per below:
Select Distinct Top 1000000 tblAssets.AssetUnique, tblUsersInGroup.AssetID, tblAssets.Domain, tblAssets.Description, tblUsersInGroup.Domainname, tblUsersInGroup.Username, tblUsersInGroup.Lastchanged, tsysOS.Image As icon, tblAssets.IPAddress, tblAssets.Lastseen, tsysOS.OSname From tblUsersInGroup Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode Where tblAssets.AssetName Like 'someserver%' And tblUsersInGroup.Groupname = 'administrators' Order By tblAssets.AssetUnique, tblUsersInGroup.Domainname, tblUsersInGroup.Username
This has served us well, however I need to expand it.
Most of my servers have an AD group that is part of the local administrators group. My report only shows the group name, but what I was hoping to do was somehow expand the report so it would show the members of the AD group.
I'm guessing it needs to make two queries, one against the server to find the AD group, then another against AD to find the members. I then have a new column that shows the group name and another with the members.