declare @Filter as uniqueidentifier--Define the variable with the @VariableName 
set @Filter = 
--'036E2BD3-4E10-4C23-B262-9A6B128A597F' --Filter containing computers with Inventory Solution Plug-in
'EB3A1A12-E1C7-4431-B060-F0333E4E488C' --Filter containing ALL managed computers for this SMP

select 
       c.Name as 'Computer Name'
      ,vc.[IP Address] as 'IP Address'
      ,sb.Name + ': ' + su.Name as 'Bulletin: Update'
      ,ISNULL (su.SeverityName, sb.Severity) as 'Severity' 
      ,case
            when isu._ResourceGuid is not null then 'Installed'
            when isu._ResourceGuid is null then 'Vulnerable'
            else 'Unknown'
      end as 'IsInstalled', ese.Start as 'Install Start Date', ese.Status --Vulnerable to Revised Update or updated Service Pack etc.
      ,case 
            when sb.Enabled = 1 then 'True'
            when sb.Enabled = 0 then 'False'
            else 'Unknown'
      end as 'Downloaded'
      ,ci.Name as 'Vendor'
      ,case
	    when reb.RebootRequired = 1 then 'Yes'
	    when reb.RebootRequired = 0 then 'No'
	    else 'Unknown'
	end as 'Reboot Required'
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 vPMCore_SoftwareUpdateAdvertismentRebootRequired reb on reb._ResourceGuid = asu._ResourceGuid and reb._SWUGuid = asu.SoftwareUpdateGuid --Reboot Required 1=YES & 0=NO (Note: If 'NULL' returned; could be purged data or problem with missed event - see TECH167291)
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
join vPMCore_SoftwareUpdate su --Used to get the name and severity of the update
      on su._ResourceGuid = asu.SoftwareUpdateGuid
left join ItemReference ir on ir.ChildItemGuid = su._ResourceGuid --remove 'left' join to display only updates with a created Policy
	and ir.Hint = 'swu'
left join Evt_AeX_SWD_Execution ese on c.Guid = ese._ResourceGuid --NULL = data has been purged from maintenance or failed to process etc.
	and ese.AdvertisementId = ir.ParentItemGuid
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
join ResourceAssociation bul2vend--Used to get the link from the bulletin to vendor
      on bul2vend. ParentResourceGuid = sb.Guid
            and bul2vend.ResourceAssociationTypeGuid = '2FFEB9F0-601E-4746-A830-BDB200076503'--Software Bulletin To Vendor
join vRM_Company_Item ci on ci.Guid = bul2vend.ChildResourceGuid--Used to get the name of the vendor
left join vComputer vc on c.Name = vc.Name
where super.ChildResourceGuid is null     --Exludes all superseded updates, not targetable by Patch