Exportieren von Nutzungsdaten

Dieser Abschnitt enthält Anweisungen zum Exportieren einer Nutzungszusammenfassung auf hoher Ebene, einschließlich der folgenden relevanten Metriken:

• Roboterausführungsdauer pro Monat nach Robotertyp.
• Roboter-Gleichzeitigkeit, d. h. die maximale Anzahl von Robotern, die gleichzeitig Aufträge ausgeführt haben.
• Metriken für ausgeführte Aufträge.
• Auftragsfehlerquote.
• Metriken für ausgeführte Prozesse.
• Metriken für Warteschlangenelemente.
• Metriken für Benutzer.
• Umgebungsinformationen.

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

Hinweis: Sie können die exportierte Datendatei in das UiPath® Customer Portal hochladen, damit wir ein besseres Verständnis der Verwendung erreichen und Ihnen besseren Support bieten können.

Um Nutzungsdaten über SQL Server Management Studio oder das Dienstprogramm sqlcmd mit PowerShell zu exportieren, befolgen Sie die Anweisungen in den folgenden Abschnitten.

Exportieren von Nutzungsdaten über SQL Server Management Studio

So exportieren Sie Nutzungsdaten über SQL Server Management Studio:

1. Kopieren Sie das folgende SQL-Skript in den Editor:

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

2. Führen Sie das SQL-Skript aus.
3. Wählen Sie im Kontextmenü die Option Ergebnisse für und dann Ergebnisse im Raster aus. Diese Option erzeugt eine XML-Datei.
   Hinweis: Wenn Sie entweder die Option Ergebnisse zu Text oder die Option Ergebnisse in Datei auswählen, 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 optional die XML-Datei in das UiPath® Customer Portal hoch.

Exportieren von Nutzungsdaten über das Dienstprogramm sqlcmd

So exportieren Sie Nutzungsdaten über das Dienstprogramm sqlcmd :

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

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

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