AD Computers there are not in an Group

[Diego_Tomazzoni]

Hi!

In our environment, computers that are in groups that start with "SP" as "SP-W2K12-IIS" or "SP-W2K8-SQL" are computers that comply with security policies. So I need to see all the computers that are not in any group that starts with "SP", so that I can let them according to security policies.

I checked this code below that Susan was created, it shows all the computers that are in a particular group. I could not change the group to make it possible to show all the computers that are not in a group that start with "SP". Is it possible do it?

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblADComputers.AssetID
  From tblADComputers Left Join tblADMembership
      On tblADMembership.ChildAdObjectID = tblADComputers.ADObjectID Left Join
    tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
  Where tblADGroups.Name = 'your group name') And tblAssetCustom.State = 1 And
  tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

tks a lot!

[Susan_A · Accepted solution]

We received and answered this question via email. For everyone else's benefit, I'm pasting the reply we sent via email below.

Replacing this part of the query...

= 'your group name'

... with the following should do the trick:

Like 'SP%'

If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.