Collection Queries

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Collection Queries

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Collection Queries

Page: [1]

Message

<< Older Topic Newer Topic >>

Collection Queries - 8/11/2008 10:46:08 AM

Joe909

Posts: 2
Score: 0
Joined: 8/11/2008
Status: offline

I'm trying to combine two collection queries and I can't wrap my brain around the sytax, so I was hoping someone here can help me out. Right now I've got the following two separate queries. How can I combine them into a single collection query?

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 SystemGroupName = "MyDomain\\MyDomainGroupName"

SELECT sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client from SMS_R_System as sys WHERE sys.ResourceID NOT IN (SELECT ARP.ResourceID FROM SMS_G_System_ADD_REMOVE_PROGRAMS as ARP WHERE ARP.DisplayName = "SoftwareDisplayName" AND ARP.Version = "1.0.0.0.1")

Post #: 1

RE: Collection Queries - 8/11/2008 11:19:46 AM

mhudson

Posts: 539
Score: 12
Joined: 4/1/2007
From: College Station, TX
Status: offline

The easiest way is to have 2 queries for the collection. You can click on the Star/sun icon and just keep adding queries to the collection.

_____________________________

Matthew Hudson
http://sms-hints-tricks.blogspot.com/
http://www.sccm-tools.com

(in reply to Joe909)

Post #: 2

RE: Collection Queries - 8/11/2008 11:37:22 AM

jnelson993

Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Yeah, Matthew's right on, if you're combining the output of those 2 queries (meaning you want to take all of the computers in the first query AND all of the computers in the second query) then just adding queries is a nice easy way to go. It's the equivalent of one big query that uses OR in the WHERE clause like "WHERE this=blah OR that = blah"

However, if you want to only have it return only those machines that are in BOTH queries, then you need to combine the queries into one.

Using your 2 queries as an example:

Results of first query and second query combined:
SELECT
sys.ResourceID,
sys.ResourceType,
sys.Name,
sys.SMSUniqueIdentifier,
sys.ResourceDomainORWorkgroup,
sys.Client
FROM
SMS_R_System AS sys
WHERE sys.ResourceID NOT IN (SELECT
                              ARP.ResourceID
                            FROM
                              SMS_G_System_ADD_REMOVE_PROGRAMS AS ARP
                            WHERE ARP.DisplayName = "SoftwareDisplayName"
                                   AND ARP.Version = "1.0.0.0.1")
OR sys.SystemGroupName = "MyDomain\\MyDomainGroupName"

Machines that exist in first query AND second query:
SELECT
sys.ResourceID,
sys.ResourceType,
sys.Name,
sys.SMSUniqueIdentifier,
sys.ResourceDomainORWorkgroup,
sys.Client
FROM
SMS_R_System AS sys
WHERE sys.ResourceID NOT IN (SELECT
                              ARP.ResourceID
                            FROM
                              SMS_G_System_ADD_REMOVE_PROGRAMS AS ARP
                            WHERE ARP.DisplayName = "SoftwareDisplayName"
                                   AND ARP.Version = "1.0.0.0.1")
AND sys.SystemGroupName = "MyDomain\\MyDomainGroupName"

< Message edited by jnelson993 -- 8/11/2008 11:38:57 AM >

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to mhudson)

Post #: 3