[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15595
  • Last Modified:

How do I build an SCCM collection based on a letter in the computer name?

I am trying to create collections based on the machine names.

Lets say the the names are PROSERVD01, PROSERVP01, PROSERVS01

I would like the query to check the letter at the third position from the right (D,P,S) and display only machines that match a certain letter. Any help will be greatly appreciated.
0
Aerodrake
Asked:
Aerodrake
  • 3
  • 3
  • 2
1 Solution
 
McNeticCommented:
Manually edit the sql query statement (it's labelled 'show sql" or "show query" or something) to look like this:

select * from SMS_R_System where LEFT(RIGHT(Name, 3), 1) = "D"
0
 
AerodrakeAuthor Commented:
Thanks for the reply.

I was working with the same idea.. but I'm getting syntax errors. I found this on a unrelated page, do you know if this is true for all WQL?

"Query Syntax is WQL not SQL - some minor syntactical differences.  In particular, advanced string manipulations are not supported in WQL (example: Right, Left, Substring, etc.)"
Source: http://securevantage.spaces.live.com/blog/cns!905E136EE69247B4!183.entry
0
 
McNeticCommented:
I'm thought that query strings were sql strings directly issued to the sql server by sccm, but I'm not sure about that. I don't have a sccm server at hand to test it by mysqlf. I can't find any reference to sccm on the page linked by you, though.

However, if you now that the first part of the machine name is constant and equals to PROSERV, the following query should work:

select * from SMS_R_System where Name like "PROSERVS%"
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
dssbrgCommented:
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.NetbiosName like "COMP__NAME%"
0
 
dssbrgCommented:
Actually, a more decisive answer than what I put above.

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.NetbiosName like "PROSERVD%" or  SMS_R_System.NetbiosName like "PROSERVP%" or "PROSERVS%"
0
 
AerodrakeAuthor Commented:
The machine names do not all start the same, hence the reason I'm looking for the 3rd letter from the end.
0
 
dssbrgCommented:
Are they consistent in the number of characters?  If so, then use the "_______D%" (7 x "_").  Otherwise, I would recommend using the first Experts comment and morph it with mine using the OR statements.
0
 
AerodrakeAuthor Commented:
Yeah, thats the problem.. they are not all the same length and as far as I cant tell WQL does not support advanced string manipulation and I'm not able to find much about it. I might be stuck on this one.. I think I'll use system groups instead. Thanks for the replies.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now