Solved

SQL Statement to Query list of PCs - SCCM 2012

Posted on 2014-09-28
3
185 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now