The following queries have been tested againt PMS 7.1.2; however, they should also work against 7.1.1 as well:
As these two queries do not say if an item is still checked, they will not display the current status of that page section.
--/ All Vendors and Products that have any bulletin-info:
SELECT DISTINCT TOP (100) PERCENT dbo.RM_ResourceCompany.Name, dbo.RM_ResourceSoftware_Release.Name AS Product
FROM dbo.RM_ResourceCompany INNER JOIN
dbo.ResourceAssociation AS ra3 ON dbo.RM_ResourceCompany.Guid = ra3.ChildResourceGuid INNER JOIN
dbo.RM_ResourcePatch_Software_Update AS su INNER JOIN
dbo.ResourceAssociation AS ra ON su.Guid = ra.ChildResourceGuid INNER JOIN
dbo.RM_ResourceSoftware_Bulletin AS b ON ra.ParentResourceGuid = b.Guid INNER JOIN
dbo.ResourceAssociation AS ra2 ON su.Guid = ra2.ParentResourceGuid INNER JOIN
dbo.RM_ResourceSoftware_Release ON ra2.ChildResourceGuid = dbo.RM_ResourceSoftware_Release.Guid ON
ra3.ParentResourceGuid = dbo.RM_ResourceSoftware_Release.Guid
ORDER BY dbo.RM_ResourceCompany.Name, Product
--/ All Vendors/Products that do NOT have a valid bulletin in the DB:
SELECT DISTINCT TOP (100) PERCENT dbo.RM_ResourceCompany.Name, dbo.RM_ResourceSoftware_Release.Name AS Product
FROM dbo.RM_ResourcePatch_Software_Update AS su INNER JOIN
dbo.ResourceAssociation AS ra ON su.Guid = ra.ChildResourceGuid INNER JOIN
dbo.RM_ResourceSoftware_Bulletin AS b ON ra.ParentResourceGuid = b.Guid INNER JOIN
dbo.ResourceAssociation AS ra2 ON su.Guid = ra2.ParentResourceGuid RIGHT OUTER JOIN
dbo.RM_ResourceCompany INNER JOIN
dbo.ResourceAssociation AS ra3 ON dbo.RM_ResourceCompany.Guid = ra3.ChildResourceGuid INNER JOIN
dbo.RM_ResourceSoftware_Release ON ra3.ParentResourceGuid = dbo.RM_ResourceSoftware_Release.Guid ON
ra2.ChildResourceGuid = dbo.RM_ResourceSoftware_Release.Guid
WHERE (b.Guid IS NULL)
ORDER BY dbo.RM_ResourceCompany.Name, Product
The following query uses the check-box logic within that page:
--/ Software that is selected in the "Vendors and Software" PMImport section
declare @hdoc int
declare @doc varchar(max)
select @doc = convert(varchar(max), State) from vItem
where ClassGuid = '730ce470-aa29-442f-a37b-965a1fd0cf7d'
exec sp_xml_preparedocument @hdoc output, @doc
select v.Name Vendor, sw.Name Product from RM_ResourceCompany v
inner join ResourceAssociation ra on v.Guid=ra.ChildResourceGuid
inner join RM_ResourceSoftware_Release sw on ra.ParentResourceGuid = sw.guid
left outer join
(select release.guid,release.[Name] from openxml(@hdoc, '/item/PatchManagementSolutionPolicy/CoreConfig/SWReleasesToExcludeFromSWUpdatesImport/sRelease') with (guid uniqueidentifier) stateXml
inner join vItem release on release.Guid = stateXml.guid
inner join ResourceAssociation ra on ra.ParentResourceGuid = release.Guid
where ra.ResourceAssociationTypeGuid = '292dbd81-1526-423a-ae6d-f44eb46c5b16') as ex on sw.Guid = ex.Guid
where ex.guid is null and sw.ProductGuid='B1338338-5575-4A27-9808-23BEC40D79FA' and sw.Attributes=0
order by v.Name,sw.Name
exec sp_xml_removedocument @hdoc