DECLARE



@BulletinGuid 

uniqueidentifier--Define the variable with the @VariableName 


,@Filter as uniqueidentifier--Define the variable with the @VariableName 

SET


@BulletinGuid = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

--set @Filter = 'EB3A1A12-E1C7-4431-B060-F0333E4E488C'

select


distinct 

sb

.Name as [Bulletin]


,vi2.Name as [TargetName]


, count (c.Name) as [Computers in Target]

from


vRM_Computer_Item c--vcomputer--This is the computer, used for its guid and name

join


Inv_Applicable_Windows_Software_Update asu--Get updates applicable to each computer


on asu._ResourceGuid = c.Guid

left


join Inv_Installed_Windows_Software_Update isu 


on isu._ResourceGuid = asu._ResourceGuid


and isu.SoftwareUpdateGuid = asu.SoftwareUpdateGuid

join


ResourceAssociation bul2su --Gets us from the update to the bulletin via its ResourceAssociation


on bul2su.ChildResourceGuid = asu.SoftwareUpdateGuid


and bul2su.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293'--Software Bulletin To Software Update

join


vPMCore_SoftwareBulletin sb --Used to get the name, severity and Enabled status of the bulletin


on sb.Guid = bul2su.ParentResourceGuid and sb.Guid = @BulletinGuid

join


vPMCore_SoftwareUpdate su --Used to get the name and severity of the update


on su._ResourceGuid = asu.SoftwareUpdateGuid

left


join ResourceAssociation super 


on super.ParentResourceGuid = asu.SoftwareUpdateGuid


and super.ResourceAssociationTypeGuid = '644A995E-211A-4D94-AA8A-788413B7BE5D'--Software Component Supersedes Software Component

join


CollectionMembership cm--Tables used for the filter parameter


on cm.ResourceGuid = c.Guid


--and cm.CollectionGuid = @Filter

inner


join TargetFilterCollection tfc on tfc.CollectionGuid = cm.CollectionGuid --used for get filters and Targets


inner join TargetFilter tf on tf.Guid = tfc.TargetFilterGuid


inner join vResourceTargetUses rtu on rtu.ResourceTargetGuid = tf.ResourceTargetGuid


inner join ResourceTargetMembershipCache rtmc on rtmc.ResourceTargetGuid = tf.ResourceTargetGuid


inner join vItem vi2 on vi2.Guid = rtmc.ResourceTargetGuid