Remappage des ID d’organisation

Pour migrer d’Insights autonome vers Automation Suite, vous devez procéder au remappage des ID d’organisation dans les tables suivantes :

(
	[Id] [int] NOT NULL,
	[Key] [nvarchar](128) NOT NULL,
	[Name] [nvarchar](128) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[OrganizationId] [nvarchar](128) NULL,
 CONSTRAINT [PK_dbo.Tenants] PRIMARY KEY CLUSTERED 

CREATE TABLE [insightsintegrations].[TenantServiceIntegrations](
	[Id] [uniqueidentifier] NOT NULL,
	[TenantId] [uniqueidentifier] NOT NULL,
	[OrganizationId] [uniqueidentifier] NOT NULL,
	[ServiceName] [int] NOT NULL,
	[Status] [int] NOT NULL,
	[CreationTime] [datetime2](7) NOT NULL,
	[LastUpdatetime] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_TenantServiceIntegrations] PRIMARY KEY CLUSTERED 
 
 CREATE TABLE [insightspermissions].[GroupRoles](
	[GroupId] [uniqueidentifier] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
	[OrganizationId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_GroupRoles] PRIMARY KEY CLUSTERED 
 
 CREATE TABLE [insightspermissions].[Groups](
	[Id] [uniqueidentifier] NOT NULL,
	[OrganizationId] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Email] [nvarchar](max) NULL,
 CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED 
 
 CREATE TABLE [insightspermissions].[Roles](
	[Id] [uniqueidentifier] NOT NULL,
	[TenantId] [uniqueidentifier] NULL,
	[OrganizationId] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Resource] [nvarchar](max) NULL,
	[IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED 
 
 CREATE TABLE [insightspermissions].[TenantGroups](
	[GroupId] [uniqueidentifier] NOT NULL,
	[TenantId] [uniqueidentifier] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[TimeProcessed] [datetime2](7) NULL,
	[LastUpdateTime] [datetime2](7) NOT NULL,
	[CreationTime] [datetime2](7) NOT NULL,
	[OrganizationId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_TenantGroup] PRIMARY KEY CLUSTERED 
 
 CREATE TABLE [insightspermissions].[Tenants](
	[Id] [uniqueidentifier] NOT NULL,
	[OrganizationId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Tenants] PRIMARY KEY CLUSTERED 
 
 CREATE TABLE [insightspermissions].[Users](
	[Id] [uniqueidentifier] NOT NULL,
	[OrganizationId] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Email] [nvarchar](max) NULL,
	[IsDeleted] [bit] NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
 
 CREATE TABLE [insightsprovisioning].[TenantInstances](
	[TenantId] [uniqueidentifier] NOT NULL,
	[OrganizationId] [uniqueidentifier] NOT NULL,
	[ServiceType] [nvarchar](max) NULL,
	[Status] [int] NOT NULL,
	[State] [int] NOT NULL,
	[CreationTime] [datetime2](7) NOT NULL,
	[LastUpdatetime] [datetime2](7) NOT NULL,
	[TenantName] [nvarchar](max) NULL,
 CONSTRAINT [PK_TenantInstances] PRIMARY KEY CLUSTERED 
 
 CREATE TABLE [read].[HASHKEYS](
	[hashkey] [bigint] NOT NULL,
	[tenantkey] [nvarchar](128) NOT NULL,
	[tenantname] [nvarchar](256) NULL,
	[organizationid] [nvarchar](128) NULL,
	[status] [int] NULL
)

[dbo].[Tenants]

[insightsintegrations].[TenantServiceIntegrations]

[insightspermissions].[GroupRoles]

[insightspermissions].[Groups]

[insightspermissions].[Roles]

[insightspermissions].[TenantGroups]

[insightspermissions].[Tenants]

[insightspermissions].[Users]

[insightsprovisioning].[TenantInstances]

[read].[HASHKEYS]

Astuce : voici un exemple permettant de mettre à jour les tables :

--- Script to replace MSI organization ID with AS organization ID.
--- Please update the following DECLARE statements with your corresponding MSI org ID and AS org ID before executing this script.

DECLARE @msi_organization_id nvarchar(500) = upper('687db434-ce5c-4058-bc0b-3ed3fa23882b');
DECLARE @as_organization_id nvarchar(500) = upper('AA259F1A-5828-4608-AF71-27177A7831B8');
DECLARE @msi_resource nvarchar(500) = lower('insights/687db434-ce5c-4058-bc0b-3ed3fa23882b/687db434-ce5c-4058-bc0b-3ed3fa23882b/*');
DECLARE @as_resource nvarchar(500) = lower('insights/aa259f1a-5828-4608-af71-27177a7831b8/687db434-ce5c-4058-bc0b-3ed3fa23882b/*');

SELECT * FROM [dbo].[Tenants];
UPDATE [dbo].[Tenants]
    SET OrganizationId = lower(@as_organization_id)
    WHERE OrganizationId = lower(@msi_organization_id);

SELECT * FROM [insightsintegrations].[TenantServiceIntegrations];
UPDATE [insightsintegrations].[TenantServiceIntegrations]
    SET OrganizationId = @as_organization_id
    WHERE OrganizationId = @msi_organization_id;

ALTER TABLE [insightspermissions].[GroupRoles] NOCHECK CONSTRAINT FK_GroupRoles_Groups_GroupId_OrganizationId;
SELECT * FROM [insightspermissions].[GroupRoles];
UPDATE [insightspermissions].[GroupRoles]
    SET OrganizationId =  @as_organization_id
    WHERE OrganizationId = @msi_organization_id;
ALTER TABLE [insightspermissions].[GroupRoles] CHECK CONSTRAINT FK_GroupRoles_Groups_GroupId_OrganizationId;

ALTER TABLE [insightspermissions].[TenantGroups] NOCHECK CONSTRAINT FK_TenantGroups_Groups_GroupId_OrganizationId;
SELECT * FROM [insightspermissions].[TenantGroups];
UPDATE [insightspermissions].[TenantGroups]
    SET OrganizationId = @as_organization_id
    WHERE OrganizationId = @msi_organization_id;
ALTER TABLE [insightspermissions].[TenantGroups] CHECK CONSTRAINT FK_TenantGroups_Groups_GroupId_OrganizationId;

SELECT * FROM [insightspermissions].[Groups];
UPDATE [insightspermissions].[Groups]
    SET OrganizationId =  @as_organization_id
    WHERE OrganizationId = @msi_organization_id;

SELECT * FROM [insightspermissions].[Roles];
UPDATE [insightspermissions].[Roles]
    SET OrganizationId = @as_organization_id
    WHERE OrganizationId = @msi_organization_id;
UPDATE [insightspermissions].[Roles]
    SET Resource = @as_resource
    WHERE Resource =  @msi_resource;

SELECT * FROM [insightspermissions].[Tenants];
UPDATE [insightspermissions].[Tenants]
    SET OrganizationId =  @as_organization_id
    WHERE OrganizationId = @msi_organization_id;

SELECT * FROM [insightspermissions].[Users];
UPDATE [insightspermissions].[Users]
    SET OrganizationId = @as_organization_id
    WHERE OrganizationId = @msi_organization_id;

SELECT * FROM [insightsprovisioning].[TenantInstances];
UPDATE [insightsprovisioning].[TenantInstances]
    SET OrganizationId =  @as_organization_id
    WHERE OrganizationId = @msi_organization_id;

SELECT * FROM [read].[HASHKEYS];
UPDATE [read].[HASHKEYS]
    SET OrganizationId = lower(@as_organization_id)
    WHERE OrganizationId = @msi_organization_id;