000031541 - How to automatically assign machine groups in RSA ECAT 4.x

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000031541
Applies ToRSA Product Set: ECAT
RSA Version/Condition: 4.x
TasksThe purpose of this article is to assign a machine group based on a SQL Query.
The following steps could potentially be placed in an automated task to assign new machines daily.
ResolutionStep 1: Create the desired groups in ECAT UI.
Step 2: Build a query to retrieve a group number. This can only be done one group at the time.
Example with a group named Workstations:
SELECT TOP (1) @fk_machinegroup = PK_MachineGroups  
FROM [dbo].[MachineGroups] AS [mg] WITH (NOLOCK) 
WHERE [mg].[Name] LIKE 'workstations'                 -- YOUR GROUP NAME

Step 3: Build a query to retrieve only the machines which you wish to change.
Example with MachineOU:
SELECT TOP 10 *  
FROM [dbo].[Machines] AS [m] 
INNER JOIN [dbo].[MachineOU] AS [mo] WITH (NOLOCK) ON [m].[FK_MachineOU] = [mo].[Pk_MachineOU] 
WHERE 
  [m].[FK_MachineGroups] = 1 AND         -- ONLY CHANGE IF DEFAULT 
  [mo].[MachineOU] LIKE '%Workstations%'

Example with IP:
SELECT TOP 10 *  
FROM [dbo].[Machines] AS [m] 
WHERE 
   [m].[FK_MachineGroups] = 1 AND         -- ONLY CHANGE IF DEFAULT 
   [m].[LocalIp] LIKE '10.10.10.%'        -- YOUR CONDITION HERE

Step 4: Modify and run the attached script.
BEGIN TRANSACTION  
DECLARE @fk_machinegroup INT; 
 
--  S T E P   2  --------------------------------------------------- 
SELECT TOP (1) @fk_machinegroup = PK_MachineGroups 
FROM [dbo].[MachineGroups] AS [mg] WITH (NOLOCK) 
WHERE [mg].[Name] LIKE 'workstations'                 -- YOUR GROUP NAME 
 
--  S T E P   3  --------------------------------------------------- 
UPDATE [m] 
SET FK_MachineGroups = @fk_machinegroup 
FROM [dbo].[Machines] AS [m] 
WHERE 
   [m].[FK_MachineGroups] = 1 AND         -- ONLY CHANGE IF DEFAULT 
   [m].[LocalIp] LIKE '10.10.10.%'        -- YOUR CONDITION HERE 
 
--  S T E P   4  --------------------------------------------------- 
-- COMMIT TRANSACTION            -- UNCOMMENT WHEN READY TO APPLY 
ROLLBACK TRANSACTION             -- TEST, COMMENT WHEN READY TO APPLY

If you are unsure of any of the steps above or experience any issues, contact RSA Support and quote this article number for further assistance.

Attachments

    Outcomes