Report listing computers found in specific OU group

[richf]

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?

[Andy_Sismey]

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

[richf]

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]

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]

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]

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

[richf]

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.