Query based collection not being updated properly

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:

Query based collection not being updated properly

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

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Query based collection not being updated properly

Page: [1]

Message

<< Older Topic Newer Topic >>

Query based collection not being updated properly - 8/27/2008 1:15:36 AM

darklord12

Posts: 45
Score: 0
Joined: 7/23/2008
Status: offline

Under advertisement status, 155 computers out of 200 computers are reported as being installed successfully. These 200 computers are in a query based collection that query all clients that have the app less than the version being pushed out. I manually update the collection membership and refresh, but it still shows 200 computers (I expect number to be at 45). I remoted into some random machines and confirmed that the new version was installed successfully. However, when I opened up the resource explorer in config mgr console on these same random machines it still shows the old vesion instead of the new version. Anyone knows what causes the collection not being updated properly?

< Message edited by darklord12 -- 8/27/2008 1:17:21 AM >

Post #: 1

RE: Query based collection not being updated properly - 8/27/2008 1:41:52 AM

jnelson993

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

Can we see the query? It could be that "less than version" criteria. SQL and WQL use a dictionary sort, not a numeric sort. For example, version 9.0 comes AFTER version 11.0 because it's a left-to-right, character-by-character comparison. So, starting from the left, 9 comes after 1.

There's ways around this, but it'd be easier to show you with the query you've got.

_____________________________

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

(in reply to darklord12)

Post #: 2

RE: Query based collection not being updated properly - 8/27/2008 2:00:31 AM

darklord12

Posts: 45
Score: 0
Joined: 7/23/2008
Status: offline

Here it is:
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 = "DR Systems Web Ambassador" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version != "R7_2_28_100_"

With this query, I want to find the app named "DR Systems Web Ambassasor" that has version not like R7_2_28_100_ The program version that is within the quotation is the exact version I copy from add/remove program.

< Message edited by darklord12 -- 8/27/2008 2:09:42 AM >

(in reply to jnelson993)

Post #: 3

RE: Query based collection not being updated properly - 8/27/2008 2:16:01 AM

jnelson993

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

You've got underscore _ characters in your filter predicate...is that by design? Are you looking for underscores or are you looking for any single character at that position? (Forgive me if you know this already) The underscore character has special meaning to SQL/WQL, it means, match ANY single character in the position where the underscore is. So the code:

WHERE version LIKE "A_C"

Would match
AAC
ABC
ACC
ADC
AEC
A9C
... you get the point. In other words, it would match A<anycharacter>C

So, can you show me the results of this SQL query (open SQL Management Studio or SQL Query Analyzer and run it)
<snip>

< Message edited by jnelson993 -- 8/27/2008 2:20:52 AM >

(in reply to darklord12)

Post #: 4

RE: Query based collection not being updated properly - 8/27/2008 2:17:58 AM

jnelson993

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

AH! Sorry, forgot a crucial filter, don't send that last query result:
<snip>

< Message edited by jnelson993 -- 8/27/2008 2:20:34 AM >

_____________________________

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

(in reply to jnelson993)

Post #: 5

RE: Query based collection not being updated properly - 8/27/2008 2:20:12 AM

jnelson993

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

It's late, I'm not thinking clearly
SELECT DISTINCT
Version0
FROM
v_Add_Remove_Programs
WHERE
DisplayName0 LIKE 'DR Systems Web Ambassador'
ORDER BY 1

_____________________________

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

(in reply to jnelson993)

Post #: 6

RE: Query based collection not being updated properly - 8/27/2008 2:45:41 AM

darklord12

Posts: 45
Score: 0
Joined: 7/23/2008
Status: offline

I am looking for underscore. So where do I put the version R7_2_28_100 into the code above? I want to find all versions that is not R7_2_28_100 and exclude those clients that already have R7_2_28_100 from the collection.

< Message edited by darklord12 -- 8/27/2008 2:56:36 AM >

(in reply to jnelson993)

Post #: 7

RE: Query based collection not being updated properly - 8/27/2008 3:05:19 AM

jnelson993

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

Don't put anything in yet, I want to see all of the versions that are returned from that query. Just run it as-is and post the results here.

_____________________________

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

(in reply to darklord12)

Post #: 8

RE: Query based collection not being updated properly - 8/27/2008 3:21:32 AM 1 votes

jnelson993

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

Sorry, I wasn't looking from the website so I wasn't seeing the edit's you've made to your posts. Now that I've refreshed the page I see what you're asking for.

If you want to look for wildcards like _ inside of your WHERE clause like this, you'll need to use the bracket [] operator to specify a set of characters to look for. In this case you want to match a single underscore character [_]. So, your query should look something 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
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS
    ON SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
   AND SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "DR Systems Web Ambassador"
   AND SMS_G_System_ADD_REMOVE_PROGRAMS.Version NOT LIKE "R7[_]2[_]28[_]100[_]"

< Message edited by jnelson993 -- 8/27/2008 3:23:57 AM >

_____________________________

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

(in reply to jnelson993)

Post #: 9

RE: Query based collection not being updated properly - 8/27/2008 3:23:52 AM

darklord12

Posts: 45
Score: 0
Joined: 7/23/2008
Status: offline

< Message edited by darklord12 -- 8/27/2008 3:24:41 AM >

(in reply to jnelson993)

Post #: 10

RE: Query based collection not being updated properly - 8/27/2008 3:30:01 AM

jnelson993

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

Works for me...

_____________________________

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

(in reply to darklord12)

Post #: 11

RE: Query based collection not being updated properly - 8/27/2008 3:50:46 AM

darklord12

Posts: 45
Score: 0
Joined: 7/23/2008
Status: offline

The query returns much more computers with version not like r7_2_28_100 this time, which is something I expect. Would "R7[_]2[_]28[_]100[_]" work with "not equal to" because I get nothing when I use "not equal to" instead of "not like". Thanks so much again for the quick and helpful responses

(in reply to darklord12)

Post #: 12

RE: Query based collection not being updated properly - 8/27/2008 4:22:19 AM

jnelson993

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

Nope, It will only work with LIKE and NOT LIKE

_____________________________

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

(in reply to darklord12)

Post #: 13

RE: Query based collection not being updated properly - 8/27/2008 4:26:26 AM

jnelson993

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

BTW, I just created an article on escaping % and _ characters in your queries to better explain some of this:

http://myitforum.com/cs2/blogs/jnelson/archive/2008/08/27/121684.aspx

_____________________________

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

(in reply to jnelson993)

Post #: 14

Page: [1]

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Query based collection not being updated properly

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts

0.297