Automation Suite on OpenShift installation guide

• Use SQL Server Maintenance Solution
• Delete old data periodically
• Back up the database

Using SQL Server Maintenance Solution​

Backing up the database​

Deleting old data periodically​

Shared Suite Capabilities​

1. Create a new database called, for example, UiPathArchives:

   assignment

   create database UiPathArchives
   create database UiPathArchives
   

2. Create the following backup tables:

   1. ArchiveAuditEvent with the same structure as the AuditEvent table:
      assignment

      SELECT * INTO [UiPathArchives].[dbo].[ArchiveAuditEvent] from [AutomationSuite_Platform].[adt].[AuditEvent] where 1 = 2
      SELECT * INTO [UiPathArchives].[dbo].[ArchiveAuditEvent] from [AutomationSuite_Platform].[adt].[AuditEvent] where 1 = 2
      

3. Archive the data:

   1. Archive audit records

      assignment

      DECLARE @NumberOfDaysToKeep INT
      DECLARE @CurrentDate DATETIME
      
      -- Specify the number of days
      SET @NumberOfDaysToKeep = 60
      -- Archive the list of audit event records that you want to delete
      SET @CurrentDate = GetDate()
      BEGIN TRANSACTION
      INSERT INTO [UiPathArchives].[dbo].[ArchiveAuditEvent]
      SELECT
      [Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
      [UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
      FROM [adt].[AuditEvent]
      WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
      -- Delete the audit events
      DELETE FROM [adt].[AuditEvent]
      WHERE EXISTS (SELECT 1 FROM [UiPathArchives].[dbo].[ArchiveAuditEvent] WHERE Id = [adt].[AuditEvent].[Id])
      COMMIT TRANSACTION
      DECLARE @NumberOfDaysToKeep INT
      DECLARE @CurrentDate DATETIME
      
      -- Specify the number of days
      SET @NumberOfDaysToKeep = 60
      -- Archive the list of audit event records that you want to delete
      SET @CurrentDate = GetDate()
      BEGIN TRANSACTION
      INSERT INTO [UiPathArchives].[dbo].[ArchiveAuditEvent]
      SELECT
      [Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
      [UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
      FROM [adt].[AuditEvent]
      WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
      -- Delete the audit events
      DELETE FROM [adt].[AuditEvent]
      WHERE EXISTS (SELECT 1 FROM [UiPathArchives].[dbo].[ArchiveAuditEvent] WHERE Id = [adt].[AuditEvent].[Id])
      COMMIT TRANSACTION
      

      Old data is copied to these archives prior to being deleted when using the following query.

4. Delete data from the table.

   Important:

   Before running the following script, make sure to adapt them to your environment.

   1. Audit events
      assignment

      declare @NumberOfDaysToKeep int
      declare @CurrentDate datetime
      
      -- Specify the number of days
      SET @NumberOfDaysToKeep = 60
      -- Create temporary table with the list of audit event records that you want to delete
      SET @CurrentDate = GetDate()
      SELECT
      [Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
      [UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
      INTO #TempAuditRecordsToDelete
      FROM [adt].[AuditEvent]
      WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
      -- Review the audit event records to be deleted
      SELECT * FROM #TempAuditRecordsToDelete
      -- Delete the audit events
      BEGIN TRANSACTION
      DELETE FROM [adt].[AuditEvent]
      WHERE EXISTS (SELECT 1 FROM #TempAuditRecordsToDelete WHERE Id = [adt].[AuditEvent].[Id])
      DROP TABLE #TempAuditRecordsToDelete
      COMMIT TRANSACTION
      declare @NumberOfDaysToKeep int
      declare @CurrentDate datetime
      
      -- Specify the number of days
      SET @NumberOfDaysToKeep = 60
      -- Create temporary table with the list of audit event records that you want to delete
      SET @CurrentDate = GetDate()
      SELECT
      [Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
      [UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
      INTO #TempAuditRecordsToDelete
      FROM [adt].[AuditEvent]
      WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
      -- Review the audit event records to be deleted
      SELECT * FROM #TempAuditRecordsToDelete
      -- Delete the audit events
      BEGIN TRANSACTION
      DELETE FROM [adt].[AuditEvent]
      WHERE EXISTS (SELECT 1 FROM #TempAuditRecordsToDelete WHERE Id = [adt].[AuditEvent].[Id])
      DROP TABLE #TempAuditRecordsToDelete
      COMMIT TRANSACTION
      

Identity Server​

1. Create a new database called, for example, UiPathIdentityArchives:

   assignment

   create database UiPathIdentityArchives
   create database UiPathIdentityArchives
   

2. Create the following backup tables:

   1. ArchiveLoginAttempts with the same structure as the UserLoginAttempts table:

      assignment

      select * into [UiPathIdentityArchives].[dbo].[ArchiveUserLoginAttempts] from [AutomationSuite_Platform].[identity].[UserLoginAttempts] where 1=2
      select * into [UiPathIdentityArchives].[dbo].[ArchiveUserLoginAttempts] from [AutomationSuite_Platform].[identity].[UserLoginAttempts] where 1=2
      

      Old data is copied to these archives prior to being deleted when using the following query.

3. Delete data from the table.

   Important:

   Before running the following script, make sure to adapt them to your environment.

   1. User login attempts

      To delete login attempts older than 60 days, for example, use the following query. It can be executed manually or scheduled in an SQL Server Job.

      assignment

      declare @NumberOfDaysToKeep int
      set @NumberOfDaysToKeep = 60
      if OBJECT_ID('[UiPathIdentityArchives].[dbo].[UserLoginAttemps]') = NULL 
        begin select * into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] from [identity].UserLoginAttempts where 1=2 end
      begin transaction
        set identity_insert [UiPathIdentityArchives].[dbo].[UserLoginAttemps] on
        insert into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] ([Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName])
          select [Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName]
            from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
        delete from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
      commit transaction
      declare @NumberOfDaysToKeep int
      set @NumberOfDaysToKeep = 60
      if OBJECT_ID('[UiPathIdentityArchives].[dbo].[UserLoginAttemps]') = NULL 
        begin select * into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] from [identity].UserLoginAttempts where 1=2 end
      begin transaction
        set identity_insert [UiPathIdentityArchives].[dbo].[UserLoginAttemps] on
        insert into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] ([Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName])
          select [Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName]
            from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
        delete from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
      commit transaction
      

Orchestrator​

Automation Hub​

Process Mining​