Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Patch Management Solution 7.1.2 "Software and Vendor" page queries

Created: 06 Jun 2013 • Updated: 06 Jun 2013 | 1 comment
Language Translations
SK's picture
+1 1 Vote
Login to vote

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

Comments 1 CommentJump to latest comment

HighTower's picture

This is fantastic!  The first and third queries do exactly what I want!

However, the second query is also returning every policy that we have created for software distribution.  I'm not sure what's going on there...

0
Login to vote