Video Screencast Help

In what table is the PM software bulletin info held?

Created: 07 Oct 2013 • Updated: 01 Nov 2013 | 2 comments

I'm trying to build a custom report that shows what the filesize is for each update within a bulletin.  I've found most of what I need in dbo.inv_software_update, but I can't find the table that contains the bulletins... anyone know what table those are in?

Operating Systems:

Comments 2 CommentsJump to latest comment

Ludovic Ferre's picture

Hi there,

Here's a sample query I'm using often, this should contain plenty of interesting objects for you:

select --top 100
       swb.Name as 'Bulletin name',
       swu.Name as 'Update name', 
       SoftwareBulletin = swb.Guid,
       activ.Enabled as BulletinState,
       SoftwareUpdateGuid = asu.SoftwareUpdateGuid, 
       Installed = case when (isu._ResourceGuid is not null) then 1 else 0 end,
       usu.ParentResourceGuid as SupersededBy
  from Inv_Applicable_Windows_Software_Update asu
  join RM_ResourcePatch_Software_Update swu
    on asu.SoftwareUpdateGuid = swu.Guid
  left join Inv_Installed_Windows_Software_Update isu 
    on isu.SoftwareUpdateGuid = asu.SoftwareUpdateGuid  
   and isu._ResourceGuid =  asu._ResourceGuid  
  join ResourceAssociation swb2swu
    on swb2swu.ChildResourceGuid = asu.SoftwareUpdateGuid 
   and swb2swu.ResourceAssociationTypeGuid = '7eeab03a-839c-458d-9af2-55db6b173293'
  join RM_ResourceSoftware_Bulletin swb 
    on  swb2swu.ParentResourceGuid = swb.Guid    
  join ItemActive activ 
    on swb.Guid = activ.Guid       
  join Inv_AeX_AC_Identification cid 
    on asu._ResourceGuid = cid._ResourceGuid  
  left outer join ResourceAssociation usu
    on asu.SoftwareUpdateGuid = usu.ChildResourceGuid
   and usu.ResourceAssociationTypeGuid = '644a995e-211a-4d94-aa8a-788413b7be5d'
 where activ.Enabled = 1
   and isu._ResourceGuid is null
   and usu.ChildResourceGuid is null
 order by, swb.Name desc,

Ludovic FERRÉ
Principal Remote Product Specialist

Ludovic Ferre's picture

BTW, the report name would read something like "Vulnerable updates with active policy by computer".

Ludovic FERRÉ
Principal Remote Product Specialist