Unable to unsassign a group containing large number of nodes from an application template in SAM

The query used to retrieve group assignments for the template experienced a high request time, which is contributing to the issue. This may be due to the large servers assigned to the group.

• Since the group cannot be unassigned from the application template in the SolarWinds Platform Web Console due to timeout, Custom SQL script need to be used to remove the association.
• Open SSMS and connect to the SolarWinds Platform DB server.
• Note :  Always perform a full database backup before running DELETE operations.
• Right-click the SolarWinds Platform Database and select New Query. 
• Paste the queries below and execute them in order.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

           1.  Lookup IDs before execution

SELECT * FROM APM_ApplicationTemplate WHERE Name LIKE '%TEMPLATE_NAME%';  -- Replace TEMPLATE_NAME
SELECT * FROM Containers WHERE Name LIKE '%GROUP_NAME%';                  -- Replace GROUP_NAME
DECLARE @TemplateID INT = 8;  -- Template ID from APM_ApplicationTemplate
DECLARE @GroupID    INT = 6;  -- Group ID from Containers

            2. Verify Assignment

IF NOT EXISTS (
    SELECT 1 
    FROM APM_TemplateGroupAssignment 
    WHERE TemplateID = @TemplateID AND GroupID = @GroupID
)
BEGIN
    PRINT 'No template-group assignment found. Exiting.';
    RETURN;
END
PRINT 'Template-group assignment exists. Proceeding...';

            3. Get Nodes in Group

SELECT DISTINCT 
    CAST(SUBSTRING(Name, CHARINDEX('=', Name) + 1, LEN(Name)) AS INT) AS NodeID
INTO #GroupNodes
FROM ContainerMemberDefinitions
WHERE ContainerID = @GroupID
  AND Entity = 'Orion.Nodes';

PRINT 'Collected list of nodes from the group:';
SELECT NodeID FROM #GroupNodes;

            4. Identify Group-Created Applications

SELECT 
    a.ID AS ApplicationID, 
    a.NodeID, 
    n.Caption AS NodeName
INTO #CandidateApps
FROM APM_Application a
JOIN #GroupNodes g ON a.NodeID = g.NodeID
JOIN Nodes n       ON n.NodeID = a.NodeID
WHERE a.TemplateID = @TemplateID
  AND a.CreatedByGroup = 1;

PRINT 'Identified applications created by this group-template assignment.';

            5. Filter Out Nodes Assigned by Other Groups

SELECT 
    c.ApplicationID, 
    c.NodeID
INTO #FinalApps
FROM #CandidateApps c
WHERE NOT EXISTS (
    SELECT 1
    FROM APM_TemplateGroupAssignment tga
    JOIN ContainerMemberDefinitions cmd 
      ON cmd.ContainerID = tga.GroupID
    WHERE tga.TemplateID = @TemplateID
      AND tga.GroupID <> @GroupID
      AND CAST(SUBSTRING(cmd.Name, CHARINDEX('=', cmd.Name) + 1, LEN(cmd.Name)) AS INT) = c.NodeID
);

PRINT 'Filtered applications to those exclusively assigned by this group.';

            6. Review Before Deletion

PRINT 'Preview: Applications that will be deleted.';
SELECT 
    f.ApplicationID, 
    f.NodeID, 
    n.Caption AS NodeName
FROM #FinalApps f
JOIN Nodes n ON n.NodeID = f.NodeID;

           7.  Delete Applications

DELETE a
FROM APM_Application a
JOIN #FinalApps f ON a.ID = f.ApplicationID;

PRINT 'Applications deleted successfully.';

           8 . Clean Blacklist Entries

DELETE b
FROM APM_TemplateGroupAssignmentsBlacklist b
WHERE b.TemplateID = @TemplateID
  AND b.NodeID IN (SELECT NodeID FROM #GroupNodes)
  AND NOT EXISTS (
      SELECT 1
      FROM APM_TemplateGroupAssignment tga
      JOIN ContainerMemberDefinitions cmd 
        ON cmd.ContainerID = tga.GroupID
      WHERE tga.TemplateID = @TemplateID
        AND CAST(SUBSTRING(cmd.Name, CHARINDEX('=', cmd.Name) + 1, LEN(cmd.Name)) AS INT) = b.NodeID
  );

PRINT 'Blacklist cleaned for nodes no longer assigned to this template.';

           9. Remove Template Assignment

DELETE TGA
FROM APM_TemplateGroupAssignment TGA
JOIN Containers C ON TGA.GroupID = C.ContainerID
JOIN APM_ApplicationTemplate AT ON TGA.TemplateID = AT.ID
WHERE C.ContainerID = @GroupID
  AND AT.ID = @TemplateID;

PRINT 'Template-group assignment removed.';

           10.  Cleanup Temporary Tables

DROP TABLE IF EXISTS #GroupNodes;
DROP TABLE IF EXISTS #CandidateApps;
DROP TABLE IF EXISTS #FinalApps;

PRINT 'Unassign template from group completed successfully.';