Finding all servers without a certain local user account.

[richardmcgovern]

Hi,

As the title says really - i'm trying to create a report (with very limited SQL knowledge) where it will return to me all Windows Servers that DO NOT have a certain local admin account present. Here's the code I have so far:

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 = 'LOCALADMIN') And tsysOS.OSname = 'Win 2019') Or
  (tsysOS.OSname = 'Win 2016') Or
  (tsysOS.OSname = 'Win 2012 R2') Or
  (tsysOS.OSname = 'Win 2012') Or
  (tsysOS.OSname = 'Win 2008 R2') Or
  (tsysOS.OSname = 'Win 2008' And tblAssetCustom.State = 1)
Order By tblAssets.AssetName

This report seems to run without returning any syntax errors but the results it is returning aren't accurate. It will return a server for example that claims does not have the LOCALADMIN account but when I go into 'Config > User Info > Users' the account is there?

As I mentioned before my SQL is very limited so if somebody could point me in the right direction I'd appreciate it!

Thanks,

[richardmcgovern]

Thats great, thanks both for the help/advice!

[RCorbeil]

Given the code presented, it would be worth taking a step back and making this a teachable moment: don't just offer a correction, but explain why the original didn't work as expected.

Logical operators have an order of precedence like algebraic operators: exponents, parentheses, multiplication/division, addition/subtraction. x = 2 + 3 x 4 produces a different result than x = (2 + 3) x 4. In this case, it's parentheses, NOT, AND, OR.

If you take a look at your WHERE clause, you'll see that it's a bit of a mess and is at the root of your bad results.

Where
  (tblAssets.AssetID Not In (Select tblUsers.AssetID From tblUsers
                             Where tblUsers.Name = 'LOCALADMIN')
    And tsysOS.OSname = 'Win 2019')
  Or (tsysOS.OSname = 'Win 2016')
  Or (tsysOS.OSname = 'Win 2012 R2')
  Or (tsysOS.OSname = 'Win 2012')
  Or (tsysOS.OSname = 'Win 2008 R2')
  Or (tsysOS.OSname = 'Win 2008' And tblAssetCustom.State = 1)

IF the OS = "Win 2019" and there's no localadmin account, include it (don't care whether the asset is active)
OR IF the OS = "Win 2016", include it (don't care about a localadmin account or whether the asset is active)
OR IF the OS = "Win 2012 R2", include it (don't care about a localadmin account or whether the asset is active)
OR IF the OS = "Win 2012", include it (don't care about a localadmin account or whether the asset is active)
OR IF the OS = "Win 2008 R2", include it (don't care about a localadmin account or whether the asset is active)
OR IF the OS = "Win 2008" AND the asset is active, include it (don't care about a localadmin account)

What I expect you actually meant was

Where
  tblAssetCustom.State = 1
  AND tblAssets.AssetID Not In (Select tblUsers.AssetID From tblUsers
                                Where tblUsers.Name = 'LOCALADMIN')
  AND (   tsysOS.OSname = 'Win 2019'
       OR tsysOS.OSname = 'Win 2016'
       OR tsysOS.OSname = 'Win 2012 R2'
       OR tsysOS.OSname = 'Win 2012'
       OR tsysOS.OSname = 'Win 2008 R2'
       OR tsysOS.OSname = 'Win 2008'
      )

Because you're working with a fixed list of OSname values, you could also use

Where
  tblAssetCustom.State = 1
  AND tblAssets.AssetID Not In (Select tblUsers.AssetID From tblUsers
                                Where tblUsers.Name = 'LOCALADMIN')
  AND tsysOS.OSname IN ('Win 2019', 'Win 2016', 'Win 2012 R2', 'Win 2012', 'Win 2008 R2', 'Win 2008')

IF the asset is active
AND there's no localadmin account
AND (the asset is running one of these OS versions)

Andy.S's proposal refines things with one assumption. If, for example, you have any machines running Win 2003 or 2003 R2, they would show up in the results. It's easy enough to add another condition to filter those out.

[Andy_Sismey]

Hi , Give this a go :

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 = 'LOCALADMIN' And tblAssetCustom.State = 1)
  And tsysOS.OSname Like '%20%'
Order By tblAssets.AssetName