cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Casawi79
Engaged Sweeper
Hello,
I am trying to generate a report that provides the local admins under Users and Groups and whether they are enabled or disabled. I was able to find the following report, however it only generates the admins under Users and not the AD account under Groups. See below. Thanks.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblUsers.Name,
tblUsers.Fullname,
tblUsers.Accounttype,
tblUsers.Disabled,
tblUsers.Lockout,
tblUsers.PasswordChangeable,
tblUsers.PasswordExpires,
tblUsers.PasswordRequired,
tblUsers.SID,
tblUsers.Status,
tblUsers.BuildInAdmin,
tblUsers.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblUsers.Name
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
I deleted one of your other posts as it appeared to be a duplicate of this post. I included a basic report below that lists all (local and domain) accounts that are members of your computers' administrator groups. You can tick any additional fields you may need in the tblUsers table at the top of the report builder.

Keep in mind however that the tblUsers table only stores information on local Windows accounts. We don't store the status (enabled/disabled) or password settings of domain accounts for instance. This feature is on our customer wish list, but we do not have a release date for it at this time.
Select Top 1000000 tblAssets.AssetUnique,
tblUsersInGroup.AssetID,
tblAssets.Domain,
tblAssets.Description,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tblUsersInGroup.Lastchanged,
tsysOS.Image As icon,
tblUsers.Disabled
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
Left Join tblUsers On tblUsers.AssetID = tblUsersInGroup.AssetID And
tblUsers.Name = tblUsersInGroup.Username
Where Not Exists(Select Distinct * From (Select tblAssets.AssetName As Domain,
tblUsers.Name As Username
From tblAssets
Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Where tblUsers.BuildInAdmin = 1 And tblUsersInGroup.Domainname Like
tblAssets.AssetName And tblUsersInGroup.Username Like tblUsers.Name
Union
Select tsysadmins.Domain,
tsysadmins.AdminName As username
From tsysadmins
Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
tblUsersInGroup.Username Like tsysadmins.AdminName) DERIVEDTBL) And
tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

View solution in original post

2 REPLIES 2
vqT4cDoP9iXyMZw
Champion Sweeper
Is there a revised version of this report for v7?

I'm getting erratic results trying to view the presence of the local Administrator account, and whether or not it is presently disabled.
Susan_A
Lansweeper Alumni
I deleted one of your other posts as it appeared to be a duplicate of this post. I included a basic report below that lists all (local and domain) accounts that are members of your computers' administrator groups. You can tick any additional fields you may need in the tblUsers table at the top of the report builder.

Keep in mind however that the tblUsers table only stores information on local Windows accounts. We don't store the status (enabled/disabled) or password settings of domain accounts for instance. This feature is on our customer wish list, but we do not have a release date for it at this time.
Select Top 1000000 tblAssets.AssetUnique,
tblUsersInGroup.AssetID,
tblAssets.Domain,
tblAssets.Description,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tblUsersInGroup.Lastchanged,
tsysOS.Image As icon,
tblUsers.Disabled
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
Left Join tblUsers On tblUsers.AssetID = tblUsersInGroup.AssetID And
tblUsers.Name = tblUsersInGroup.Username
Where Not Exists(Select Distinct * From (Select tblAssets.AssetName As Domain,
tblUsers.Name As Username
From tblAssets
Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
Where tblUsers.BuildInAdmin = 1 And tblUsersInGroup.Domainname Like
tblAssets.AssetName And tblUsersInGroup.Username Like tblUsers.Name
Union
Select tsysadmins.Domain,
tsysadmins.AdminName As username
From tsysadmins
Where tblUsersInGroup.Domainname Like tsysadmins.Domain And
tblUsersInGroup.Username Like tsysadmins.AdminName) DERIVEDTBL) And
tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName