0

I am trying to write a SQL query to setup a dynamic collection in Configuration Manager 2012. My current query is

select * from SMS_R_System where SMS_R_System.Name LIKE 'cmgr%'

This will grab any server name that starts with cmgr and put it in the specified collection.

My issue is that I need to add another statement in this query to exclude servers that contain the following entries qw, dw and tw. This will prevent my non production servers from being put into the Production collections.

My knowledge of SQL scripting is very limited, so I appreciate any feedback.

bitoiu
  • 6,893
  • 5
  • 38
  • 60
BrianDoughertyE
  • 1
  • which field is the one that contains the entries 'qw', 'dw', 'tw'? – Adam Porad Mar 26 '14 at 17:21
  • Adam, The server names are serverappPW1 or serverappQW1. They are part of the overall server name and it is how we distinguish between production and non production... – BrianDoughertyE Mar 26 '14 at 17:28
  • what is Configuration Manager 2012? – bitoiu Mar 26 '14 at 17:38
  • Configuration manager 2012 is part of the Microsoft System Center Suite of products (Formerly SMS). It is used for Software distribution, patch management and remote management of systems. – BrianDoughertyE May 16 '14 at 18:51

2 Answers2

2

Can you use something like this?

select * from SMS_R_System 
where SMS_R_System.Name LIKE 'cmgr%'
AND SMS_R_System.Name NOT LIKE '%qw%' 
AND SMS_R_System.Name NOT LIKE '%dw%' 
AND SMS_R_System.Name  NOT LIKE '%tw%'
rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • Roryap, Thanks. That might do the trick. I need to test once I get the servers into Config Mgr. I will let you know...Thanks again – BrianDoughertyE Mar 26 '14 at 17:39
  • hello, Thanks for all the advice on this. The query has been working, but not I have a more convoluted one. I have a collection that contains multiple server names. I was able update my existing query by adding OR statment. It looks something like this: – BrianDoughertyE May 16 '14 at 18:45
  • select * from SMS_R_System where SMS_R_System.Name LIKE 'acl%' OR SMS_R_System.Name LIKE 'bre%' OR SMS_R_System.Name LIKE 'eps%' OR SMS_R_System.Name LIKe 'hrk%' AND SMS_R_System.Name NOT LIKE '%qw%' AND SMS_R_System.Name NOT LIKE '%dw%' AND SMS_R_System.Name NOT LIKE '%tw%' The problem now is that the AND Statement is not being recognized an servers with qw,dw and tw are being returned from the query. Any thoughts on how I can make this all work?? BTW, Config Mgr is Microsoft System Center Configuration Manager....Thanks – BrianDoughertyE May 16 '14 at 18:46
0

You might want to take a look at that previous answer, it speaks about using the NOT EXISTS command as part of your query.

Community
  • 1
  • 1
Sir Celsius
  • 822
  • 8
  • 22