Solved

SQL Statement to Query list of PCs - SCCM 2012

Posted on 2014-09-28
3
189 Views
Last Modified: 2014-10-23
Hi Everybody,

I am trying to create list of computers in the collection (SCCM 2012) which has SCCM client installed but I also want to make exception do not include a few computers (example do not include computer start with Test-01)

Here is my Query Statement:

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.Client = "1"


Anybody can help me out with the above issue?

Thanks.

Emese
0
Comment
Question by:Szuromi
3 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 40349067
you can use NOT LIKE
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.Client NOT LIKE 'Test-01%'

Open in new window

0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 400 total points
ID: 40349574
Hi!

Depending on the size of the table then you should use EXCEPT like this

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.Client = "1"
EXCEPT
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.Client = "1"
AND SMS_R_SYSTEM.Name LIKE "Test-01%"

Open in new window


This would work much faster than the not like suggestion above and even more if you have index on the table that has these two columns (Client, Name ) on that table.

Regards,
   Tomas Helgi
0
 

Author Closing Comment

by:Szuromi
ID: 40400238
Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question