Copyright © TIBCO Software Inc. All Rights Reserved

Audit Schema Details

Tables that are part of the audit schema are depicted in Figure 9.

Figure 9 Audit_RelationShip

For the schema above, the values are as shown in Table 14.

Table 14 Audit_RelationShip Values

Name	Value
Name	Audit_RelationShip
Data Model	Physical

Table summaries for the audit schema are explained in Table 15.

Table 15 Audit Schema Tables Summary (Sheet 1 of 8)

Name	Documentation
BC_TRANSACTIONS	This is the main table that holds all the summary rows for auditing. An audit trail happens when: • TIBCO BusinessConnect sends or receives messages from a trading partner. • TIBCO BusinessConnect Gateway engine bootstraps and starts up the event sources, as well as when BusinessConnect PartnerExpress or BusinessConnect FTP Server users log in. • TIBCO BusinessConnect PartnerExpress or TIBCO BusinessConnect FTP Server users, or other business protocol partners, transfer messages or receive messages to TIBCO BusinessConnect. The BC_TRANS_ID column is a GUID generated by TIBCO BusinessConnect that holds the latest transaction STATUS from the partner or from the messages sent from the private process. The STARTDATE column is stamped when the first entry is inserted, and the TS column is updated every time a child row is inserted in the BC_MESSAGES table. Depending on the business protocol type (such as TIBCO BusinessConnect EDI Protocol powered by Instream, TIBCO BusinessConnect Services Plug-in, TIBCO BusinessConnect ebXML Protocol, TIBCO BusinessConnect RosettaNet Protocol, TIBCO BusinessConnect SOAP Protocol) or the startup of the TIBCO BusinessConnect Gateway engines, various column values are populated differently in order to show the views differently when looked from the TIBCO Administrator UI. Usage of the AUX columns is completely governed by individual business protocols or Gateway instances, while the log viewer for individual plug-ins generates a screen layout based on the metadata of these plug-ins. Users who archive the BC_TRANSACTIONS data should query based on the STATUS column and the TS column, which would give all the transactions between the dates specified for certain STATUSes. Note: This table will grow based on the exchanged transactions, as well as the number of times the Gateway instances are restarted. This table should be added as a part of the archiving process; for example, a search for Select * from BC_TRANSACTIONS where STATUS is such as %COMPLETED% OR %ERROR% and TS is <from Date> .
	A typical sample archive query for Oracle can be such as follows: Select * from BC_TRANSACTIONS where STATUS in ('COMPLETED','ERROR') AND TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') <= TO_TIMESTAMP('<to date>', 'YYYY-MM-DD HH24:MI:SS.FF') AND TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') >= TO_TIMESTAMP('<from date>','YYYY-MM-DD HH24:MI:SS.FF') A typical sample archive query for Microsoft SQL database can be such as follows: Select * from BC_TRANSACTIONS where STATUS in ('COMPLETED','ERROR') AND convert(datetime,convert(varchar, TS, 121), 121) >= convert (datetime, '<from_date>', 121) AND convert(datetime,convert(varchar, TS, 121), 121) <= convert(datetime, '<to_date>', 121) (The query varies based on the database type)
BC_MESSAGES	This table provides an audit trail for each transaction summary row. The different state depends on the type of TIBCO BusinessConnect transaction, protocol, and plug-in variations that are used; it also varies based on the stage of transition a transaction enters, such as authentication, decryption, and so on: • TIBCO BusinessConnect audit trails, startup activities, and transitions of each Gateway service in the Gateway Service Instance of the log in the TIBCO Administrator UI. It also audit trails the transaction exchange between partners and TIBCO BusinessConnect in the appropriate protocol level audit trail. • Gateway Service Session login for user level activities for FTP Server and PartnerExpress are logged and an audit trail gets generated with transitions. • Business protocol level audits are captured and can be viewed in the appropriate Protocol Log Viewer section; for example, TIBCO BusinessConnect Services Plug-in has its own Log Viewer screen that audits the different transitions the transactions go through when a partner sends or receives a message. Various audit trail activities keep adding up and a table can grow significantly. This table, along with its BIN table (BC_MESSAGES_BIN, BC_RESEND_BIN) should also be archived. Usage of the AUX columns is completely ruled by individual business protocols or Gateway instances, while the log viewer for individual plug-ins generates a screen layout based on the metadata of these plug-ins. Note: This table would grow based on the exchanged transactions, as well as the number of times the Gateway instances are restarted. This table should be added as a part of the archiving process.
	A typical sample archive query for Oracle can be such as follows: Select * from BC_MESSAGES where BC_TRANS_ID IN (SELECT BC_TRANS_ID FROM BC_TRANSACTIONS where TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') <= TO_TIMESTAMP('<to_date>', 'YYYY-MM-DD HH24:MI:SS.FF') AND TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') >= TO_TIMESTAMP('<from_date>','YYYY-MM-DD HH24:MI:SS.FF') AND STATUS IN ('COMPLETED', 'ERROR')) A typical sample archive query for Microsoft SQL database can be such as follows: Select * from BC_MESSAGES where BC_TRANS_ID IN (select BC_TRANS_ID FROM BC_TRANSACTIONS where convert(datetime,convert(varchar, TS, 121), 121) >= convert(datetime, '<from_date>', 121) AND convert(datetime,convert(varchar, TS, 121), 121) <= convert(datetime, '<to_date>', 121) AND STATUS IN ('COMPLETED', 'ERROR')) The query varies based on the database type.
BC_MESSAGES_BIN	This table has a foreign key to the BC_MESSAGES table. It contains certain transition details that TIBCO BusinessConnect logs, such as failure descriptions or payloads written by TIBCO BusinessConnect protocols when the option Include Message in Log is enabled. Rows in this table need to be archived to avoid the table to grow too big. Note: This table will grow based on the exchanged transactions for business protocols and should be added as part of the archiving process. A typical sample archive query for Oracle varies based on the database type and can be such as follows: Select * from BC_MESSAGES_BIN where BININDEX IN (SELECT BININDEX FROM BC_MESSAGES where BC_TRANS_ID IN (SELECT BC_TRANS_ID FROM BC_TRANSACTIONS where TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') <= TO_TIMESTAMP('<to_date>', 'YYYY-MM-DD HH24:MI:SS.FF') AND TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') >= TO_TIMESTAMP('<from_date>','YYYY-MM-DD HH24:MI:SS.FF') AND STATUS IN ('COMPLETED', 'ERROR'))) A typical sample archive query for Microsoft SQL database can be as follows: Select * from BC_MESSAGES_BIN where BININDEX IN (SELECT BININDEX FROM BC_MESSAGES A, BC_TRANSACTIONS B where A.BC_TRANS_ID = B.BC_TRANS_ID AND convert(datetime,convert(varchar, B.TS, 121), 121) >= convert(datetime, '<from_date>', 121) AND convert(datetime,convert(varchar, B.TS, 121), 121) <= convert(datetime, '<to_date>', 121) AND B.STATUS IN ('COMPLETED', 'ERROR'))
BC_ALERT	This table has a foreign key to the BC_MESSAGES table and contains Alert details that TIBCO BusinessConnect logs, such as expired certificates, and so on. Rows in this table need to be archived to avoid the table to grow too big. A typical sample archive query for Oracle varies based on the database type and can be such as follows: Select * from BC_ALERT where BININDEX IN (SELECT BININDEX FROM BC_MESSAGES where BC_TRANS_ID IN (SELECT BC_TRANS_ID FROM BC_TRANSACTIONS where TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') <= TO_TIMESTAMP(<to_date>, 'YYYY-MM-DD HH24:MI:SS.FF') AND TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') >= TO_TIMESTAMP(<from_date>,'YYYY-MM-DD HH24:MI:SS.FF') AND STATUS IN ('COMPLETED', 'ERROR'))) A typical sample archive query for Microsoft SQL database can be as follows: Select * from BC_ALERT where BININDEX IN (SELECT BININDEX FROM BC_MESSAGES A, BC_TRANSACTIONS B where A.BC_TRANS_ID = B.BC_TRANS_ID AND convert(datetime,convert(varchar, B.TS, 121), 121) >= convert(datetime, '<from_date>', 121) AND convert(datetime,convert(varchar, B.TS, 121), 121) <= convert(datetime, '<to_date>', 121) AND B.STATUS IN ('COMPLETED', 'ERROR'))
BC_RESEND_BIN	This table stores all resendable information to be used by TIBCO BusinessConnect. The stored messages are not in a readable format since it is proprietary information. Note: This table will grow based on the exchanged transactions for business protocols and should be added as part of the archiving process. A typical sample archive query for Oracle varies based on the database type and can be such as follows: Select * from BC_RESEND_BIN where BININDEX IN (SELECT BININDEX from BC_MESSAGES where BC_TRANS_ID IN (SELECT BC_TRANS_ID from BC_TRANSACTIONS where TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') <= TO_TIMESTAMP('<to_date>', 'YYYY-MM-DD HH24:MI:SS.FF') AND TO_TIMESTAMP(TO_CHAR(TS,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF') >= TO_TIMESTAMP('<from_date>','YYYY-MM-DD HH24:MI:SS.FF') AND STATUS IN ('COMPLETED', 'ERROR'))) A typical sample archive query for Microsoft SQL database can be as follows: Select * from BC_RESEND_BIN where BININDEX IN (SELECT BININDEX from BC_MESSAGES A, BC_TRANSACTIONS B where A.BC_TRANS_ID = B.BC_TRANS_ID AND convert(datetime,convert(varchar, B.TS, 121), 121) >= convert(datetime, '<from_date>', 121) AND convert(datetime,convert(varchar, B.TS, 121), 121) <= convert(datetime, '<to_date>', 121) AND B.STATUS IN ('COMPLETED', 'ERROR'))
BC_UACLOG	The table BC_UACLOG logs any edits that happen during the TIBCO BusinessConnect configuration dome with TIBCO Administrator. All changes made by the user logged into the TIBCO Administrator UI are captured in this table. Any changes to participants, business agreements and operation editor are also part of the audit trail captured in this table. The tables BC_UACLOG and BC_UACLOG_DETAIL can be purged and archived based on the column OPERATION_TIME to be a part of the selection query. Note: This table would grow based on the modifications to participants, business agreements, and operations. It should be added as part of the archiving process.
BC_LOGACL_TEMP	This table holds the User-to-Partner information and is only transient until the user is logged and querying the log viewer.
BC_DUP	This table stores the transactions hash values, which are used to indicate whether an incoming or outgoing transaction is a duplicate. The information stored is completely controlled by individual business protocols. Note: This table will grow as transactions are exchanged. It should be added as a part of the archiving process.
BC_UACLOG_DETAIL	Note: This table will grow based on the modifications to participants, business agreements, and operations. It should be added as a part of the archiving process.
BC_POLLER_INFO	This table is used to store transient information populated by the TIBCO Administrator UI, such as Resend Actions or Queue Action. This table does not grow and need not be a part of the archiving process.

The Relationship diagrams describe all the tables that are part of the Audit Log in TIBCO BusinessConnect. Not all entity tables are used at this time, but are created so as to be prepared for future functionality.

Copyright © TIBCO Software Inc. All Rights Reserved