使用状況データをエクスポートする

このセクションでは、使用状況の概要をエクスポートする方法について説明します。関連する以下のメトリックなどをエクスポートします。

• ロボットの月間実行時間 (ロボットのタイプ別)
• ロボットの同時実行数: ジョブを同時に実行したロボットの最大数。
• 実行したジョブに関するメトリック。
• ジョブの失敗率。
• 実行したプロセスに関するメトリック。
• キュー アイテムに関するメトリック。
• ユーザーに関するメトリック。
• 環境情報。

データ エクスポートの結果は output.xml ファイルに保存されます。

注:

エクスポートしたデータ ファイルを UiPath Customer Portal にアップロードすると、UiPath® が使用状況の把握やサポートの向上を図るうえで貴重な情報となります。

PowerShell を使用して SQL Server Management Studio または sqlcmd ユーティリティから使用状況データをエクスポートするには、以下のセクションに示す手順に従います。

SQL Server Management Studio を使用して使用状況データをエクスポートする

SQL Server Management Studio を使用して使用状況データをエクスポートするには以下の手順に従います。

1. 次の SQL スクリプトをエディターにコピーします。

   -- 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. この SQL スクリプトを実行します。
3. コンテキスト メニューから [Results To] を選択し、[Results to Grid] を選択します。このように指定すると XML ファイルが生成されます。
   注:

   [ Results to Text ] オプションまたは [ Results to File ] オプションを選択すると、エクスポートしたデータの一部が切り捨てられます。

4. この XML ファイルを SQL Server Management Studio で開き、そのまま保存します。
5. 必要に応じて、XML ファイルを UiPath® Customer Portal にアップロードします。

sqlcmd ユーティリティを使用して使用状況データをエクスポートする

sqlcmd ユーティリティを使用して使用状況データをエクスポートするには、次の手順に従います。

1. 次のコードをテキスト エディターにコピーし、ExportScriptOrchMSI.sql をファイル名として 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. 以下のコマンドでこの SQL スクリプトを実行します。

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

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

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

4. UiPath® Customer Portal に XML ファイルをアップロードします。