WSUS Reports - Querying WSUS database

hi,

   iam trying fetch approved  security , critical updates status machine .but results given default report , query different.here query using

declare @computername varchar(200)
declare @installcount int
declare @failedcount int
declare @unknowncount int
declare cursor_variable cursor
select [fulldomainname]     
  [susdb].[dbo].[tbcomputertarget] ([targetid] in
(select   [targetid]  [susdb].[dbo].[tbtargetintargetgroup]
( [targetgroupid] in (select [targetgroupid]
  [susdb].[dbo].[tbtargetgroup]where [name] in
('wks staging',
'itinfrastructure',
'wks production - doz',
'wks service desk',
'workstations',
'wks exception',
'wks fut',
'wks win7 corpsupport',
'wks win7',
'wks production',
'wks production - mindscape',
'wks production - al salam',
'wks corporate support',
'wks corporate systems',
'ie7 - doz',
'ie7 - mindscape',
'wks production - dac',
'wks pre-production')))))

open cursor_variable

fetch next cursor_variable @computername
while @@fetch_status = 0 
begin 
select  @installcount=count(distinct [mskbnumber])
        [susdb].[dbo].[v_allsecurityandcriticalupdatesstatusonallcomputers] [status]='installed' , [action]='install' , computername=@computername
select  @failedcount=count(distinct [mskbnumber])
        [susdb].[dbo].[v_allsecurityandcriticalupdatesstatusonallcomputers] [status]='failed' , [action]='install' , computername=@computername
select  @unknowncount=count(distinct [mskbnumber])
        [susdb].[dbo].[v_allsecurityandcriticalupdatesstatusonallcomputers] [status]='unknown' , [action]='install' , computername=@computername
update workstations set installed=@installcount,failed=@failedcount,unknown=@unknowncount
computername=@computername

fetch next cursor_variable @computername
end;

close cursor_variable;
deallocate cursor_variable

 i have tried below of no use

select  knowledgebasearticle

from public_views.vcomputertarget comp
join public_views.vupdateinstallationinfo uinf on comp.computertargetid = uinf.computertargetid
join public_views.vupdate updt on uinf.updateid = updt.updateid
join public_views.vupdateapproval udta on updt.updateid = udta.updateid

where classificationid in ('e6cf1350-c01b-414d-a61f-263d14d133b4', '0fa1201d-4330-4fa8-8ae9-b877473b6441')
and  name='machinename' , [action]='install' , (state='1' or state='4' )

what mistake doing?

i want count of approved security , critical updates installed on machine.

any help?

---

gowthaman j

hi,

according description, question seems related development. focus on management issue , here not best support resource development, recommend initial new thread in our msdn forum.

msdn forum:http://social.msdn.microsoft.com/forums/en-us/categories/

thank understanding.

best regards,

clarence zhang

---

please remember click “mark answer” on post helps you, , click “unmark answer” if marked post not answer question. can beneficial other community members reading thread.

Windows Server  >  WSUS