Exporting usage data

This section provides instructions on how to export a high-level usage summary, including the following relevant metrics:

• Robot execution duration per month, by robot type.
• Robot concurrency, meaning the maximum number of robots that executed jobs at the same time.
• Metrics on jobs run.
• Job failure rate.
• Metrics on processes run.
• Metrics on queue items.
• Metrics on users.
• 環境情報。

The result of the data export is an output.xml file.

Note: You can upload the exported data file to the UiPath® Customer Portal, to help us achieve a better understanding of usage and provide you with better support.

To export usage data via either SQL Server Management Studio or the sqlcmd utility, using PowerShell, follow the instructions in the following sections.

Exporting usage data via SQL Server Management Studio

To export usage data via SQL Server Management Studio:

1. Copy the following SQL script into the editor:

    -- uncomment the below line if running from sqlcmd, :XML is directive for sqlcmd
   -- :XML ON
   DECLARE @monthsBack INT = 6
   IF OBJECT_ID('tempdb..#TenantsAndAccounts') IS NOT NULL DROP TABLE #TenantsAndAccounts;
   DECLARE @lastMigrationId NVARCHAR(50) = ''
   IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = '__MigrationHistory'))
   BEGIN
   SET @lastMigrationId = (SELECT
   		TOP 1 [MigrationId]
   	FROM [dbo].[__MigrationHistory]
   	ORDER BY MigrationId DESC)
   END
   IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = '__EFMigrationsHistory'))
   BEGIN
   	SET @lastMigrationId = (SELECT
   		TOP 1 [MigrationId]
   	FROM [dbo].__EFMigrationsHistory
   	ORDER BY MigrationId DESC)
   END
   SET NOCOUNT ON
   DECLARE @startTime DATETIME = DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, -@monthsBack, GETUTCDATE())), 0)
   SELECT [Id], [Key], [AccountId] INTO #TenantsAndAccounts FROM [Tenants] WITH(NOLOCK)
   ;WITH TransitionStatesCTE AS (
     SELECT je.[TenantId],
         je.[State],
         je.[CreationTime],
   	  j.[RuntimeType],
         LEAD(je.[CreationTime]) OVER (PARTITION BY je.[JobId] ORDER BY je.[CreationTime]) as NextCreationTime,
         LEAD(je.[State]) OVER (PARTITION BY je.[JobId] ORDER BY je.[CreationTime]) as NextAction
     FROM [dbo].[JobEvents] je WITH(NOLOCK) 
     JOIN [dbo].[Jobs] j WITH(NOLOCK) ON j.[Id] = je.[JobId]
     WHERE je.[CreationTime] >= @startTime
   ),
   MaxConcurrency AS (
   	SELECT 
   		je.[TenantId],
   		je.[CreationTime],
   		j.[RuntimeType],
   		je.State,
   		SUM(CASE WHEN je.[State] = 1 THEN 1 WHEN je.[State] IN (4, 5, 6, 7) AND je.[PreviousState] IN (1, 2, 3) THEN -1 ELSE 0 END) OVER ( PARTITION BY j.[RuntimeType], je.[TenantId] ORDER BY je.[CreationTime] ASC) AS RunningTotal
   	FROM JobEvents je WITH(NOLOCK)
   	JOIN [dbo].[Jobs] j WITH(NOLOCK) ON j.[Id] = je.[JobId]
   	WHERE j.[CreationTime] >= @startTime),
   CombinedResults AS (
       SELECT [Name], [Value]
       FROM [dbo].[Settings] WITH(NOLOCK)
       WHERE [Name] = 'Legacy.InstallationId' OR [Name] = 'InstallationId'
       UNION
       SELECT 'LastAppliedMigrationId' AS [Name], @lastMigrationId AS [Value]
   	UNION
   	SELECT 'ExportDate' AS [Name], CONVERT(NVARCHAR(50), GETUTCDATE(), 126) AS [Value]
   )
   SELECT (
   (SELECT
   	OrganizationKey,
   	TenantKey,
       [Date],
   	(
           SELECT RuntimeType
           FOR XML PATH(''), TYPE, ELEMENTS
       ) AS Custom,
   	RuntimeType AS DeDup,
       Name,
       Value
   	FROM (
   	SELECT
   		t.[Key] AS TenantKey,
   		t.[AccountId] AS OrganizationKey,
   		tcte.RuntimeType,
   		EOMONTH(tcte.[CreationTime]) AS [Date],
   		SUM(DATEDIFF(SECOND, tcte.[CreationTime], tcte.[NextCreationTime])) AS JobsExecutionDurationSeconds
   	FROM TransitionStatesCTE tcte
   	JOIN #TenantsAndAccounts t ON t.[Id] = tcte.[TenantId]
   	WHERE tcte.[State] IN (1, 2, 3) AND tcte.[NextAction] IN (2, 3, 4, 5, 6, 7)
   	GROUP BY EOMONTH(tcte.[CreationTime]), t.[Key], t.[AccountId], tcte.[RuntimeType], tcte.[TenantId]) AS SummaryData
   UNPIVOT (Value FOR Name IN (JobsExecutionDurationSeconds)) AS UnpivotedData
   FOR XML PATH('Tenant'), TYPE)),
   (SELECT
   	OrganizationKey,
   	TenantKey,
       [Date],
   	(
           SELECT RuntimeType
           FOR XML PATH(''), TYPE, ELEMENTS
       ) AS Custom,
   	RuntimeType AS DeDup,
       Name,
       Value
   	FROM (
   		SELECT
     			t.[Key] AS TenantKey,
   			t.[AccountId] AS OrganizationKey,
   			mc.[RuntimeType],
   			EOMONTH(mc.[CreationTime]) AS [Date],
   			MAX(mc.[RunningTotal]) AS JobsMaxConcurrency
   		FROM [MaxConcurrency] mc
   		JOIN #TenantsAndAccounts t ON t.[Id] = mc.[TenantId]
   		GROUP BY EOMONTH(mc.[CreationTime]), t.[Key], t.[AccountId], mc.[RuntimeType]) AS SummaryData
   UNPIVOT (Value FOR Name IN (JobsMaxConcurrency)) AS UnpivotedData
   FOR XML PATH('Tenant'), TYPE)
   ,
   (SELECT 
       OrganizationKey,
   	TenantKey,
       [Date],
       Name,
       Value
   	FROM (
   	SELECT
   		t.[Key] AS TenantKey,
   		t.[AccountId] AS OrganizationKey,
   		EOMONTH([CreationTime]) AS [Date],
   		COUNT(1) AS 'ProcessesCreatedCount'
   	FROM [dbo].[Releases] WITH(NOLOCK)
   	JOIN #TenantsAndAccounts t ON t.[Id] = [TenantId]
   	WHERE [IsDeleted] = 0
   	GROUP BY EOMONTH([CreationTime]), t.[Key], t.[AccountId]) AS SummaryData
   UNPIVOT (Value FOR Name IN (ProcessesCreatedCount)) AS UnpivotedData
   FOR XML PATH('Tenant'), TYPE),
   
   (SELECT 
       OrganizationKey,
   	TenantKey,
       [Date],
   	(
           SELECT RuntimeType
           FOR XML PATH(''), TYPE, ELEMENTS
       ) AS Custom,
   	RuntimeType AS DeDup,
       Name,
       Value
   	FROM (
   	SELECT 
   		t.[Key] AS TenantKey,
   		t.[AccountId] AS OrganizationKey,
   		EOMONTH([CreationTime]) AS [Date],
   		[RuntimeType],
   		CAST(COUNT(1) AS NVARCHAR(50)) AS 'JobsCreated',
   		CAST(SUM(CASE WHEN [State] = 5 THEN 1 ELSE 0 END) AS NVARCHAR(50)) AS 'JobsStateSuccessfulCount',
   		CAST(SUM(CASE WHEN [State] = 4 THEN 1 ELSE 0 END) AS NVARCHAR(50)) AS 'JobsStateFaultedCount',
   		CAST(SUM(CASE WHEN [ResumeVersion] <> NULL THEN 1 ELSE 0 END) AS NVARCHAR(50)) AS 'JobsStateWentThroughSuspendedCount',
   		CAST(SUM(CASE WHEN [Source] = 0 THEN 1 ELSE 0 END) AS NVARCHAR(50)) AS 'JobsSourceManualCount',
   		CAST(SUM(CASE WHEN [Source] = 1 THEN 1 ELSE 0 END) AS NVARCHAR(50)) AS 'JobsSourceScheduleCount',
   		CAST(SUM(CASE WHEN [Source] = 2 THEN 1 ELSE 0 END) AS NVARCHAR(50)) AS 'JobsSourceQueueCount',
   		CAST(SUM(CASE WHEN [Source] = 12 THEN 1 ELSE 0 END) AS NVARCHAR(50)) AS 'JobsSourceAssistantCount',
   		CAST(COUNT(DISTINCT(CASE WHEN [State] IN (4, 5) THEN RobotId END)) AS NVARCHAR(50)) AS 'JobsExecutionsDistinctRobotsCount',
   		CAST(MIN(CASE WHEN [State] IN (4,5) THEN [CreationTime] ELSE GETUTCDATE() END) AS NVARCHAR(50)) AS 'JobsCreatedMinimumCreationTime',
   		CAST(MAX(CASE WHEN [State] IN (4,5) THEN [CreationTime] ELSE GETUTCDATE() END) AS NVARCHAR(50)) AS 'JobsCreatedMaximumCreationTime'
   	FROM [dbo].[Jobs] WITH(NOLOCK)
   	JOIN #TenantsAndAccounts t ON t.[Id] = [TenantId]
   	WHERE [CreationTime] >= @startTime
   	GROUP BY EOMONTH([CreationTime]), t.[Key], t.[AccountId], RuntimeType) AS SummaryData
   UNPIVOT (Value FOR Name IN (JobsCreated, JobsStateSuccessfulCount, JobsStateFaultedCount, JobsStateWentThroughSuspendedCount, JobsSourceManualCount, JobsSourceScheduleCount, JobsSourceQueueCount, JobsSourceAssistantCount, JobsExecutionsDistinctRobotsCount, JobsCreatedMinimumCreationTime, JobsCreatedMaximumCreationTime)) AS UnpivotedData
   FOR XML PATH('Tenant'), TYPE),
   (SELECT 
       OrganizationKey,
   	TenantKey,
       [Date],
       Name,
       Value
   FROM 
       (
   	SELECT
   		t.[Key] AS TenantKey,
   		t.[AccountId] AS OrganizationKey,
   		EOMONTH([CreationTime]) AS [Date],
   		COUNT(1) AS 'QueueItemsCreated',
   		SUM(CASE WHEN [Status] = 0 THEN 1 ELSE 0 END) AS 'QueueItemsStateNewCount',
   		SUM(CASE WHEN [Status] = 2 THEN 1 ELSE 0 END) AS 'QueueItemsStateFailedCount',
   		SUM(CASE WHEN [Status] = 3 THEN 1 ELSE 0 END) AS 'QueueItemsStateSuccessfulCount',
   		SUM(CASE WHEN [Status] = 4 THEN 1 ELSE 0 END) AS 'QueueItemsStateAbandonedCount',
   		SUM(CASE WHEN [Status] = 5 THEN 1 ELSE 0 END) AS 'QueueItemsStateRetriedCount',
   		SUM(CASE WHEN [Status] = 6 THEN 1 ELSE 0 END) AS 'QueueItemsStateDeletedCount'
   	FROM [dbo].[QueueItems] WITH(NOLOCK)
   	JOIN #TenantsAndAccounts t ON t.[Id] = [TenantId]
   	WHERE [CreationTime] >= @startTime
   	GROUP BY EOMONTH([CreationTime]), t.[Key], t.[AccountId]) AS SummaryData
   UNPIVOT (Value FOR Name IN (QueueItemsCreated, QueueItemsStateNewCount, QueueItemsStateFailedCount, QueueItemsStateSuccessfulCount, QueueItemsStateAbandonedCount, QueueItemsStateRetriedCount, QueueItemsStateDeletedCount)) AS UnpivotedData
   FOR XML PATH('Tenant'), TYPE),
   (SELECT 
       OrganizationKey,
   	TenantKey,
       Name,
       Value
   FROM 
       (
   	SELECT
   		t.[Key] AS TenantKey,
   		t.[AccountId] AS OrganizationKey,
   		COUNT(1) AS 'UsersTotalCount',
   		SUM(CASE WHEN AllowedSessions & 2 = 2  THEN 1 ELSE 0 END) AS 'UsersAllowedSessionAttendedCount',
   		SUM(CASE WHEN AllowedSessions & 4 = 4  THEN 1 ELSE 0 END) AS 'UsersAllowedSessionUnattendedCount',
   		SUM(CASE WHEN LicenseType = 1  THEN 1 ELSE 0 END) AS 'UsersRobotsAttendedCount',
   		SUM(CASE WHEN LicenseType = 3  THEN 1 ELSE 0 END) AS 'UsersRobotsRpaDeveloperCount',
   		SUM(CASE WHEN LicenseType = 4  THEN 1 ELSE 0 END) AS 'UsersRobotsCitizenDeveloperCount',
   		SUM(CASE WHEN LicenseType = 6  THEN 1 ELSE 0 END) AS 'UsersRobotsRpaDeveloperProCount',
   		SUM(CASE WHEN [Type] = 0  THEN 1 ELSE 0 END) AS 'UsersTypeUserCount',
   		SUM(CASE WHEN [Type] = 1  THEN 1 ELSE 0 END) AS 'UsersTypeRobotCount',
   		SUM(CASE WHEN [Type] = 2  THEN 1 ELSE 0 END) AS 'UsersTypeDirectoryUserCount',
   		SUM(CASE WHEN [Type] = 3  THEN 1 ELSE 0 END) AS 'UsersTypeDirectoryGroupCount',
   		SUM(CASE WHEN [Type] = 4  THEN 1 ELSE 0 END) AS 'UsersTypeDirectoryRobotCount',
   		SUM(CASE WHEN [Type] = 5  THEN 1 ELSE 0 END) AS 'UsersTypeDirectoryExternalApplicationCount'
   	FROM [dbo].[Users] WITH(NOLOCK)
   	JOIN #TenantsAndAccounts t ON t.[Id] = [TenantId]
   	WHERE [IsDeleted] = 0
   	GROUP BY t.[Key], t.[AccountId]) AS SummaryData
   UNPIVOT (Value FOR Name IN (UsersTotalCount, UsersAllowedSessionAttendedCount, UsersAllowedSessionUnattendedCount, UsersRobotsAttendedCount, UsersRobotsRpaDeveloperCount, UsersRobotsCitizenDeveloperCount, UsersRobotsRpaDeveloperProCount, UsersTypeUserCount, UsersTypeRobotCount, UsersTypeDirectoryUserCount, UsersTypeDirectoryGroupCount, UsersTypeDirectoryRobotCount, UsersTypeDirectoryExternalApplicationCount)) AS UnpivotedData
   FOR XML PATH('Tenant'), TYPE),
   (SELECT * 
   FROM CombinedResults
   FOR XML PATH('Static'), TYPE),
   (SELECT 
       OrganizationKey,
   	TenantKey,
   	(
   		SELECT [DeletionTime], [CreationTime], [LicenseId], [LicenseHostLicenseId]
   		FOR XML PATH(''), TYPE, ELEMENTS
       ) AS Custom,
   	LicenseId AS DeDup,
       Name,
       Value
   FROM (
   	SELECT
   		t.[Key] AS TenantKey,
   		t.[AccountId] AS OrganizationKey,
   		l.[Id] AS 'LicenseId',
   		l.[EncryptedLicenseInfo] AS 'LicenseInfo',
   		l.[DeletionTime],
   		l.[CreationTime],
   		l.[HostLicenseId] AS 'LicenseHostLicenseId'
   	FROM [dbo].[Licenses] l WITH(NOLOCK)
   	JOIN #TenantsAndAccounts t ON t.[Id] = l.[TenantId]) AS SummaryData
   UNPIVOT (Value FOR Name IN (LicenseInfo)) AS UnpivotedData
   FOR XML PATH('Tenant'), TYPE),
   (SELECT 
       OrganizationKey,
   	TenantKey,
   	[Date],
   	(
   		SELECT [LicenseType]
   		FOR XML PATH(''), TYPE, ELEMENTS
       ) AS Custom,
       Name,
       Value
   FROM (
   	SELECT
   		t.[Key] AS TenantKey,
   		t.[AccountId] AS OrganizationKey,
   		cls.[Type] AS LicenseType,
   		SUM(cls.Used) AS UsedLicenses,
   		EOMONTH(cls.[Timestamp]) AS [Date]
   	FROM [dbo].[ConsumptionLicenseStats] cls WITH(NOLOCK)
   	JOIN #TenantsAndAccounts t ON t.[Id] = cls.[TenantId]
   	WHERE cls.[Timestamp] >= @startTime
   	GROUP BY EOMONTH(cls.[Timestamp]), [Type], t.[Key], t.[AccountId]) AS SummaryData
   UNPIVOT (Value FOR Name IN (UsedLicenses)) AS UnpivotedData
   FOR XML PATH('Tenant'), TYPE)
   FOR XML PATH('orchestrator')
   IF OBJECT_ID('tempdb..#TenantsAndAccounts') IS NOT NULL DROP TABLE #TenantsAndAccounts

2. Execute the SQL script.
3. From the contextual menu, select Results To, then Results to Grid. This option produces an XML file.
   Note: Selecting either the Results to Text option or the Results to File option truncates the exported data.
4. Open the XML file in SQL Server Management Studio, then save the file.
5. Optionally, upload the XML file to the UiPath® Customer Portal.

Exporting usage data via the sqlcmd utility

To export usage data via the sqlcmd utility:

1. Copy the following code into a text editor and save it as an SQL file named ExportScriptOrchMSI.sql:

    DECLARE @monthsBack INT = 6
   
   SET NOCOUNT ON
   DECLARE @startTime DATETIME = DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, -@monthsBack, GETUTCDATE())), 0)
   SELECT [Id], [Key], [AccountId] INTO #TenantsAndAccounts FROM [Tenants] WITH(NOLOCK)
   ;WITH TransitionStatesCTE AS (
     SELECT je.[TenantId],
         je.[State],
         je.[CreationTime],
   	  j.[RuntimeType],
         LEAD(je.[CreationTime]) OVER (PARTITION BY je.[JobId] ORDER BY je.[CreationTime]) as NextCreationTime,
         LEAD(je.[State]) OVER (PARTITION BY je.[JobId] ORDER BY je.[CreationTime]) as NextAction
     FROM [dbo].[JobEvents] je WITH(NOLOCK) 
     JOIN [dbo].[Jobs] j WITH(NOLOCK) ON j.[Id] = je.[JobId]
     WHERE je.[CreationTime] > @startTime
   )
   SELECT
   	tcte.[TenantId],
   	t.[Key] AS TenantKey,
   	t.[AccountId] AS OrganizationKey,
   	tcte.RuntimeType,
   	FORMAT(tcte.[CreationTime], 'MM.yyyy') AS [Date],
   	SUM(DATEDIFF(SECOND, tcte.[CreationTime], tcte.[NextCreationTime])) as ExecutionDurationSeconds,
   	'JobExecutionTime' AS DatasetName
   FROM TransitionStatesCTE tcte
   JOIN #TenantsAndAccounts t ON t.[Id] = tcte.[TenantId]
   WHERE tcte.[State] IN (1, 2, 3) AND tcte.[NextAction] IN (2, 3, 4, 5, 6, 7)
   GROUP BY FORMAT(tcte.[CreationTime], 'MM.yyyy'), t.[Key], t.[AccountId], tcte.[RuntimeType], tcte.[TenantId]
   
   SELECT
   	[TenantId],
   	t.[Key] AS TenantKey,
   	t.[AccountId] AS OrganizationKey,
   	FORMAT([CreationTime], 'MM.yyyy') AS [Date],
   	COUNT(1) AS 'Processes.Created.Count',
   	'NumberOfProcesses' AS DatasetName
   FROM [dbo].[Releases] WITH(NOLOCK)
   JOIN #TenantsAndAccounts t ON t.[Id] = [TenantId]
   WHERE [IsDeleted] = 0
   GROUP BY TenantId, FORMAT([CreationTime], 'MM.yyyy'), t.[Key], t.[AccountId]
   
   SELECT 
   	TenantId,
   	t.[Key] AS TenantKey,
   	t.[AccountId] AS OrganizationKey,
   	FORMAT([CreationTime], 'MM.yyyy') AS [Date],
   	[RuntimeType],
   	COUNT(1) AS 'Jobs.Created',
   	SUM(CASE WHEN [State] = 5 THEN 1 ELSE 0 END) AS 'Jobs.State.Successful.Count',
   	SUM(CASE WHEN [State] = 4 THEN 1 ELSE 0 END) AS 'Jobs.State.Faulted.Count',
   	SUM(CASE WHEN [ResumeVersion] <> NULL THEN 1 ELSE 0 END) AS 'Jobs.State.WentThroughSuspended.Count',
   	SUM(CASE WHEN [Source] = 0 THEN 1 ELSE 0 END) AS 'Jobs.Source.Manual.Count',
   	SUM(CASE WHEN [Source] = 1 THEN 1 ELSE 0 END) AS 'Jobs.Source.Schedule.Count',
   	SUM(CASE WHEN [Source] = 2 THEN 1 ELSE 0 END) AS 'Jobs.Source.Queue.Count',
   	SUM(CASE WHEN [Source] = 12 THEN 1 ELSE 0 END) AS 'Jobs.Source.Assistant.Count',
   	COUNT(DISTINCT(CASE WHEN [State] IN (4, 5) THEN RobotId END)) AS 'Jobs.Executions.DistinctRobots.Count',
   	MIN(CASE WHEN [State] IN (4,5) THEN [CreationTime] ELSE GETUTCDATE() END) AS 'Jobs.Created.MinimumCreationTime',
   	MAX(CASE WHEN [State] IN (4,5) THEN [CreationTime] ELSE GETUTCDATE() END) AS 'Jobs.Created.MaximumCreationTime',
   	'JobsMetrics' AS DatasetName
   FROM [dbo].[Jobs] WITH(NOLOCK)
   JOIN #TenantsAndAccounts t ON t.[Id] = [TenantId]
   WHERE [CreationTime] > @startTime
   GROUP BY FORMAT([CreationTime], 'MM.yyyy'), t.[Key], t.[AccountId], RuntimeType, [TenantId]
   
   SELECT
   	[TenantId],
   	t.[Key] AS TenantKey,
   	t.[AccountId] AS OrganizationKey,
   	FORMAT([CreationTime], 'MM.yyyy') AS [Date],
   	COUNT(1) AS 'QueueItems.Created',
   	SUM(CASE WHEN [Status] = 0 THEN 1 ELSE 0 END) AS 'QueueItems.State.New.Count',
   	SUM(CASE WHEN [Status] = 2 THEN 1 ELSE 0 END) AS 'QueueItems.State.Failed.Count',
   	SUM(CASE WHEN [Status] = 3 THEN 1 ELSE 0 END) AS 'QueueItems.State.Successful.Count',
   	SUM(CASE WHEN [Status] = 4 THEN 1 ELSE 0 END) AS 'QueueItems.State.Abandoned.Count',
   	SUM(CASE WHEN [Status] = 5 THEN 1 ELSE 0 END) AS 'QueueItems.State.Retried.Count',
   	SUM(CASE WHEN [Status] = 6 THEN 1 ELSE 0 END) AS 'QueueItems.State.Deleted.Count',
   	'QueueItemsMetrics' AS DatasetName
   FROM [dbo].[QueueItems] WITH(NOLOCK)
   JOIN #TenantsAndAccounts t ON t.[Id] = [TenantId]
   WHERE [CreationTime] > @startTime
   GROUP BY FORMAT([CreationTime], 'MM.yyyy'), [TenantId], t.[Key], t.[AccountId]
   
   SELECT
   	t.[Key] AS TenantKey,
   	t.[AccountId] AS OrganizationKey,
   	TenantId,
   	COUNT(1) AS 'Users.Total.Count',
   	SUM(CASE WHEN AllowedSessions & 2 = 2  THEN 1 ELSE 0 END) AS 'Users.AllowedSession.Attended.Count',
   	SUM(CASE WHEN AllowedSessions & 4 = 4  THEN 1 ELSE 0 END) AS 'Users.AllowedSession.Unattended.Count',
   	SUM(CASE WHEN LicenseType = 1  THEN 1 ELSE 0 END) AS 'Users.Robots.Attended.Count',
   	SUM(CASE WHEN LicenseType = 3  THEN 1 ELSE 0 END) AS 'Users.Robots.RpaDeveloper.Count',
   	SUM(CASE WHEN LicenseType = 4  THEN 1 ELSE 0 END) AS 'Users.Robots.CitizenDeveloper.Count',
   	SUM(CASE WHEN LicenseType = 6  THEN 1 ELSE 0 END) AS 'Users.Robots.RpaDeveloperPro.Count',
   	SUM(CASE WHEN [Type] = 0  THEN 1 ELSE 0 END) AS 'Users.Type.User.Count',
   	SUM(CASE WHEN [Type] = 1  THEN 1 ELSE 0 END) AS 'Users.Type.Robot.Count',
   	SUM(CASE WHEN [Type] = 2  THEN 1 ELSE 0 END) AS 'Users.Type.DirectoryUser.Count',
   	SUM(CASE WHEN [Type] = 3  THEN 1 ELSE 0 END) AS 'Users.Type.DirectoryGroup.Count',
   	SUM(CASE WHEN [Type] = 4  THEN 1 ELSE 0 END) AS 'Users.Type.DirectoryRobot.Count',
   	SUM(CASE WHEN [Type] = 5  THEN 1 ELSE 0 END) AS 'Users.Type.DirectoryExternalApplication.Count',
   	'UsersMetrics' AS DatasetName
   FROM [dbo].[Users] WITH(NOLOCK)
   JOIN #TenantsAndAccounts t ON t.[Id] = [TenantId]
   WHERE [IsDeleted] = 0
   GROUP BY [TenantId], t.[Key], t.[AccountId]
   
   DECLARE @lastMigrationId NVARCHAR(50) = ''
   IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = '__MigrationHistory'))
   BEGIN
   SET @lastMigrationId = (SELECT
   		TOP 1 [MigrationId]
   	FROM [dbo].[__MigrationHistory]
   	ORDER BY MigrationId DESC)
   END
   IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = '__EFMigrationsHistory'))
   BEGIN
   	SET @lastMigrationId = (SELECT
   		TOP 1 [MigrationId]
   	FROM [dbo].__EFMigrationsHistory
   	ORDER BY MigrationId DESC)
   END
   
   SELECT
   	[Name],
   	[Value],
   	'EnvironmentSettings' AS DatasetName
   FROM [dbo].[Settings] WITH(NOLOCK)
   WHERE [Name] = 'Legacy.InstallationId' OR [Name] = 'InstallationId'
   UNION
   SELECT
   	'LastAppliedMigrationId' AS [Name],
   	@lastMigrationId AS [Value],
   	'EnvironmentSettings' AS DatasetName
   
   SELECT
   	t.[Key] AS TenantKey,
   	t.[AccountId] AS OrganizationKey,
   	l.[TenantId],
   	l.[Id] AS 'License.Id',
   	l.[EncryptedLicenseInfo] AS 'License.Info',
   	l.[DeletionTime] AS 'License.DeletionTime',
   	l.[CreationTime] AS 'License.CreationTime',
   	l.[HostLicenseId] AS 'License.HostLicenseId',
   	'LicenseInfoDataSet' as DatasetName
   FROM [dbo].[Licenses] l
   LEFT JOIN #TenantsAndAccounts t ON t.[Id] = l.[TenantId]
   
   IF OBJECT_ID('tempdb..#TenantsAndAccounts') IS NOT NULL DROP TABLE #TenantsAndAccounts

2. Run the SQL script by executing the following command:

    sqlcmd -i ExportScriptOrchMSI.sql -S ‘<Server>' -U '<User>' -P '<Password>' -d '<DB>' -o '<output-file>' -y 0

3. To view the exported data file locally, run the following formatting command:

    (Get-Content output.xml) -join '' | Set-Content output.xml

4. Upload the XML file to the UiPath® Customer Portal.