Find Where Local Account Doesn't Exist

[WhatTheFrenchTo]

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.

[Daniel_B · Accepted solution]

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]

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.