question

SrikanthNarayanan-1504 avatar image
0 Votes"
SrikanthNarayanan-1504 asked saldana-msft edited

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!

configuration-manager-generalconfiguration-manager-updates
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GarthJones-9654 avatar image
0 Votes"
GarthJones-9654 answered GarthJones-9654 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes understood. Please let me know if there is a way to get the complete compliance report of all the machines, showing which patches are installed and which are not installed, including the installed dates?

0 Votes 0 ·

The built-in report "comp 5" will show you these details per computer.

0 Votes 0 ·
Amandayou-MSFT avatar image
0 Votes"
Amandayou-MSFT answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.