Site servers are duplicated in the ResourceAssoication table

Products

• IT Management Suite

Issue/Introduction

The issues vary but all relate to site server problems.

NA

Cause

The cause is currently under investigation. 

Resolution

The following script can be run to determine if the issue exists.

SELECT DISTINCT c.Name, rt.Name, MAX (ra.CreatedDate) 'Most recent Created Date', MIN (ra.CreatedDate) 'Oldest Created Date'
FROM ResourceAssociation ra
JOIN vRM_SiteService_Item ss on ss.Guid = ra.ParentResourceGuid
JOIN ResourceType rt on rt.Guid = ss.ResourceTypeGuid
JOIN vRM_Computer_Item c on c.Guid = ra.ChildResourceGuid
JOIN
      (
            SELECT ra.ChildResourceGuid
            FROM ResourceAssociation ra
            WHERE ra.ResourceAssociationTypeGuid = '5F00E96B-93F3-41F0-94A7-7DBBB8AEF841'
            GROUP BY ra.ChildResourceGuid
            HAVING COUNT (ra.ChildResourceGuid) > 1
      )a ON a.ChildResourceGuid = ra.ChildResourceGuid
WHERE ra.ResourceAssociationTypeGuid = '5F00E96B-93F3-41F0-94A7-7DBBB8AEF841'
GROUP BY c.Name, rt.Name
HAVING COUNT (ra.ChildResourceGuid) > 1
ORDER BY 1,2

If the issue is present the script below can be run to resolve it. 

IMPORTANT NOTE: As a best practice please ensure that there is a backup of the database before running any script that will modify data in the database.

DELETE ra
FROM ResourceAssociation ra
JOIN
 (
     SELECT ra.ChildResourceGuid, MIN (ra.CreatedDate) 'Oldest Created Date'
     FROM ResourceAssociation ra
     join vRM_SiteService_Item ss on ss.Guid = ra.ParentResourceGuid
     WHERE ra.ResourceAssociationTypeGuid = '5F00E96B-93F3-41F0-94A7-7DBBB8AEF841'
     GROUP BY ra.ChildResourceGuid, ss.ResourceTypeGuid
     HAVING COUNT (ra.ChildResourceGuid) > 1
 ) a ON a.ChildResourceGuid = ra.ChildResourceGuid
  AND a.[Oldest Created Date] = ra.CreatedDate
WHERE ra.ResourceAssociationTypeGuid = '5F00E96B-93F3-41F0-94A7-7DBBB8AEF841'