Report to list names of all policies installing a Patch bulletin.

book

Article ID: 181430

calendar_today

Updated On:

Products

Patch Management Solution for Windows Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

 Please use the following SQL code to get a report to list all policies associated with each bulletin for SMP

 Copy each of  the following code in SQL code section while creating  new reports.

1. SMP Console > Reports > all reports 
2. Reports > Right Click > New > Report > SQL report

 

 

-- Report To list bulletin with list of policies applied 

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

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

where sb2su.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293'  

order by sb.Name

 

 Optional  ****

 

-- to list bulletin details, Policy name and if the policy is enabled 

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

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

left join  

( select ParentItemGuid [policy], ia.Enabled

from ItemReference adv  

inner join ItemActive ia on ia.Guid = adv.ParentItemGuid   

and adv.Hint like 'policy_swu'  

where ia.Enabled = 1  

--group by ParentItemGuid  

)activeSWU on activeSWU.policy = ir.ParentItemGuid 

where sb2su.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293'  

order by sb.Name