cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
WhatTheFrenchTo
Engaged Sweeper III
So long story short I'd like to audit our network and find PCs/Servers that don't have a specific local account name. Does anyone have a query crafted to perform this task? Looking for a specific account name that hopefully exists on all domain joined PCs. Thanks in advance for the assistance.
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
You can do that with a subquery. Please find an example below. The subquery lists computers having this account and you are listing computers in the main report which don't get listed by the subquery. Not sure if added the filter tblUsers.SID Like '%-500' by intention? This would filter on built-in administrator accounts only.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tsysOS.Image As icon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID Not In (Select tblUsers.AssetID From tblUsers
Where tblUsers.Name = 'saveme') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

View solution in original post

2 REPLIES 2
Daniel_B
Lansweeper Alumni
You can do that with a subquery. Please find an example below. The subquery lists computers having this account and you are listing computers in the main report which don't get listed by the subquery. Not sure if added the filter tblUsers.SID Like '%-500' by intention? This would filter on built-in administrator accounts only.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tsysOS.Image As icon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID Not In (Select tblUsers.AssetID From tblUsers
Where tblUsers.Name = 'saveme') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName
WhatTheFrenchTo
Engaged Sweeper III
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.Name = 'saveme' And tblUsers.SID Like '%-500' And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

I've used this report to generate a list... I came up with 2 machines total... However this isn't the best way to run this report. I need to find a way to query the DB for any machines that don't have the specified account name as local admin on the workstations.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now