Collection query assistance

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 query assistance

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 query assistance

Page: [1]

Message

<< Older Topic Newer Topic >>

Collection query assistance - 5/9/2008 11:35:18 AM

adane

Posts: 8
Score: 0
Joined: 3/3/2006
Status: offline

Hope someone can help me with translating SQL to WQL...

I've got a SQL query that we use in an Excel spreadsheet to simply count the number of patches missing in the company - it's a good reporting tool that management like to see.

it shows though that there are a core of unpatched XP clients dotted around the estate that infrequently get turned on and so miss patches. I have a baseline package that goes out to targetted clients but doesn't use BITS so I have to selectively target.

At the moment I manually add these clients into the "baseline" collection but I'd like to populate the collection dynamically with a query that returns all clients missing more than 10 patches. We can edit the Excel SQL query as below and that just shows us the machines missing more than 10 patches but I can't figure out how to turn this into an SMS query to use in a collection.
I've done some searches in the forums and not found anything but if anyones already done this can I borrow the query code, or can someone help me with the code below.

Thanks
Andy

select distinct sys.Name0 as 'System_Name',
case
when
(sum
(case when patch.status0='applicable' and patch.Qnumbers0<>'none' then 1 else 0 end)
)>0 then
(cast (sum(case when patch.status0='applicable' and patch.qnumbers0<>'none' then 1 else 0 end)as integer(10)))
else '0'
end as 'Count of Missing Patches' , ws.lasthwscan as 'lastscan',
comp.username0 as 'User_Name'
from v_R_System sys
left join v_gs_computer_system comp on comp.resourceid=sys.resourceid
left join v_gs_workstation_status ws on ws.resourceid=comp.resourceid
left join (select distinct resourceid, status0, Qnumbers0, ID0 from v_gs_patchstateex) patch on patch.resourceid=ws.resourceid
where sys.Name0 in (SELECT Name FROM v_FullCollectionMembership
WHERE CollectionID = 'SMS00001' )
and patch.ID0 like 'MS%'
group by
sys.name0, comp.username0, ws.lasthwscan
having case
when
(sum
(case when patch.status0='applicable' and patch.Qnumbers0<>'none' then 1 else 0 end)
)>0 then
(cast (sum(case when patch.status0='applicable' and patch.qnumbers0<>'none' then 1 else 0 end)as integer(10)))
else '0'
end >= 10

Post #: 1

RE: Collection query assistance - 5/9/2008 12:43:40 PM

jnelson993

Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Sorry, but it's just not going to happen that way. In WQL there's no such thing as COUNT() nor can you use all those CASE or CAST statements. WQL is just very limiting in that regard. You can't really get a collection query comprable to what you've got there.

Now, a totally unsupported way to do this would be to create a collection and then go to the SMS db and modify the SQL statement created for that new collection afterwards to returne the machines you're looking for, but I'd really resist doing that.

_____________________________

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

(in reply to adane)

Post #: 2