
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-14-2015 03:48 PM
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.
Any help would be greatly appreciated.
Thank you.
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-15-2015 02:01 PM
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:
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
3 REPLIES 3

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-25-2015 06:59 PM
Just a follow-up. I have been testing this for a week and it is working great.
Thank you!
Thank you!

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-15-2015 03:03 PM
Thank you for your help. I will try this out. It will be very helpful.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-15-2015 02:01 PM
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:
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
