Report returning Patch Policies containing bulletins and policy status

Products

• Patch Management Solution for Windows
• Patch Management Solution
• IT Management Suite

Issue/Introduction

The following custom report will give you Policy name, list of bulletin's added in specific policy and Policy status i.e. Enabled or Disabled.

Environment

Patch Management Solution 8.x

Resolution

Create a new SQL report and copy/paste below query:

SELECT Distinct ir.ParentItemGuid as [policy GUID], sb2su.ParentResourceGuid [BulletinGuid],sb.Name as [Bulletin Name], i.Name as [Policy Name],

CASE
    WHEN ia.Enabled = 1 THEN 'Enabled'
    WHEN ia.Enabled = 0 THEN 'Disabled'
    ELSE '<<< Unknown >>>'
  END
  AS [Policy Status]
 
FROM ItemReference ir   
inner join ResourceAssociation sb2su on sb2su.ChildResourceGuid = ir.ChildItemGuid  
and  ir.Hint like 'policy_swu'  
inner join Item i on i.Guid = ir.ParentItemGuid
inner join vPMCore_SoftwareBulletin sb on sb2su.ParentResourceGuid = sb.Guid
inner join ItemActive ia on ia.Guid=ir.ParentItemGuid
where sb2su.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293'  
order by sb.Name

 

 

Example result set: