Exporter les données d'utilisation

Cette section fournit des instructions décrivant comment exporter un récapitulatif d'utilisation de haut niveau, y compris les mesures pertinentes suivantes :

• Durée d'exécution des robots par mois, par type de robot.
• Concurrence des robots, à savoir, le nombre maximal de robots qui ont exécuté des tâches en même temps.
• Mesures sur les exécutions de tâches.
• Taux d'échec des tâches.
• Mesures sur les exécutions de processus.
• Mesures sur les éléments de file d'attente.
• Mesures sur les utilisateurs.
• Informations sur l'environnement.

Les données sont exportées dans un fichier output.xml.

Remarque :

Vous pouvez télécharger le fichier des données exportées sur le UiPath® Customer Portal pour nous aider à mieux comprendre les schémas d'utilisation et à vous fournir une meilleure assistance.

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

Exporter les données d'utilisation par le biais de SQL Server Management Studio

Pour exporter les données d'utilisation par le biais de SQL Server Management Studio, procédez comme suit :

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

   -- uncomment the below line if running from sqlcmd, :XML is directive for sqlcmd
   -- :XML ON-- comment the below line if not 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 AND je.[Action] = 4) 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 (1, 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(r.[CreationTime]) AS [Date],
           COUNT(1) AS 'ProcessesCreatedCount'
       FROM [dbo].[Releases] r WITH(NOLOCK) 
       JOIN #TenantsAndAccounts t ON t.[Id] = r.[TenantId]
       WHERE r.[IsDeleted] = 0
       GROUP BY EOMONTH(r.[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',
           CAST(COUNT(DISTINCT [ReleaseId]) AS NVARCHAR(50)) AS 'JobsExecutingDistinctProcessesCount'
       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, JobsExecutingDistinctProcessesCount)) 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], [HostLicenseInfo]
           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',
           hl.[EncryptedLicenseInfo] AS 'HostLicenseInfo'
       FROM [dbo].[Licenses] l WITH(NOLOCK)
       JOIN #TenantsAndAccounts t ON t.[Id] = l.[TenantId]
       LEFT JOIN [dbo].[HostLicenses] hl ON hl.[Id] = l.[HostLicenseId]) 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 Results To (Envoyer les résultats à), puis Results to Grid (Afficher les résultats sous forme de grille). Cette option produit un fichier XML.
   Remarque :

   La sélection de l'option Results to Textou de l'option 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 télécharger le fichier XML sur le portail client UiPath®.

Exporter les données d'utilisation par le biais de l'utilitaire sqlcmd

Pour exporter des données d'utilisation par le biais de l'outil sqlcmd, procédez comme suit :

1. Copiez le code suivant dans un éditeur de texte et enregistrez-le sous forme de fichier SQL nommé ExportScriptOrchMSI.sql :

   -- comment the below line if not 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 AND je.[Action] = 4) 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 (1, 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(r.[CreationTime]) AS [Date],
           COUNT(1) AS 'ProcessesCreatedCount'
       FROM [dbo].[Releases] r WITH(NOLOCK) 
       JOIN #TenantsAndAccounts t ON t.[Id] = r.[TenantId]
       WHERE r.[IsDeleted] = 0
       GROUP BY EOMONTH(r.[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',
           CAST(COUNT(DISTINCT [ReleaseId]) AS NVARCHAR(50)) AS 'JobsExecutingDistinctProcessesCount'
       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, JobsExecutingDistinctProcessesCount)) 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], [HostLicenseInfo]
           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',
           hl.[EncryptedLicenseInfo] AS 'HostLicenseInfo'
       FROM [dbo].[Licenses] l WITH(NOLOCK)
       JOIN #TenantsAndAccounts t ON t.[Id] = l.[TenantId]
       LEFT JOIN [dbo].[HostLicenses] hl ON hl.[Id] = l.[HostLicenseId]) 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 à l'aide de 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. Téléchargez le fichier XML sur le UiPath® Customer Portal.