Filtering software report by OU

[TylerO]

I have this report that finds assets that don't have a particular piece of software. How can I add a filter to only return computers in a specific OU? In this case 'OU=Computers'. Here is the existing script, my SQL knowledge is lacking and I keep running in to errors trying to add a Where clause.

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,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Skype for Business Basic 2016%') And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

[RCorbeil]

Looking at my database, I see that there are no spaces in tblADComputers.OU, so if you want to make an exact match you would need to use

WHERE
  tblADComputer.OU = 'OU=Computers,OU=SC,OU=SG,DC=SERVERDOMAIN,DC=local'

Alternatively, you could do it as a series of "contains" string matches. Going for an exact match is faster, but a bunch of "contains" matches may be easier for you to read.

WHERE
  (    tblADComputers.OU LIKE '%OU=Computers%'
   AND tblADComputers.OU LIKE '%OU=SC%'
   AND tblADComputers.OU LIKE '%OU=SG%'
   AND tblADComputers.OU LIKE '%DC=SERVERDOMAIN%'
   AND tblADComputers.OU LIKE '%DC=local%'
  )

T-SQL: LIKE
W3Schools: SQL wildcard characters

[CyberCitizen]

RC62N wrote:
Looking at my database, I see that there are no spaces in tblADComputers.OU, so if you want to make an exact match you would need to use

Thanks RC62N, that looks exactly like what I needed I will check this tomorrow and confirm about to head out for the night.

[CyberCitizen]

Hi Guy's,

I am having a similar issue with filtering on the OU.

I am trying to create 3x separate reports for each company filted on the OU, however I can't get it to accept the filters.

OU Structure.

OU=Computers, OU=HP, OU=SG, DC=SERVERDOMAIN, DC=local
OU=Computers, OU=SC, OU=SG, DC=SERVERDOMAIN, DC=local
OU=Computers, OU=SG, OU=SG, DC=SERVERDOMAIN, DC=local

I am needing to be able to filter on the following OU=Computers, OU=SC, OU=SG, DC=SERVERDOMAIN, DC=local to return only assets under that OU.

[TylerO · Accepted solution]

I needed to make a small change. There was a missing closing bracket, and the way the OU column works requires a Like operator, since it displays the FQDN, not just a single OU. Thanks!

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,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Skype for Business Basic 2016%') And
tblADComputers.OU Like '%Computers%'
Order By tblAssets.Domain,
tblAssets.AssetName

[ProfileNL]

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,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Skype for Business Basic 2016%' And
tblADComputers.OU = 'Computers'
Order By tblAssets.Domain,
tblAssets.AssetName