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.
- Create_1_tables.sql has been updated:
- RemoteFileName
- LocalFileName
- ActionID
- ActionGUID
- SourceDirectory
- SourceFileName
- DestinationDirectory
- DestinationFileName
- tbl_Actions has been updated to increase from 512 to 1024 the character limit for:
- SourceDirectory
- SourceFileName
- TargetDirectory
- TargetFileName
- SP_INSERT_TBL_ACTIONS has been updated to increase from 512 to 1024 the character limit for:
- pSourceDirectory
- pSourceFileName
- pTargetDirectory
- pTargetFileName
- sp_Update_tbl_Actions_Finished has been updated to increase from 512 to 1024 the character limit for:
- pSourceDirectory
- pSourceFileName
- pTargetDirectory
- pTargetFileName
- Fixed an issue where Oracle stored procedures would not execute properly (create_5_sprocs.sql)
- updates to the clearing the temp table and parent transactions
This update applies a variety of fixes, no new tables.
Time_stamp and Timestamp in several tables changed from "Timestamp (datetime, null)" to "Timestamp (datetime2(7), not null)"
Added WorkspaceAction Index
This update added new columns to tables, as noted below under ARM tables.
Several tables were updated with new columns, as noted below.
This update adds tables for EFT v8 features such as GDPR, privacy, Workspaces, and the Scan Data action in Event Rules.
This update applies a variety of fixes, such as correcting the "Activity - All Transfers" report to not show SFTP transactions with the filename twice.
This update adds Advanced Workflow Event Rules steps to the database for use by Insight.
Allows Workspace path to be missing in "tbl_WorkspaceActions" table
Fixed purge script
This update corrects wrong variables types in "SP_INSERT_TBL_OUTLOOKREPORT" procedure for Oracle.
This update extends the EventRules table EventName column to 250 characters (from 50 characters).
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.
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;
Applies to: SQL Server, Oracle
Change Type: Stored Procedure Modification
Description: Added Event actions’ parameters that weren’t being audited by ARM.
Applies to: SQL Server, Oracle
Change Type: Table Change, Stored Procedure Modification, Function Modification
Description: This upgrade is needed to support HA reporting functionality.
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.
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.
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.
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.
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.
Applies to: SQL Server, Oracle
Change Type: Table Change
Description: This upgrade removes the unused tbl_ResultCodes table.
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.
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'.
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
- 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:
- 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:
- Finally, the upgrade process will create a new View called vw_ProtocolCommands.
Oracle Upgrade
- 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.
- 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:
- As of EFT 6.5 the original TBL_SCHEMAVERSION table has been
deprecated and is dropped by the upgrade process if present.
- 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.
- 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.
- 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:
- 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.
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)
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