Exportation des données d'utilisation

Cette section fournit des instructions sur la façon d'exporter un résumé d'utilisation de haut niveau, y compris les métriques pertinentes suivantes :

• Durée d'exécution du robot par mois, par type de robot.
• Concurrence des Robots (Robot Concurrence), impliquant le nombre maximal de Robots qui ont exécuté des tâches en même temps.
• Métriques d'exécution des tâches.
• Taux d’échec des tâches.
• Métriques des processus exécutés.
• Métriques sur les éléments de la file d'attente.
• Métriques sur les utilisateurs.
• Informations sur l'environnement.

Le résultat de l'exportation des données est un fichier output.xml .

Remarque : vous pouvez charger le fichier de données exporté sur le Customer Portal UiPath®, pour nous aider à mieux comprendre son utilisation et à vous fournir une meilleure assistance.

Pour exporter les données d'utilisation via SQL Server Management Studio ou l'utilitaire sqlcmd à l'aide de PowerShell, suivez les instructions dans les sections suivantes.

Exportation des données d'utilisation via SQL Server Management Studio

Pour exporter les données d'utilisation via SQL Server Management Studio :

1. Copiez le script SQL suivant dans l'éditeur :

   -- 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. Exécutez le script SQL.
3. Dans le menu contextuel, sélectionnez Résultats vers, puis Résultats vers Grille. Cette option produit un fichier XML.
   Remarque : la sélection de l'option Résultats vers le texte (Results to Text) ou de l'option Résultats vers le fichier (Results to File) tronque les données exportées.
4. Ouvrez le fichier XML dans SQL Server Management Studio, puis enregistrez le fichier.
5. Vous pouvez également charger le fichier XML sur le Customer Portal UiPath®.

Exportation des données d'utilisation via l'utilitaire sqlcmd

Pour exporter les données d'utilisation via l'utilitaire sqlcmd :

1. Copiez le code suivant dans un éditeur de texte et enregistrez-le en tant que fichier SQL nommé 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. Exécutez le script SQL en exécutant la commande suivante :

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

3. Pour afficher le fichier de données exporté localement, exécutez la commande de formatage suivante :

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

4. Chargez le fichier XML sur le Customer Portal UiPath®.