wrong results from collection query?

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:

wrong results from collection query?

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 >> wrong results from collection query?

Page: [1]

Message

<< Older Topic Newer Topic >>

wrong results from collection query? - 11/20/2008 4:51:02 PM

mbinetti

Posts: 873
Score: 5
Joined: 4/1/2005
Status: offline

i am creating a collection that should report machines that DO NOT have a specific KB in the add remove programs example...
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 inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName != "Hotfix for Windows XP (KB937468) (For testing purposes only)"

the criteria says it is not equal to ...xyz...

if it is not equal to then the machine that have Hotfix for "Windows XP (KB937468) (For testing purposes only)" should not report but they all do.

any ideas?

_____________________________

Mauricio

Post #: 1

RE: wrong results from collection query? - 11/20/2008 4:57:16 PM

bmason505

Posts: 2031
Score: 114
Joined: 1/23/2003
From: Minneapolis, MN
Status: offline

You need a subselect query.

_____________________________

Brian Mason
MCSA\MCSE\MS MVP - SCCM
Wells Fargo
http://www.miscusergroup.org/

(in reply to mbinetti)

Post #: 2

RE: wrong results from collection query? - 11/20/2008 5:02:27 PM

jnelson993

Posts: 959
Score: 132
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

What you're looking for by writing it the way you are is, "show me all of the machines that have any software except that one hotfix" Well, that will match every machine you have.

Instead you need to first write a subquery to find all of the resourceIDs for machines that HAVE that hotfix, then find all of the machines that aren't in that list.

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.ResourceID IN
    (SELECT
        ResourceID
     FROM
        SMS_G_System_ADD_REMOVE_PROGRAMS
     WHERE
        SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName != "Hotfix for Windows XP (KB937468) (For testing purposes only)")

_____________________________

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

(in reply to mbinetti)