Solved

SQL Statement to Query list of PCs - SCCM 2012

Posted on 2014-09-28
3
178 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
Comment Utility
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 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 400 total points
Comment Utility
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
Comment Utility
Thanks.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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 …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

15 Experts available now in Live!

Get 1:1 Help Now