MECM - SQL query to find last patched date and last installed patch for a machine

Srikanth Narayanan 1 Reputation point
2021-10-19T13:27:44.957+00:00

Hi All,

I am trying to find the last patched date and last installed patch KB number/Software update name for multiple machines through a SQL query.

The query that I have now gives the patch deployment status of each machine. But what I am looking for is the last patched date and last installed patch KB number/Software update name. Would really appreciate any help on this. 

Declare @DeploymentName as Varchar(255)
Set @DeploymentName = 'Aug patchset%' -- Specify Software update deployment name
select 
vrs.Name0,
vrs.Active0,
vrs.AD_Site_Name0,
vrs.User_Name0,
vrs.Operating_System_Name_and0,
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,
wsus.LastErrorCode as 'LasErrorCode',
wsus.LastScanTime as 'LastWSUSScan',
DateDiff(D, wsus.LastScanTime, GetDate()) as 'LastWSUSScan Age',
wks.LastHWScan,
DateDiff(D, wks.LastHwScan, GetDate()) as 'LastHWScan Age'
from v_CIAssignment a
join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_R_System vrs on vrs.ResourceID = assc.ResourceID
join v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID
join v_UpdateScanStatus wsus on wsus.ResourceID = assc.ResourceID
where a.AssignmentName like @DeploymentName
and assc.StateType in (300,301)
order by 11 desc

Thanks!

Microsoft Configuration Manager Updates
Microsoft Configuration Manager Updates
Microsoft Configuration Manager: An integrated solution for for managing large groups of personal computers and servers.Updates: Broadly released fixes addressing specific issue(s) or related bug(s). Updates may also include new or modified features (i.e. changing default behavior).
956 questions
Microsoft Configuration Manager
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Garth Jones 1,656 Reputation points MVP
    2021-10-19T15:12:38.017+00:00

    So knowing when the last SU was applied will NOT tell you if all SU have applied or if there are outstanding SU that need to be applied.

  2. Amandayou-MSFT 11,041 Reputation points
    2021-10-21T09:39:59.187+00:00

    Haven't heard from you for some time, is Garth's answer helpful to you? If it is helpful, please accept answer. It will make someone who has the similar issue easily find the answer.

    If you have any other issues, please don't hesitate to let us know.

    Thanks and have a nice day.

    Best regards,
    Amanda

    0 comments