Search

GlobalSCAPE Knowledge Base

Is there a change document for the ARM database schema?

Karla Marsh
EFT

THE INFORMATION IN THIS ARTICLE APPLIES TO:

  • EFT Server, version 6.0 and later

QUESTION

Is there a change document for the ARM database schema?

ANSWER

The EFT installer prompts you to upgrade the ARM schema when you upgrade EFT, if the schema has changed. Below is a list of changes from ARM schema version 0.0.0.0 to present, from newest to oldest. The database version number appears in the installer during upgrade. On the Confirm Database Upgrade page, if there are updates to the database, review the warning message, click I understand, then click Install. (If there are no changes to be made to the database, the check box will not appear.) If there are any errors to repair, you can repair them on the database now, and then click Reanalyze to retest. The wizard will upgrade the database (if needed) and the specified EFT components. On the final page of the wizard, select the check boxes, as needed, then click Finish.

Below that is a list of ARM tables (i.e., the schema). You can also view the database details using SQL Server Management Studio.

You might also want to review the information in the help for your version of EFT, such as "Upgrading the EFT Database" and "Upgrading a Large Database." The "Upgrading a Large Database" topic includes instructions for manually upgrading the database "out of band" instead of during installation.

ARM Schema Update Version  22.0.0.0 (EFT v8.0.6)

Added WorkspaceAction Index 

ARM Schema Update Version  21.0.0.0 (EFT v8.0.5)

This update added new columns to tables, as noted below under ARM tables.

.ARM Schema Update Version  20.0.0.0 (EFT v8.0.4)

Several tables were updated with new columns, as noted below.

ARM Schema Update Version 18.0.0.0  (EFT v8)

This update adds tables for EFT v8 features such as GDPR, privacy, Workspaces, and the Scan Data action in Event Rules.

ARM Schema Update Version 17.0.0.0  (EFT v7.4.11)

This update applies a variety of fixes, such as correcting the "Activity - All Transfers" report to not show SFTP transactions with the filename twice.

ARM Schema Update Version 15.0.0.0  (EFT v7.4.7)

This update adds Advanced Workflow Event Rules steps to the database for use by Insight.

ARM Schema Update Version 13.0.0.0  (EFT v7.4.6.43)

Allows Workspace path to be missing in "tbl_WorkspaceActions" table

ARM Schema Update Version 12.2.0.0  (EFT v7.4.6.2)

Fixed purge script

ARM Schema Update Version 12.1.0.0  (EFT v7.4.2)

This update corrects wrong variables types in "SP_INSERT_TBL_OUTLOOKREPORT" procedure for Oracle.

ARM Schema Update Version 11.1.0.0  (EFT v7.3.5)

This update extends the EventRules table EventName column to 250 characters (from 50 characters).

ARM Schema Update Version 11.0.0.0  (EFT v7.3.3)

This update adds support for Workspaces Outlook Add-in auditing and reporting.

  • New Table: tbl_OutlookReport
  • New Procedure: sp_Insert_tbl_OutlookReport
  • New Index: IX_tbl_OutlookReport_TransactionDate
  • New Foreign Key: FK_tbl_OutlookReport_ParentTransID

The following table was added: tbl_OutlookReport - Create Workspaces Outlook Add-in auditing table

The following procedure was added: sp_Insert_tbl_OutlookReport - Create or alter the procedure sp_Insert_tbl_OutlookReport

Refer to ARM tables below for details of the new table.

ARM Schema Update Version 10.0.0.0  (EFT v7.1)

This upgrade is needed to support Workspaces auditing;

  • Four tables have been added: tbl_WorkspaceActions, tbl_WorkspaceParticipants, lu_WorkspaceActions, lu_WorkspacesParticipantStates;

  • Two new stored procedures have been added: sp_Insert_tbl_WorkspaceActions and sp_Insert_tbl_WorkspacePart;

  • The width of the version column of tbl_Schema_Version has been increased to support the new, longer version number;

ARM Schema Update Version 9.2.0.0 (EFT v7.0.3)

  • Makes sure that the details column of the Event action table gets properly updated;

ARM Schema Update Version 9.0.0.0 to 9.1.0.0

Applies to: SQL Server, Oracle

Change Type: Stored Procedure Modification

Description: Added Event actions’ parameters that weren’t being audited by ARM.

ARM Schema Update Version 9.0.0.0 (EFT v7.0.3)

Applies to: SQL Server, Oracle

Change Type: Table Change, Stored Procedure Modification, Function Modification

Description: This upgrade is needed to support HA reporting functionality.

  • A NodeName column has been added to:

  • A NodeName input has been included in:

Additionally, to better record the beginning and ending of Event Rule actions, a Time_Stamp_End had been added to tbl_Actions, sp_Insert_tbl_Actions has been updated and a new stored procedure sp_Update_tbl_Actions_Finished has been added.

ARM Schema Update Version 8.0.0.0 (EFT v7.0)

Applies to: Oracle

Change Type: Index Creation

Description: Due to errors in earlier versions of the Oracle database creation scripts the following indexes may not have been created in Oracle databases:

  • IX_tbl_EventRules_EventGUID

  • IX_tbl_ProtocolCommands_Other

  • IX_tbl_ProtocolCommands_Command_IsInternal

This upgrade checks for the presence of these indexes and creates them, if necessary.

ARM Schema Update Version 7.0.0.0

Applies to: SQL Server, Oracle

Change Type: Function Modification, Stored Procedure Modification

Description: This upgrade recreates the f_TransferResult function and the sp_GetInboundTransfersInfo procedure to resolve an issue by which finished HTTP/S downloads were not appearing in the Status Viewer or reports.

ARM Schema Update Version 6.0.0.0

Applies to: None

Change Type: None

Description: This upgrade was replaced by a later upgrade and is left as a placeholder to maintain proper versioning. This upgrade has no effect on the database.

ARM Schema Update Version 5.0.0.0

Applies to: SQL Server, Oracle

Change Type: Function Modification, Stored Procedure Modification

Description: This upgrade recreates the f_TransferResult and f_CommandProtocolError functions and the sp_GetInboundTransfersInfo procedure to resolve an issue in which aborted transfers were not always appearing correctly in the Status Viewer.

ARM Schema Update Version 4.0.0.0

Applies to: SQL Server, Oracle

Change Type: Table Change

Description: This upgrade removes the unused tbl_ResultCodes table.

ARM Schema Update Version 3.0.0.0

Applies to: SQL Server only

Change Type: Stored Procedure Modification

Description: This upgrade recreates the sp_Insert_tbl_Groups stored procedure to resolve an issue in which the procedure failed to obtain the newly generated identity value after executing the sp_Insert_tbl_Authentications stored procedure.

ARM Schema Update Version 2.0.0.0

Applies to: SQL Server only

Change Type: User Account Modification

Description:

SQL Server databases created using the EFT version 6.3 database creation scripts contained a defect in which the EFT database user account was created with its default schema set to a non-existent schema. The schema had the same name as the username.

Later versions of the EFT database creation scripts set the database user account's default schema to 'dbo' which is more standard. Additionally, the user account was created as a 'db_owner' which results in the various database objects being created in the dbo schema anyway.

To resolve this inconsistency, this upgrade will determine if the database user account's default schema has the same name as the account and if so set the default schema to 'dbo'.

ARM Schema Update Version 1.0.0.0

Applies to: SQL Server, Oracle

Change Type: Multiple, see below for specific changes

Description: This upgrade modifies the tables to use the nchar/nvarchar data type to allow persistence of various languages within the database. This upgrade also resolves issues with databases created by earlier versions of EFT.

Converting existing data to the new data types should be considered a significant upgrade process. Please consult the EFT help topics Upgrading the EFT Database and Upgrading Large Databases for additional information.

Note that for the most part the upgrade script may be re-executed multiple times in the case that an error must be resolved by manual intervention.

SQL Server Upgrade

  1. When upgrading databases earlier than EFT 6.4, the upgrade process will increase the size of the following columns to support storage of IPv6 addresses:
  2. The upgrade will then proceed with changing all char and varchar columns to nchar and nvarchar. Be aware that this process drops the majority of the objects (other than the tables) prior to converting the data types and then recreates them afterwards. The upgrade uses the following process to migrate to the nchar/nvarchar data type:
  3. Finally, the upgrade process will create a new View called vw_ProtocolCommands.

Oracle Upgrade

  1. EFT now includes a View in the database. Originally the database account created for use by EFT did not include the CREATE VIEW privilege. This privilege must be granted to the account prior to upgrading to this version of the database. This upgrade will attempt to create a test view to ensure the account has the privilege before proceeding further into the upgrade process.
  2. When upgrading databases earlier than EFT 6.4 the upgrade process will increase the size of the following columns to support storage of IPv6 addresses:
  3. As of EFT 6.5 the original TBL_SCHEMAVERSION table has been deprecated and is dropped by the upgrade process if present.
  4. In EFT 6.3 the SQL scripts had an issue where the TBL_PCIVIOLATIONS was defined twice. Once with the ID column called PCIVIOLATIONID and later with it called PCIVIOLATIONSID. Unfortunately this led to some issues down the road. To be consistent with the SQL Server table definitions we ultimately need to ensure the column name is PCIVIOLATIONID. If the incorrect column name is detected then it is renamed. Note that the trigger used to insert the sequence number is dropped and recreated later as part of the Unicode upgrade process. Additionally, the table may have been created without the PK_TBL_PCIVIOLATIONS primary key. If this is detected then the primary key is created.
  5. Earlier scripts created a table called TBL_ADMINCOMMANDS. This table is not used by EFT; if detected it is dropped, as is the associated sequence TBL_ADMINCOMMANDS_SEQ.
  6. The upgrade will then proceed with changing all char and varchar columns to nchar and nvarchar. Be aware that this process drops the majority of the objects (other than the tables) prior to converting the data types and then recreates them afterwards. The upgrade uses the following process to migrate to the nchar/nvarchar data type:
  7. Finally, the upgrade process will create a new View called vw_ProtocolCommands

ARM Tables

The tables created during installation are listed below. Because all of the EFT modules and features are available during the trial, all of the tables below are created, even if you do not activate that module/feature.

If you have installed Microsoft SQL Server Management Studio, you can view the tables in the Object Explorer. Right-click on a table, then click Select Top 1000 Rows. The SQL Query appears in the right pane. ("lu" is a lookup table.)

The tables are listed below in alphabetical order. (In SQL Server Management Studio, the lookup (lu) tables are listed first.)

lu_ActionResultID - Logs the result ID for the Action.

  • 0 = Success
  • 1 = Fail

tbl_Actions - Logs Actions performed when Event Rules are processed.

  • Time_stamp_end added in EFT v7.0
  • ActionGUID added in EFT v7.4.11

tbl_AdminActions - Logs Actions performed by administrators in EFT.

  • In v8.0.5, database version 21.0.0.0, StateBefore andStateAfter were added.

tbl_AS2Actions - Contains information about separate Actions for each AS2 transaction:

lu_AS2ActionStatus - Indicates whether file/MDN was successfully sent/received or action failed. (moved to AS2Actions table in EFT v8)

  • 1 = Success: File was received.sent, MDN received/sent;
  • 2 = Failure: Action failed to send/receive file or MDN

lu_AS2ActionType - Contains information about separate Actions for each AS2 transaction. (moved to AS2Actions table in EFT v8)

  • 0 = Inbound Transaction
  • 1 = Outbound Transaction
  • 2 = Receive File
  • 3 = Send File
  • 4 = Send MDN
  • 5 = Receive MDN
  • 6 = MDN Verification

lu_AS2ContentType - Contains information about separate Actions for each AS2 transaction. (moved to AS2Transactions table in EFT v8)

  • 0 = Application
  • 1 = EDIFACT
  • 2 = XML
  • 3 = MutuallyDefinedEDI
  • 4 = Binary
  • 5 = Plaintext

lu_AS2Direction - Contains information about separate Actions for each AS2 transaction. (moved to AS2Transactions table in EFT v8)

  • 0 = Inbound
  • 1 = Outbound

lu_AS2MDNType - Contains information about separate Actions for each AS2 transaction. (moved to AS2Transactions table in EFT v8)

  • 0 = NoMDN
  • 1 = Sync
  • 2 = Async HTTP
  • 3 = Async SMTP

lu_AS2TransactionStatus - Contains information about separate Actions for each AS2 transaction. (moved to AS2Transactions table in EFT v8)

  • 0 = In Progress
  • 1 = Success
  • 2 = Failed
  • 3 = Failed
  • 4 = Failed

 tbl_AS2Transactions - Contains details of AS2 Transactions:

  • NodeName added in EFT v7.0
  • FileName, ContentType, LocalPath, and RemotePath added in EFT v8.0.5, database version 21.0.0.0

tbl_Authentications - Logs authentication attempts for administrators and users per Site

tbl_AuthenticationsExpired - Logs authentication attempts for administrators and users per Site

Reason:
  • WTC idle timeout
  • EFT removed
    • Site stop
    • Kick users connected via FTP/SFTP
    • When closing timed out FTP connections
    • Deletion of expired sessions for HTTP (login to WTC, close tab, wait 5 minutes, login to WTC using another web browser)
  • User Logoff

lu_AuthenticationsExpired - Logs reason code for why authentication expired.

  • 0 = ExpiredNaturally
  • 1 = User logged off

lu_AuthenticationResultID - Logs reason code for why user session ended (timed out or logged out). (moved to Authentications table in EFT v8)

  •  0 = Success
  •  1 = Password not accepted
  •  2 = Account disabled
  •  3 = Too many connections per site
  •  4 = Too many connections per user
  •  5 = Too many connections per IP
  •  6 = Protocol is not supported
  •  7 = Restricted IP
  •  8 = Service is unavailable
  •  9 = Account is locked
  •  10 = Access challenge

tbl_AWEStepsErrorCode - Logs Errors presented in AWE Workflows. (moved to AWESteps table in EFT v8)

  • 0 = Success
  • 1 = Fail
  • 2 = Interrupted
  • 3 = Unexpected error
  • 4 = Timeout

 tbl_AWESteps

tbl_ClientOperations - Logs upload/download/create/etc Actions performed by EFT Event Rules when acting as a client

tbl_CustomCommands - Logs details of custom commands being executed, typically launched by Event Rules

lu_CustomCommandResultID - Logs result IDs of custom commands being executed, typically launched by Event Rules (moved to CustomCommands table in EFT v8)

  • 0 = Success
  • 1 = Success
  • 2 = Access denied
  • 3 = Command not found
  • 4 = Process failed
  • 5 = Command is disabled
  • 6 = Invalid parameters

tbl_EventRules - Logs details of Event Rules that have been processed

  • EventName extended to 250 characters, from 50 characters in v7.3.5 and later

tbl_EventRuleTransfers - Logs Event Rule Transfer failures, if configured in the administration interface, on the Server > Logs tab > Diagnostic Logging Settings > Configure.

  • ActionGUID added in EFT v8.0.5, database version 21.0.0.0

tbl_Groups - Provides plain text group name for reporting (part of db normalization)

tbl_NegotiatedCiphersSSH - Lists the ciphers used in client connection.

tbl_NegotiatedCiphersSSL - Lists the ciphers used in client connection.

tbl_OutlookReport - Provides information for creating Outlook reports(added in EFT v7.3.3)

tbl_PCIViolations - Logs PCI violations for PCI DSS compliance testing reports

lu_PCIViolationID - Logs PCI violation ID for PCI DSS compliance testing reports (moved to PCIViolations in EFT v8)

tbl_PersonalDataActions - Provides information about changes to personal data, to include node, site, template, username, remote IP, field name, before and after values, data set, and changed by whom.

tbl_PrivacyRightExercised - Provides information about privacy rights exercised, to include time, node, site, template, username, remote IP, and right exercised. (Grant, Rescind, Access, Rectify, Forget, Restrict, Port, Object)

tbl_PrivacyTermsEuStatus - Provides information when a change occurs in a user's privacy status (Privacy Policy, Terms of Service, and EU status), to include node, site, template, username, remote IP, field name, date set, and by whom.

tbl_ProtocolCommands - Logs detailed client commands sent for various protocols (ftp, http, etc)

  • Actor added in v8.0.5, database version 21.0.0.0

lu_ProtCommandInternalType - Logs detailed client commands sent for various protocols (ftp, http, etc) (moved to ProtocolCommands table in EFT v8)

  •  0 = External
  •  1 = Internal
  •  2 = REST workspaces
  •  3 = REST admin
  •  4 = REST remote agent manager
  •  5 = REST user
  • 22 = REST workspaces internal

tbl_Report_Exec_Summ (added in EFT v8.0.4)

tbl_Report_Traffic1 (added in EFT v8.0.4)

tbl_Report_Traffic2  (added in EFT v8.0.4)

tbl_Report_Traffic3  (added in EFT v8.0.4)

tbl_Report_Traffic4  (added in EFT v8.0.4)

tbl_SAT_Emails - Logs the notification e-mails sent by the SAT module (Support for Secure Ad Hoc Transfer module removed August 2014)

tbl_SAT_Files - Logs the files uploaded by the SAT module  (Support for Secure Ad Hoc Transfer module removed August 2014)

tbl_SAT_Transactions - Audits transactions managed by the Secure Ad Hoc Transfer (SAT) module.  (Support for Secure Ad Hoc Transfer module removed August 2014)

  • NodeName added in EFT v7.0

tbl_ScanDataActions - Provides information about scan data actions, including meta data, HTTP response, ICAP response, ICAP response headers, and Xheaders.

tbl_Schema_Version - Maintains the current version of the ARM schema

  • Version changed from [nchar](7) to [nchar](20) in EFT v7.1

tbl_ServerInternalEvents

tbl_SocketConnections - Logs details (IP addresses, port numbers, etc) of individual socket connections for various protocols (FTP, HTTP, etc).

lu_SocketConnectionsResultID - Logs details (IP addresses, port numbers, etc) of individual socket connections for various protocols (FTP, HTTP, etc). (moved to SocketConnections table in EFT v8)

  • 0 = Success
  • 1 = Too many connections per site
  • 2 = Too many connections per IP
  • 3 = Restricted IP
  • 4 = Banned IP

tbl_Transactions - Provides transaction references to correlate data from various ARM tables

  • NodeName added in EFT v7.0
  • time_stamp added in EFT v8.0.5, database version 21.0.0.0

tbl_WorkspaceActions – Logs actions performed to Workspaces (added in EFT v7.1)

lu_WorkspaceActions – Lookup table for workspace action values (added in EFT v7.1)

  • 0 = Unknown
  • 1 = Create
  • 2 = Update
  • 3 = Invite
  • 4 = Remove
  • 5 = Delete

tbl_WorkspaceParticipants – Logs details about Workspace participants (added in EFT v7.1)

lu_WorkspacesParticipantStates – Lookup table for workspace participant states (added in EFT v7.1)

  • 0 = Unknown
  • 1 = Invited
  • 2 = Registered
  • 3 = Joined
  • 4 = Removed
Details
Last Modified: 4 Months Ago
Last Modified By: kmarsh
Type: FAQ
Rated 1 star based on 7 votes.
Article has been viewed 78K times.
Options
Also In This Category
Tags