Computers in Multiple Software Update Plug-in Policies

Products

• Patch Management Solution

Issue/Introduction

If a computer exists in more than one policy it can cause odd and unexpected results.

Environment

ITMS 8.x

Resolution

The SQL query below can be used to verify that computers are only in one Software Update Plug-in Policy, and to used this copy and paste the followingquery into SQL Management Studio and execute it:

---------------------------------------- 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

NOTE: The SQL query above is also attached to this article.