Exportieren von Nutzungsdaten

In diesem Abschnitt finden Sie Anweisungen dazu, wie Sie eine Zusammenfassung mit den wichtigsten Kennzahlen zur Nutzung exportieren können, einschließlich:

• Ausführungsdauer von Robotern pro Monat nach Robotertyp.
• Roboterkonkurrenz, d. h. die maximale Anzahl an Robotern, die Aufträge gleichzeitig ausgeführt haben.
• Metriken zu ausgeführten Aufträgen.
• Fehlschlagsquote von Aufträgen.
• Metriken zu ausgeführten Prozessen.
• Metriken zu Warteschlangenelementen.
• Metriken zu Benutzern.
• Umgebungsinformationen.

Das Ergebnis des Datenexports ist eine output.xml-Datei.

Hinweis: Sie können die exportierte Datendatei in das UiPath® Kundenportal hochladen, um uns zu helfen, die Nutzung besser nachvollziehen und Ihnen einen besseren Support anbieten zu können.

Befolgen Sie die Anweisungen in den folgenden Abschnitten, um Nutzungsdaten entweder über SQL Server Management Studio oder das sqlcmd Dienstprogramm mithilfe von PowerShell zu exportieren.

Exportieren von Nutzungsdaten über SQL Server Management Studio

So exportieren Sie Nutzungsdaten über SQL Server Management Studio:

1. Kopieren Sie folgendes SQL-Skript in den Editor:

   -- 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. Führen Sie das SQL-Skript aus.
3. Wählen Sie im Kontextmenü die Option Ergebnisse an und dann Ergebnisse an Tabelle.Über diese Option wird eine XML-Datei erstellt.
   Hinweis: Bei Auswahl entweder der Option Ergebnisse im Text oder der Option Ergebnisse in Datei werden die exportierten Daten abgeschnitten.
4. Öffnen Sie die XML-Datei in SQL Server Management Studio und speichern Sie dann die Datei.
5. Laden Sie bei Bedarf die XML-Datei in das UiPath® Kundenportal hoch.

Exportieren von Nutzungsdaten über das Dienstprogramm sqlcmd

So exportieren Sie Nutzungsdaten über das Dienstprogramm sqlcmd:

1. Kopieren Sie folgenden Code in einen Texteditor und speichern Sie ihn als SQL-Datei mit dem Namen 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. Führen Sie das SQL-Skript aus, indem Sie folgenden Befehl ausführen:

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

3. Um die exportierte Datendatei lokal zu sehen, führen Sie folgenden Formatierungsbefehl aus:

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

4. Laden Sie die XML-Datei in das UiPath® Customer Portal hoch .