SQL Query's for all status

_{Wednesday, January 9, 2019 11:18 AM}

Hi Team,

I have WSUS standalone with WID. I need to pull the report from SQL for below status based on approved patches. 

> Waiting to get install

> Approved updates installed and waiting for reboot

> Approved updates installed and rebooted successfully.

Please help.

Regards, Santhosh B S

_{Thursday, January 10, 2019 6:27 AM}

Hello Santhosh B S,
 
Refer to this Blog.
 
Baseline compliance report, using public WSUS views
https://blogs.technet.microsoft.com/wsus/2008/06/20/baseline-compliance-report-using-public-wsus-views/
 

USE SUSDB


DECLARE @TargetGroup nvarchar(30)


DECLARE @Days int


SELECT @TargetGroup = 'Test Machines'


SELECT @Days = 7


 


-- Find all computers in the given @TargetGroup


SELECT vComputerTarget.Name


FROM PUBLIC_VIEWS.vComputerGroupMembership


INNER JOIN PUBLIC_VIEWS.vComputerTarget on vComputerGroupMembership.ComputerTargetId = vComputerTarget.ComputerTargetId


INNER JOIN PUBLIC_VIEWS.vComputerTargetGroup on vComputerGroupMembership.ComputerTargetGroupId = vComputerTargetGroup.ComputerTargetGroupId


WHERE


vComputerTargetGroup.Name = @TargetGroup


-- And only select those for which an update is approved for install, the


-- computer status for that update is either 2 (not installed), 3 (downloaded),


-- 5 (failed), or 6 (installed pending reboot), and


-- the update has been approved for install for at least @Days


AND EXISTS


(


select * from


PUBLIC_VIEWS.vUpdateEffectiveApprovalPerComputer


INNER JOIN PUBLIC_VIEWS.vUpdateApproval on vUpdateApproval.UpdateApprovalId = vUpdateEffectiveApprovalPerComputer.UpdateApprovalId


INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic on vUpdateInstallationInfoBasic.ComputerTargetId = vComputerTarget.ComputerTargetId


WHERE


vUpdateEffectiveApprovalPerComputer.ComputerTargetId = vComputerTarget.ComputerTargetId


AND vUpdateApproval.Action = 'Install'


AND vUpdateInstallationInfoBasic.UpdateId = vUpdateApproval.UpdateId


AND vUpdateInstallationInfoBasic.State in (2, 3, 5, 6)


AND DATEDIFF (day, vUpdateApproval.CreationDate, CURRENT_TIMESTAMP) > @Days


)

 
In above query listed in the Blog, you should change @TargetGroup and @Days (how many days updates have been approved for install to them for) to meet your requirements. And change the state number (2,3,5,6) at the end of the script. 0=Unknown, 1=NotApplicable, 2=NotInstalled, 3=Downloaded, 4=Installed, 5=Failed, 6=Installed Pending Reboot. 
 
Hope my answer could help you and look forward to your feedback.
 
Best Regards,
Ray

Please remembers to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

_{Friday, January 11, 2019 9:44 AM}

Hello,
 
I noticed you have not updated for several days. Has your issue been solved? Or is there any update?
 
Feel free to feedback.
 
Best Regards,
Ray

Please remembers to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.