Computers in Multiple Software Update Plug-in Policies

Products

• Patch Management Solution

Issue/Introduction

This SQL query can be used to verify that computers are only in one Software Update Plug-in Policy. If a computer exists in more than one policy it can cause unexpected results.

Environment

ITMS 8.x

Cause

N/A

Resolution

Copy and paste the following SQL query into SQL Management Studio and execute it.  The SQL is also attached to this article.

---------------------------------------- Summary SQL ----------------------------------------

if exists ((
		select COUNT (ic.Guid) 
		from ItemClass ic
		join ItemActive ia on ia.Guid = ic.Guid
			   join ItemAppliesTo iat on iat.ItemGuid = ia.Guid
		where ClassGuid = '5E5BDE22-C290-4A94-A36C-C5076DA6D565' 
		and ia.Enabled = 1
		having COUNT (ic.Guid) > 1))
	begin
		select  count (distinct rtcc.ResourceGuid) 'Count of computers in two or more Software Update Agent Configuration Policies'
		from Item i
			   join ItemClass ic on ic.Guid = i.Guid
			   join ItemActive ia on ia.Guid = i.Guid
			   join ItemAppliesTo iat on iat.ItemGuid = i.Guid
			   join ResourceTargetMembershipCache rtcc on rtcc.ResourceTargetGuid = iat.ResourceTargetGuid
		where ia.Enabled = 1
			   and ic.ClassGuid in ('5E5BDE22-C290-4A94-A36C-C5076DA6D565')--Altiris.PatchManagementCore.Policies.PatchAgentPolicy
		group by rtcc.ResourceTargetGuid
		having COUNT (distinct i.Guid) > 1
	end

---------------------------------------- Details SQL ---------------------------------------- 

if exists ((
		select COUNT (ic.Guid) 
		from ItemClass ic
		join ItemActive ia on ia.Guid = ic.Guid
			   join ItemAppliesTo iat on iat.ItemGuid = ia.Guid
		where ClassGuid = '5E5BDE22-C290-4A94-A36C-C5076DA6D565' 
		and ia.Enabled = 1
		having COUNT (ic.Guid) > 1))
	begin
		select distinct vc.Name as 'Computer Name', count (ia.Guid) 'Number of policies'
		from ItemActive ia 
			   join ItemAppliesTo iat on iat.ItemGuid = ia.Guid
			   join ResourceTargetMembershipCache rtcc on rtcc.ResourceTargetGuid = iat.ResourceTargetGuid
			   join vRM_Computer_Item vc on vc.Guid = rtcc.ResourceGuid
		where ia.Enabled = 1
			   and 
			   ia.Guid in (select Guid from ItemClass where ClassGuid = '5E5BDE22-C290-4A94-A36C-C5076DA6D565')--Altiris.PatchManagementCore.Policies.PatchAgentPolicy
		group by vc.Name
		having COUNT (distinct ia.Guid) > 1
	end