Hello All, I have Single tier SMS 2003 Hierarchy and approximately 2000 Client machines. I have to create a report with following information 1. Machine Name 2. Count of updates missing patches next to corresponding machines Kindly help me creating this query for SMS 2003

Hi Chanchal,Here is the examplefor only one patche, for multiple patches you need to modify the quries... I would suggest to upgrade to SCCM.... SCCM has lot of good reportshttp://blogcastrepository.com/forums/p/29671/32465.aspx#32465declare @P1 as char(8)set @P1 = 'Ms07-064' select distinct sys.Name0,CASE SUM(CASE WHEN ps.Status0='Installed' and ps.ID0 = @P1 THEN 1 WHEN ps.Status0='Applicable' and ps.ID0 = @P1 THEN -1 ELSE 0 END)WHEN 0 THEN 'N/A'ELSE (select distinct CASE WHEN ps2.LastStateName='No Status' THEN '***Missing***' ELSE ps2.LastStateName END + CASE WHEN ISNULL(CAST(ps2.LastStatusTime AS nvarchar), '##')='##' THEN '' ELSE ' - ' + CAST(ps2.LastStatusTime AS nvarchar) + ' GMT' ENDfrom v_R_System sys2join v_GS_PatchStatus ps2 on sys2.ResourceID=ps2.ResourceIDwhere ps2.ID=@P1 and sys.Name0=sys2.Name0) ENDas 'MS07-064',From v_R_System sysjoin v_GS_PATCHSTATE ps on sys.ResourceID=ps.ResourceIDwhere (ps.ID0 in (@P1) or ps.QNumbers0 in (@P1)) and sys.Name0 in (select fcm.Name from v_FullCollectionMembership fcm where fcm.CollectionID = '<Collection ID>')group by sys.Name0order by sys.Name0Abhishek Joshi http://blogcastrepository.com/blogs/abi/default.aspx http://blogcastrepository.com/members/Abhishek.aspx

For future questions you might try posting them in the SMS Newsgroups.http://technet.microsoft.com/en-us/sms/bb839598.aspxhttp://www.sccm-tools.com http://sms-hints-tricks.blogspot.com