The 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.
Step 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.
