How To Manually Import Insights Licensing Data From Orchestrator To Insights Database For A Specific Tenant

How to manually import insights licensing data from orchestrator to insights database for a specific tenant?

Repopulating licensing data for a tenant is useful if the insights was enabled / disabled multiple times and misses some data with respect to licensing. This is applicable only for Insights version 2020.4.x.

Follow the below steps to import licensing data from Orchestrator to Insights database for a specific tenant.

  1. Remove existing licensing data for the tenant from Insights DB
  • Execute the following SQL query on the Insights DB for the tenant (put the correct tenant id):
DELETE FROM [{Replace with Insights DB name}].[dbo].[RobotLicenseLogs] WHERE TenantId = {replace with the tenant id}

  1. Get the IngestionMarker value
    • Execute the following SQL query to get the current IngestionMarker value and save the result, we’ll need it later: SELECT Value FROM [{Replace with Insights DB name}].[dbo]. [IngestionMarkers] WHERE IngestionEventType = 5

  1. Turn off the Orchestrator ingestion
    • In order to prevent Orchestrator from updating the licensing data while the manual import is running, execute the following query:

UPDATE [{Replace with Insights DB name}].[dbo].[IngestionMarkers] SET Value = 100000000000 WHERE IngestionEventType = 5

  1. Import the tenant data from Orchestrator DB to Insights DB
    • Run Sql Server Import and Export Data tool (dtswizard.exe)

  • Configure the Data Source.
  1. Data source - select Microsoft OLE DB Provider for SQL Server
  2. Server name - set to the server where Orchestrator DB is running
  3. Authentication - set the credentials for the Orchestrator DB server
  4. Database - select the Orchestrator DB name

  • Configure the Destination and specify Insights database
  1. Destination - select Microsoft OLE DB Provider for SQL Server
  2. Server name - set to the server where Insights DB is running
  3. Authentication - set the credentials for the Insights DB server
  4. Database - select the Insights DB name

  1. Select “Write a query to specify the data to transfer”

  1. Provide a Source Query changing TenantId value to the id of the tenant being migrated as well as IngestionMarker value (See Get the IngestionMarker value section):

SELECT

rl.[RobotId]

,rl.[StartDate]

,rl.[EndDate]

,rl.[RobotType]

,rl.[TenantId]

,rl.[Scope]

,rl.[Key]

,rl.[Slots]

,rl.[LicenseKey]

,rl.[Properties]

,r.[Name] AS RobotName

FROM

[dbo].[RobotLicenseLogs] rl

JOIN [dbo].[Robots] r ON rl.RobotId = r.Id

where rl.TenantId = {PUT TenantId VALUE HERE}

AND rl.id <= {PUT IngestionMarker VALUE HERE}

ORDER BY rl.[Id] ASC

  1. Select [dbo].[RobotLicenseLogs] as a destination table:

  1. Review Data Type Mapping - leave with defaults


  1. Save and Run Package - choose Run immediately

  1. Complete the Wizard - click Finish


  1. Make sure the execution was successful

  1. Turn on the Orchestrator ingestion
    • After successful import turn on the Orchestrator ingestion of licensing data, use the value saved earlier (See Get the IngestionMarker value section)

UPDATE [{Replace with Insights DB name}].[dbo].[IngestionMarkers] SET Value = {Replace with value from earlier} WHERE IngestionEventType = 5 .