Notification

Icon
Error

Filtering software report by OU

Posted: Wednesday, January 11, 2017 10:41:11 PM(UTC)
TylerO

TylerO

Member Original PosterPosts: 4
0
Like
This issue has been solved! Click here to view the solution
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
ProfileNL
#1ProfileNL Member Posts: 22  
posted: 1/12/2017 9:47:01 AM(UTC)
Code:

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
TylerO
#2TylerO Member Original PosterPosts: 4  
posted: 1/12/2017 2:20:48 PM(UTC)
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
CyberCitizen
#3CyberCitizen Member Posts: 377  
posted: 7/24/2020 6:30:02 AM(UTC)
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.
RC62N
#4RC62N Member Posts: 455  
posted: 7/24/2020 2:35:32 PM(UTC)
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
Code:
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.
Code:
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
#5CyberCitizen Member Posts: 377  
posted: 7/27/2020 9:08:01 AM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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.

Active Discussions

Lansweeper Email to close ticket
by  wgonzalez_hope   Go to last post Go to first unread
Last post: Yesterday at 3:54:01 PM(UTC)
Lansweeper Mass update assets through csv
by  B.L.  
Go to last post Go to first unread
Last post: Yesterday at 3:30:21 PM(UTC)
Lansweeper Restrict the access to the knowledgebase
by  ABEAL   Go to last post Go to first unread
Last post: Yesterday at 1:38:25 PM(UTC)
Lansweeper Wake on Lan Issues
by  Christophe  
Go to last post Go to first unread
Last post: Yesterday at 1:09:12 PM(UTC)
Lansweeper Reason: No email address found for the following user
by  Juha Otava   Go to last post Go to first unread
Last post: Yesterday at 9:59:41 AM(UTC)
Lansweeper Windows 10 Upgrade to 2004
by  CyberCitizen  
Go to last post Go to first unread
Last post: Yesterday at 8:47:16 AM(UTC)
Lansweeper Scanning despite exclusion
by  pskup   Go to last post Go to first unread
Last post: Yesterday at 7:27:08 AM(UTC)
Lansweeper Export ticket to CSV
by  KeithBecker  
Go to last post Go to first unread
Last post: 8/13/2020 5:56:10 PM(UTC)