How do I create a Report that shows a general summary for all Patch Management executions?

Products

• Patch Management Solution for Windows
• IT Management Suite

Issue/Introduction

 

Resolution

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

Is there a way to report on only the Patch Management Executions? 

There is, please create a new SQL Report and enter the following data (Default shows the last 30 days.

NOTE: this query 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