→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
lance321
Engaged Sweeper II
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.
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

3 REPLIES 3
lance321
Engaged Sweeper II
Just a follow-up. I have been testing this for a week and it is working great.
Thank you!
lance321
Engaged Sweeper II
Thank you for your help. I will try this out. It will be very helpful.
Susan_A
Lansweeper Alumni
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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now