→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
TylerO
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
TylerO
Engaged Sweeper II
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

View solution in original post

5 REPLIES 5
RCorbeil
Honored Sweeper II
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
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
Honored Sweeper
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
Engaged Sweeper II
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
Engaged Sweeper III

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