Hello, I'm attempting to create a query which will pull in the 4-digit number of a list of servers in an existing collection and then identify all the other servers with the same 4-digit number. The query is limited to a specific collection which is a subset of our environment. I thought the query below would work but its producing zero results. I know for certain that something should get produced. 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 substring (Name,2,4) in (select Name from SMS_CM_RES_COLL_TGT01234) and SMS_R_System.Name like '%ABC%' TGT01234 is the collection which contains the list of servers which I am attempting to pull the substring name from; the collection is populated. If I test "select Name from SMS_CM_RES_COLL_TGT01234" it does pull in the list of servers correctly. Not clear on what I'm doing wrong. Some assistance would be appreciated! Thanks.

Hello - I don't think we can use "Name" and "SMS_CM_RES_COLL_TGT01234". You should use collection limiting dialog box for your requirement. So the query will run only on the systems which are their in superset collection. See, More details...http://technet.microsoft.com/en-us/library/bb632338.aspx Anoop C Nair - This posting is provided "AS IS" with no warranties or guarantees, and confers no rights. |Please remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

substring is not valid for WQL. Querries in ConfigMgr are WQL not SQL: http://msdn.microsoft.com/en-us/library/aa394552(v=VS.85).aspx. Also, as Anoop pointed out, SMS_CM_RES_COLL_TGT01234 is an actual table in the DB. Accordingly, because querries use WQL, you must query against the WMI schema and not the schema of the SQL DB. The WMI object to query aganst is SMS_FullCollectionMembership: http://msdn.microsoft.com/en-us/library/cc146287.aspx. It would be something ike this (test this): SELECT ResourceID FROM SMS_FullCollectionMembership fcm INNER JOIN SMS_R_System sys ON fcm.ResourceID = sys.ResourceID WHERE fcm.CollectionID = "XYZ00000" AND sys.Name like "%ABC%"Jason | http://myitforum.com/cs2/blogs/jsandys | Twitter @JasonSandys

Thanks for the responses! Anoop, I am actually using the collection limiting dialog box to pull in all the new servers which I'm looking for....just need the 4-digit number from collection TGT01234 first. Essentially I don't want the query to run against our entire environment as there is a specific subset of servers the results must come from. Jason, thanks for the links. I'll give what you posted a shot and see if it works. What's interesting is a collegue of mine is actually using substring in one of this queries within a collection to pull in some different data thus was curious if something changed. Apparently not and he isn't around for me to ask how he got it to work.

I spoke too soon on the substring, it isn't part of WQL but is part of the ConfigMgr extension to WQL: http://msdn.microsoft.com/en-us/library/cc146121.aspx. To get the resource ID of a collection, just right-click on it and select properties; it'll be listed at the bottom of tfirst page of the properties dialog.Jason | http://myitforum.com/cs2/blogs/jsandys | Twitter @JasonSandys