Missing Local Account on Computer

[josh_lamberth]

I am trying to run a report in Lansweeper to list computers that is missing an account called "testaccount" in the users group. Below if I run the search criteria it will pull up the computers that does have the account created listed once. If I set "tblUsersInGroup.Username NOT LIKE 'testaccount'", it will list each computer multiply times for every user account that is on the computer that is not named 'testaccount'. Is there a way to run a report that will only list a computer once in the report if it is missing the account?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroup.Username,
tblUsersInGroup.Domainname,
tblUsersInGroup.Groupname
From tblAssets
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Where tblUsersInGroup.Username = 'testaccount' And tblUsersInGroup.Groupname =
'users'
Order By tblAssets.AssetName

[Esben_D]

Basically, you'll have to use a subquery in the where clause. This subquery will contain the assetID of all asset where the accout testuser is found. With the where clause, you set the condition that the report must show all results except when the asset ID is identical to an assetID where the account testuser was identified.

So basically, you can create a report that shows all the information about the asset you want, and put this in the where clause:

Where tblAssets.AssetID NOT IN (Select tblAssets.AssetID
From tblAssets
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Where tblUsersInGroup.Username = 'testaccount' And tblUsersInGroup.Groupname =
'users')