cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
richf
Engaged Sweeper
I have tried to find and modify a report similar and have failed, I don't know anything about SQL reporting. I'm simply looking for a script to list all the computers found under an OU that I specify. Can anyone savvy enough help me out?
6 REPLIES 6
Andy_Sismey
Champion Sweeper III
Computer AD Membership based on - https://www.lansweeper.com/forum/yaf_postst11019_Computers-no-in-AD-Group.aspx#post41158 :

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 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 Like '%AD-Group-Name%') And
tblAssetCustom.State = 1 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName
Andy.S wrote:
Computer AD Membership based on - https://www.lansweeper.com/forum/yaf_postst11019_Computers-no-in-AD-Group.aspx#post41158 :

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 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 Like '%AD-Group-Name%') And
tblAssetCustom.State = 1 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName


Thanks for these. Still no results. I'm wondering if LanSweeper isn't seeing all my groups and searching for just the group doesn't work. I'll have to dig more into that. Thanks again!
moakyz03
Engaged Sweeper II
Rich F wrote:
Andy.S wrote:
Computer AD Membership based on - https://www.lansweeper.com/forum/yaf_postst11019_Computers-no-in-AD-Group.aspx#post41158 :

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 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 Like '%AD-Group-Name%') And
tblAssetCustom.State = 1 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName


Thanks for these. Still no results. I'm wondering if LanSweeper isn't seeing all my groups and searching for just the group doesn't work. I'll have to dig more into that. Thanks again!


Hi, you can try this and see if it does what you need:
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username,
tblADusers.Title,
tblAssets.Userdomain,
SubQuery.OU,
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 tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
SubString(SubString(tblADComputers.OU, CharIndex(',', tblADComputers.OU) -
250, 250), 4, 250) As OU
From tblAssets
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID) As
SubQuery On tblAssets.AssetID = SubQuery.AssetID
Where SubQuery.OU Not In ('OU/groups you wants aren't see1', 'OU/groups you wants aren't see2', 'OU/groups you wants aren't see', 'OU/groups you wants aren't see4', 'OU/groups you wants aren't see5') And tblAssetCustom.State = 1 And
tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
tblAssets.AssetName

I hope it helps what you expect.
Andy_Sismey
Champion Sweeper III
Ok , so you want to list all assets in an AD Group ,try this for User Membership :

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname,
tblADGroups.Name As [Group],
tblADusers.IsEnabled
From tblADMembership
Inner Join tblADusers On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name = 'AD-Group-Name'
Order By tblADusers.Userdomain,
tblADusers.Username
Andy_Sismey
Champion Sweeper III
HI This should be a good starting point :

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblADComputers.OU,
tblADComputers.IsEnabled
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tblADComputers On tblassets.AssetID =
tblADComputers.AssetID
Where tblADComputers.OU Like '%OU You Want To Query%' And tblassetcustom.State = 1
Andy.S wrote:
HI This should be a good starting point :

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblADComputers.OU,
tblADComputers.IsEnabled
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tblADComputers On tblassets.AssetID =
tblADComputers.AssetID
Where tblADComputers.OU Like '%OU You Want To Query%' And tblassetcustom.State = 1


Thanks for this, but it doesn't work for me. What I'm trying to do is I put in a security group for a printer and I want it to output a list of assetnames. Maybe I worded what I was looking for wrong in my first post.