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.
MECM - SQL query to find last patched date and last installed patch for a machine
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!
2 answers
Sort by: Most helpful
-
-
Amandayou-MSFT 11,046 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