Report that shows a general summary for Patch Management executions

Products

• Patch Management Solution for Windows
• IT Management Suite

Issue/Introduction

 This article provides information on creating a report that shows a summary of Patch Management executions

Environment

ITMS 8.x

PM 8.x

Resolution

The Table Evt_AeX_SWD_Execution holds all of the Patch Management execution and status, however it also holds status information of Software Management installs.  There is a Report in the SMP Console that shows Software Execution Summary (Reports > Software > Delivery > Execution Summary) but not for Patch Management executions.

To create a new SQL Report for Patch Management executions use the following data (Default shows the last 30 days:

NOTE: This query also shows All Failures, even those that are later successful.

select  [Status] =CASE when e.status = 'Command executed' then 'Success'

      when e.status = 'Failed' then 'Failed'

      when e.Status = 'Terminated' then 'Terminated'

      else 'Other'

End, COUNT ([Status]) as 'Count'

from Evt_AeX_SWD_Execution  e

where e._eventTime > GETDATE() – 30  -- Count Executions from last XX Days

and PackageId in (select distinct ItemGuid from [Evt_NS_Item_Management]

      where [ItemClassGuid] = '6E89DC4B-693A-4BD3-AEFA-385CCD1874E8')  -- Patch Solution Class GUID

group by [Status]

order by 2 desc