Collection building

[Mich]

I am attempting to build collections based on their patch status. For example, if a machine is missing MS patch MS04-041 it will be placed in a specific collection. I built the following SQL query to provide the information I need:

select sys.Netbios_Name0
from v_R_System sys
join v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
join v_GS_PatchStatus ps on sys.ResourceID=ps.ResourceID
where fcm.CollectionID = 'BOP0001A' and ps.LastState != 105 and ps.id = 'MS04-041'

The problem I'm having is actually building the collection because SMS keeps telling the query isn't valid (it runs perfectly in SQL QA). I'm assuming it isn't valid because it isn't WQL. Is this correct? If so, how do I port this query into WQL format? If not, what am I missing?

Thanks in advance.

-If it ain't broke, break it and make it better.

 

[johnnyb99]

What's the extact message that SMS displayed?

 

[Mich]

The query statement you entered is not valid. Please enter a valid query statement.

I thought I had found a way around this. I built a view (v_MissingPatches) based on this query,

CREATE VIEW dbo.MissingPatches
AS
SELECT     fcm.Name, ps.ID
FROM         dbo.v_FullCollectionMembership fcm INNER JOIN
                      dbo.v_GS_PatchStatus ps ON fcm.ResourceID = ps.ResourceID
WHERE     (fcm.CollectionID = 'BOP0001A') AND (ps.LastState <> 105)

I then created a collection using,

select v_MissingPatches.Name from v_MissingPatches where v_MissingPatches.id = 'ms04-041'

But I STILL get the same message?!?!?!

-If it ain't broke, break it and make it better.

 

[johnnyb99]

When I first read your post, I copied your query and pasted it into an SMS report. The report ran without incident, although nothing was returned.

How are you running the query, beside, via QA?

 

[Mich]

That's the only way.

Technically, nothing should be returned because all of your machines should have that patch. I find it interesting that your install allowed you to build a collection using that SQL query. I had to use the following WQL query to build the collection:

select *  from  SMS_R_System inner join SMS_G_System_PATCHSTATE on SMS_G_System_PATCHSTATE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PATCHSTATE.ID = "MS04-041" and SMS_G_System_PATCHSTATE.Status = "Applicable"

It works now.

-If it ain't broke, break it and make it better.