Learn how to a build a cloud-first strategyRegister Now

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

sccm collection query

Hi,

I am working on creating a new collection in SCCM that should include all computers, except for members of a specific AD group.
I have tried the following query, but it does not seem to work as intended. Query on "is equal" to a specific group works fine, but when I try to use not equal or != the query ends up showing all computers even if I include "where SMS_R_System.SystemGroupName = "mydomain\\Domain Computers"" or not.

Any ideas?

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.SystemGroupName = "mydomain\\Domain Computers" and SMS_R_System.SystemGroupName != "mydomain\\specialservers"

Thank you.
0
synackrst
Asked:
synackrst
  • 3
  • 3
  • 2
  • +1
1 Solution
 
KezziCommented:
try this instead

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.SystemGroupName = "mydomain\\Domain Computers" and SMS_R_SYSTEM.ResourceID not in (SMS_R_SYSTEM.ResourceID ,SMS_R_SYSTEM.Name, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemGroupName = "mydomain\\specialservers")

0
 
KezziCommented:
sorry missed the select in the ()  try this one instead

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.SystemGroupName = "mydomain\\Domain Computers" and SMS_R_SYSTEM.ResourceID not in (select SMS_R_SYSTEM.ResourceID ,SMS_R_SYSTEM.Name, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemGroupName = "mydomain\\specialservers"
0
 
synackrstAuthor Commented:
Hi,

Thanks, but getting a syntax error on the last one. Know whats wrong with it?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
merowingerCommented:
try single quotes (' not ") in the query
0
 
Healthworld_IT_GuyCommented:
correct syntax as follows

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.SystemGroupName = "Domain\\Domain Computers" and SMS_R_System.ResourceId not in (Select SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.SystemGroupName = "Domain\\Special_Servers")

cheers ;)
0
 
synackrstAuthor Commented:
It works now, thanks.
0
 
KezziCommented:
i missed a ) off my query.... sorry for not getting back sooner.
0
 
merowingerCommented:
Next time please split points and also assign some to Kezzi...
0
 
synackrstAuthor Commented:
Sorry my bad!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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