截屏视频帮助
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Patch Management Solution 7.1.2 "Software and Vendor" page queries

创建时间: 06 6 月 2013 • Updated: 06 6 月 2013 | 1 条评论
SK 的图片
+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

评论 1 Comment跳转至最新评论

HighTower 的图片

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