collection query not in AD group, OU, and NO Oracle

[ms-admin]

Hi, in sccm 2007r3 i have a collection query that looks for computers in the domain that don't belong to two OU's and one Active Directory security group and it works fine.

 

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,

SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemOUName = "MyDomain.AC.UK/WINDOWS7 COMPUTERS/Site1" and SMS_R_System.Name not in (select SMS_R_System.Name from SMS_R_System where SMS_R_System.SystemOUName = "MyDomain.AC.UK/WINDOWS7 COMPUTERS/Site1/A201" or SMS_R_System.SystemOUName like "MyDomain.AC.UK/WINDOWS7 COMPUTERS/Site1/A202") and SMS_R_System.Name not in (select SMS_R_System.Name from SMS_R_System where SMS_R_System.SystemGroupName = "MyDomain\\SCCM_GroupName")

 

now i've been asked to leave out computers that have oracle discoverer installed too. i appreciate that this can be done through a script but as this collection query is two thirds there i thought i'd add to it and do it from here instead. i have a query that can find the oracle computers which works but i can't seem to combine it with the current query without getting an error when i try to save it. here is the query for the oracle installs that works when run independently:

 

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,

SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SoftwareProduct on SMS_G_System_SoftwareProduct.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SoftwareProduct.ProductName like "%Oracle Discover%"

 

can anyone help me combine the two into one query? thanks.

 

[Jorgen Nilsson]

Hi,

Something like this should do it, it doesn't complain that it isn't a valid Query:

 

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System inner join SMS_G_System_SoftwareProduct on SMS_G_System_SoftwareProduct.ResourceId = SMS_R_System.ResourceId where SMS_R_System.SystemOUName = "MyDomain.AC.UK/WINDOWS7 COMPUTERS/Site1" and SMS_G_System_SoftwareProduct.ProductName like "%Oracle Discover%" and SMS_R_System.Name not in (select SMS_R_System.Name from SMS_R_System where SMS_R_System.SystemOUName = "MyDomain.AC.UK/WINDOWS7 COMPUTERS/Site1/A201" or SMS_R_System.SystemOUName like "MyDomain.AC.UK/WINDOWS7 COMPUTERS/Site1/A202") and SMS_R_System.Name not in (select SMS_R_System.Name from SMS_R_System where SMS_R_System.SystemGroupName = "MyDomain\\SCCM_GroupName")

 

regards,
Jörgen

[ms-admin]

Hi and sorry for the late reply, I've used your suggestion and it does appear to go through but it doesn't pick up all the computers that have oracle discoverer installed. if i run the query with only the software name like "%oracle discover%" it finds them all for that site (18 of them). but when i combine the queries and use the not like "%oracle discover%" it doesn't exclude the machines. and all pc's that i know have oracle installed are in the collection. i may have to think about creating a script that checks for oracle by wmi or folderexisits!