Remapping the organization IDs

Remap organization IDs when migrating from standalone Insights to Automation Suite.

To migrate from standalone Insights to Automation Suite, you must remap the organization IDs in the following tables:

(
    [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]

Tip:

This is an example on how to update the 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;