Multiple OU's for report query?

[JohnLewis]

How do you add two or three specific OU's for the reporting options?
We have a couple OU's in our AD environment that are similar in name.

Example we want to list '0U=BuildingA,OU=site1,DC=example' with: 'OU=BuildingB,OU=site2,DC=eample'

we cannot use the tblADComputers.OU Like '%Building%' as other OU's in the structure have this wording that we do not want to include in the report. ex: 'OU=BuildingC,OU=site1,DC=example


Current report script as listed:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom1.Model,
tblAssetCustom1.Serialnumber,
tsysOS.OSname,
tblAssetCustom1.Manufacturer,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom1.Warrantydate,
tblAssetCustom1.PurchaseDate,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID And
tblADComputers.OU Like '%Building%'
Inner Join tblAssetCustom tblAssetCustom1 On tblAssets.AssetID =
tblAssetCustom1.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1

[JohnLewis]

Thank you Thank you Thank you, Jeremy.D! You're are correct in that I could not get the syntax correct!
You get a solid YOU ROCK !!! 🙂

Pasted final script below in case any one else runs across this thread. Did not seem to need the "where" piece by the tblComputers.OU IN . Also added ip location to the report...

---------------------------

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom1.Model,
tblAssetCustom1.Serialnumber,
tsysOS.OSname,
tsysIPLocations.IPLocation,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom1.Warrantydate,
tblAssetCustom1.PurchaseDate,
tblAssetCustom1.Manufacturer,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID And
tblADComputers.OU In
('OU=BuildingA,OU=site1,DC=example',
'OU=BuildingB,OU=site2,DC=example')
Inner Join tblAssetCustom tblAssetCustom1 On tblAssets.AssetID =
tblAssetCustom1.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.State = 1

-----------------------------------------

[Jeremy_D · Accepted solution]

You can use an IN statement in the Criteria column to look for a match in a list of values. This is standard SQL syntax, not specific to Lansweeper. We have included a modified query below. Insert the OU names that you do want to list in the query, make sure to get rid of all the spaces after the commas in your ADSI path if there are any.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tsysOS.OSname,
  tblAssetCustom.Manufacturer,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetCustom.Warrantydate,
  tblAssetCustom.PurchaseDate,
  tblADComputers.OU
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblADComputers.OU In ('OU=BuildingA,OU=site1,DC=example',
  'OU=BuildingB,OU=site2,DC=example') And tblAssetCustom.State = 1