Computers no in AD Group

[lance321]

I am having trouble creating a report that will show me computers that are not a member of a certain AD Group.

Any help would be greatly appreciated.
Thank you.

[lance321]

Just a follow-up. I have been testing this for a week and it is working great.
Thank you!

[lance321]

Thank you for your help. I will try this out. It will be very helpful.

[Susan_A · Accepted solution]

Moved this from the Report Center to Report Requests & Questions. Please note that the Report Center is for posting finished Lansweeper reports, not questions.

A sample report that lists the information you are after can be seen below. Replace what's highlighted, leaving the single quotes in the query. Instructions for running reports can be found here. If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
  
• Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder. The dictionary explains in great detail what each table and field stores.

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