Need help building a query - We need to get all the Windows 2012/R2 2008/R2 that are not named like ABC

Hi,

We are building a query for a collection. The collection needs to have all the Windows 2008/R2 and Windows 2012/R2 that are but exclude the ones named like OLSREL, CIVOLSREL and OLSLAB

I have the following but I need to adjust the SMS_G_System_SYSTEM.Name like in order to remove all the servers named like ... from the results.

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_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.2" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.2" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.3" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.3" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.1" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.0" and SMS_G_System_SYSTEM.Name like "%OLSREL%" and SMS_G_System_SYSTEM.Name like "%CIVOLSREL%" and SMS_G_System_SYSTEM.Name like "%OLSLAB%"

August 19th, 2015 8:02am

You can also go this way:

1) Create a collection with all Computers like %abc%

2) Then create another collection with all clients and add Exclude rule and specify collection created in step 1 

Free Windows Admin Tool Kit Click here and download it now
August 19th, 2015 8:07am

Looking at your query pretty sure it wont work. you are using AND for everything and pretty sure a server can`t be NT Server 6.2 and NT Server 6.3 at the same time.

Try this one. Will give you all the server that are windows 2008 and UP and exclude the server as long as one of the name is found.

Keep in mind am not report/query expert.

select *  from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Server 6.%" and SMS_R_System.Name not like "%OLSREL%" or
SMS_R_System.Name not like "%CIVOLSREL%" or
SMS_R_System.Name not like "%OLSLAB%"



August 19th, 2015 8:13am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics