Report to find PCs without a localuser account

[allenh318ebc]

I am trying to get a report that pulls PCs without a localuser account. I can get this one to find devices that have the account, but using 'Not Like' just lists all the other accounts on the devices.



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,
tblAssetCustom.Model
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 tblUsers.Name = 'localuser' And tblAssetCustom.Model Like 'Think%' And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblUsers.Name

[allenh318ebc]

We are trying to find out what devices do not have a localuser account, so we can get that installed on them. If I have a report that shows devices that do not have that account, I can run a deployment against that report. The report will pull multiple lines of the same device with each local account. It is basically pullihg every local account that is not a localuser. If LaptopA does not have a localuser account, it still shows on this report with every other local account. I need it to tell me each device that does not have that local account, but only once for each device.

[RCorbeil]

If you're looking to filter the list for multiple user accounts, you'll need to include them all in the exception list.

Select Top 1000000
  tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.Model
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
  tblAssetCustom.Model Like 'Think%'
  And tblAssetCustom.State = 1
  AND tblAssets.AssetID NOT IN (SELECT
                                  tblUsers.AssetID
                                FROM
                                  tblUsers
                                WHERE
                                  tblUsers.Name IN ('localuser1', 'localuser2', 'localuser3') )
Order By
  tblAssets.Domain,
  tblAssets.AssetName

If that's not what you're after, can you try to provide a little more detail, because as the query sits I don't think it should be producing duplicates.

[allenh318ebc]

I did that, and it runs, but will still list the same device 4-5 times with all the other local accounts. I would need to have eech device listed only one time.

[RCorbeil]

It looks like I copy/pasted a bit I'd deleted elsewhere. Drop the reference to tblUsers.Name from the Order By. Sorry about that.

[allenh318ebc]

When I try to save, I am getting the error 'The multi-part identifier "tblUsers.Name" could not be bound'

[RCorbeil]

OK, computer, first, pull me a list of assets where "localuser" is found. Got that? Good. Now pull me a list of assets that aren't in that first list.

Select Top 1000000
  tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.Model
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
  tblAssetCustom.Model Like 'Think%'
  And tblAssetCustom.State = 1
  AND tblAssets.AssetID NOT IN (SELECT
                                  tblUsers.AssetID
                                FROM
                                  tblUsers
                                WHERE
                                  tblUsers.Name = 'localuser')
Order By
  tblAssets.Domain,
  tblAssets.AssetName,
  tblUsers.Name