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® Customer Portal 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.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. Laden Sie die XML-Datei in das UiPath® Kundenportal hoch.