→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
WhatTheFrenchTo
Engaged Sweeper III
Good Afternoon All,

I'm looking for a report to query all PCs on a specific domain and make sure a custom username has full local admin rights on each PC. Any help would be greatly appreciated. I have this thus far:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroup.Username,
tblUsersInGroup.Domainname,
tblUsersInGroup.Groupname
From tblAssets
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Where tblAssets.AssetID Not In (Select tblUsers.AssetID From tblUsers
Where tblUsers.Name = 'saveme') And tblUsersInGroup.Groupname =
'administrators'
Order By tblAssets.AssetName

However, I have a strong feeling it's not exactly what I need. saveme is the name of the custom local admin account I'm looking for I need to make sure it exists on all PCs.
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
The tblUsersInGroup filter isn't part of your sub query, but your main query. Your report is therefore listing machines without the specified user, but it doesn't verify whether the user is part of the administrators group. It has lines for all admins on the machine as well. The report below should be more accurate. You'll need to insert your own NetBIOS domain name into the highlighted part of the query.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select Top 1000000 tblUsersInGroup.AssetID
From tblUsersInGroup Inner Join tblAssets On tblAssets.AssetID =
tblUsersInGroup.AssetID
Where tblUsersInGroup.Domainname = tblAssets.AssetName And
tblUsersInGroup.Username = 'saveme' And tblUsersInGroup.Groupname =
'administrators') And tblAssets.Domain = 'lansweeper'
Order By tblAssets.AssetName

View solution in original post

2 REPLIES 2
WhatTheFrenchTo
Engaged Sweeper III
Thank you much Susan!
Susan_A
Lansweeper Alumni
The tblUsersInGroup filter isn't part of your sub query, but your main query. Your report is therefore listing machines without the specified user, but it doesn't verify whether the user is part of the administrators group. It has lines for all admins on the machine as well. The report below should be more accurate. You'll need to insert your own NetBIOS domain name into the highlighted part of the query.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select Top 1000000 tblUsersInGroup.AssetID
From tblUsersInGroup Inner Join tblAssets On tblAssets.AssetID =
tblUsersInGroup.AssetID
Where tblUsersInGroup.Domainname = tblAssets.AssetName And
tblUsersInGroup.Username = 'saveme' And tblUsersInGroup.Groupname =
'administrators') And tblAssets.Domain = 'lansweeper'
Order By tblAssets.AssetName