Thanks for the useful tip. Under display audit sequences, I found the audit file formats. I need to do this outside baan tools as we do not have development licence. If it helps other, I am pasting the information below :
Format of Audit Files (tttech0080sf0050)
---------------------------------------
INDEX
auditdef6.1
Info File
Sequence File
audit_spec File
* auditdef6.1
This file is used to specify the directories in which audit files should
be created. This file is located in $BSE/lib. There is one such file for
each BSE environment.
This file has following structure (similar to isamdef6.1).
<Table Name>:<Company Number>:<Directory Name>
Following is meaning of each field.
- Table Name
This indicates the table. This can be in following formats:
"*" - This means all tables in all packages.
Package - This means all tables in this package
E.g tf,td etc.
Package & Module code - All tables in the module
E.g tccom,ttadv.
Package, Module and
table number - E.g ttadv999,tccom010.
- Company Number
Tables can be selected based on company number. E.g.:
* - All companies.
100 - Company 100 only.
100,000 - Company 100 and 000.
- Directory
Directory in which the audit files will be stored.
Under this directory, another directory will be created, whose
name will be:
a<Package + Module Code>
* Info File
There is one info file per table per company number.
The info file can be read using the 4 GL functions or the dictionary
programs.
The info file contains controlling information for sequence files
( called info header ) followed by sequence headers of all sequence
files created.
Format of the info file name is
a<mmmnnn><company number>.inf
'mmm' means module code
'nnn' means table number
E.g for table ttadv999, company 0, the name will be
aadv999000.inf
Structure of the info file is a header called info header, followed by
multiple sequence headers, one for each sequence created.
Info header:
- Version Information
Used to store the version information. This should never be
modified by the application by any means. Length: 4 bytes
- Table name
The table for which audit information is stored.
Length: 8 bytes
- Company Number
The company of the table.
Length: 2 bytes
- Status
Specifies whether sequence reuse is allowed or not.
Length: 1 byte
- From Sequence Number
Audit data will be stored in sequence files specified
from this value. Length: 2 bytes
- To Sequence Number
The last sequence file after which sequence reuse will
be started or the user will have to take appropriate action
like deleting the existing file before reusing them.
Length: 2 bytes
- End Sequence Number
Specifies the last sequence written in the info file.
This field is for internal use by Audit Server only ! The user
should never change this by any means otherwise the audit
server will not work correctly. Length: 2 bytes
- Max. Size of 1 audit file in Bytes
Specifies the maximum file size of a sequence file.
Length: 4 bytes
- Security Level
This determines the users who have access to the
information in the audit files. The security permissions regarding
access to audit information are stored here. Length: 2 bytes
- Current Sequence Number
Determines the current sequence number that will be used to
write the audit data in the sequence file. Length: 2 bytes
- Offset in current sequence file.
Specifies the offset in the current sequence file where the
audit data will be written. Length: 4 bytes
- Reserved space
Reserved space reserved for future use. Application
programs should never use this space as this space may be used by
a future release of the audit server. Length: 8 bytes
- Length of following sequence header
Length: 2 bytes
Sequence Header:
Each sequence file also contains a sequence header at start of it. The
info file contains copies of sequence headers for all sequences created.
- Sequence Number
This specifies the sequence number of the sequence file.
Length: 2 bytes
- Creation Date
This specifies the creation date of sequence file.
Length: 8 bytes; format: YYYYMMDD
- Creation Time
This specifies the creation time of sequence file.
Length: 6 bytes; format: HHMMSS
- Termination Date
This specifies the termination date of sequence file.
Length: 8 bytes; format: YYYYMMDD
- Termination Time
This specifies the termination time of sequence file.
Length: 6 bytes; format: HHMMSS
- Status
The status gives information to user whether the sequence
file was user terminated or was closed because of DD change or
because a transaction data was not possible to accommodate in
sequence file. Length: 2 bytes
- Number of transaction in the sequence
This specifies the data of number of transactions present
in sequence file. Length: 2 bytes
- Application Info - 1
Application Info - 2
Application Info - 3
Application Info - 4
These bytes are reserved for storing data specific to the
application. If the application needs to store any information
in audit files, these fields should be used to store that
information. Length: 4 bytes (per application info)
- Number of fields being audited
The total number of fields being audited. Length: 2 bytes
- Number of fields in primary index (Length: 1 byte)
Indicates how many fields are in primary key.
- Field Name (Length: 8 bytes)
Field Type (Length: 1 byte)
Field Depth (Length: 1 byte)
Field Length (Length: 2 bytes)
Field information for all fields being audited. First, the
fields, which are part of the primary index are written, in the
same sequence as they are in the primary index. Next, all
remaining fields are written in the order in which they occur
in the table DD. E.g if fields f1, f2, f3, f4 are being audited
and the primary index is on f4, f2 then field information will
be written first for f4, next for f2. Then it will be written
for all remaining fields.
- Reserved space
This reserved space may be used in future. Length: 8 bytes
- Length of following sequence header
Length: 2 bytes
Sequence header:
...
...
* Sequence File
Multiple sequence files are used to store audit data for each
table/company number combination. The sequence files can be read using
the 4 GL functions or the dictionary programs.
Format of the sequence file name is
a<mmmnnn><company number>.<sequence file number>
'mmm' means module code
'nnn' means table number
E.g for table ttadv999, company number 0, there can be sequence files
aadv999000.000
aadv999000.001
aadv999000.002
aadv999000.003
....
The sequence file contains the table name and company number, followed
by the sequence header. This is followed by transaction data of multiple
transactions. Transaction data is followed by a transaction header and
contains multiple audit rows generated for the transaction.
Sequence File Layout.
- Table Name
Length: 8 bytes
- Company Number
Length: 2 bytes
- Length of sequence header
Length: 2 bytes
- Sequence Header
Explained above.
- Transaction Header
- Transaction Data ( Audit rows )
- Transaction Header
- Transaction Data ( Audit rows )
- Transaction Header
- Transaction Data ( Audit rows )
....
The format of the sequence header is already described above, in info
file description. The format of the transaction header is as follows:
Transaction Header Format:
We need to log both Unix user id & BAAN User name. This
is because a Unix user can operate using different BAAN user
names by changing the "USER" environment variable. Different
BAAN users can have different table privileges in SQL
databases. So, both should be stored. Using unix user id, print
programs can find the unix login name etc.
- Unix user id (Length: 4 bytes)
- BAAN User Name (Length: 8 bytes)
- Date ( YYYYMMDD ) (Length: 8 bytes)
- Time ( HHMMSS ) (Length: 6 bytes)
- Session Name (Length: 15 bytes)
- Transaction Status (Length: 1 byte)
Transaction Status can have following values.
COMMIT_DONE
ABORT_DONE
- Number of audit rows in the transaction (Length: 2 bytes)
- Total bytes of data of this transaction (Length: 4 bytes)
Transaction Data Format:
- Length of the row (Length: 2 bytes)
- Application Data (Length: 4 bytes)
- Type of operation (Length: 1 byte)
- Row Data
In every audit row, 4 bytes space is kept to store application data.
This data is stored and manipulated by 4-GL applications and not by the
audit server. The audit server just keeps the 4 byte extra space to be
used later. "Type of operation" is a single character field, indicating
the database action done on the table. Following are the characters used
for indicating audited database actions.
C - Create Table.
L - Clear Table.
R - Drop ( Remove ) Table.
I - Insert row.
D - Delete row.
U - Update row.
In the audit row, the row data is present only for row level actions.
The field values, whenever stored, are stored in the same format as
their data type. Thus a value for a "long" field is also stored as long
in the audit row. The length of each field entry in the audit row is the
same as the length of the column.
For insert, the row data consist of field values ( i.e the values to be
inserted ) of all audited fields, in the order in which they occur in
the audit DD.
For delete, the row data consist of field values ( of the row to be
deleted ) of all audited fields, in the order in which they occur in
the audit DD.
In case of update, not all fields in the table may be updated. So, apart
from storing field values, a identifier is required to specify the field
to which this data belongs to. Here, field numbers are used for
identifying the fields. A field number for a field is its position in
the AUDIT DD ( starting with field 0 ).
A entry for a field contains the following information:
- Field Number - 2 Bytes
- Is the value changed - 1 Byte ( 'Y' or 'N' ).
- Old Value
- New Value
Here, if a field is changed, both old and new values are stored. If a
primary audited key field is not changed, only the old value is stored.
For other audited fields, if they are not changed, nothing is stored.
For all primary key audited fields and all other audited fields which
are changed, these entries are made to make the audit data.
For more information on audit rows and examples, refer to chapter
Audit Management.
* audit_spec File
For specifying audit file parameters, a file named "audit_spec" is used.
This file resides in the $BSE/lib directory. There is one such file for
each BSE environment.
Format of the file is
<Table Name>:<Company Number>:<Audit Specifications>
Following is meaning of each field.
- Table Name
This indicates the table. This can be in following formats:
"*" - This means all tables in all packages.
Package - This means all tables in this package
E.g tf,td etc.
Package & Module code - All tables in the module
E.g tccom,ttadv.
Package, Module and
table number - E.g ttadv999,tccom010.
- Company
This can be either a in one of following formats.
* - All companies.
100 - Single company e.g company 100 only.
100,000 - Comma separated list of companies e.g
company 100 and 000.
- Specifications
The following specifications can be put in the file.
- Start Sequence.
This is specified by the keyword "FROMSEQ" or "fromseq". The
start sequence is always 000. The user can not change this.
- To Sequence.
This is specified by the keyword "TOSEQ" or "toseq" followed
by a sequence number.
Note that there should be a space between the keyword and
the sequence number.
E.g
TOSEQ 999
or
toseq 999
If the end sequence is not specified for a table/company
combination, default is 999.
- Maximum sequence file size.
This is specified by the keyword "MAXSEQSIZE" or "MAXSEQSIZE"
followed by a size specification.
Note that there should be a space between the keyword and
the size specification.
The size can be specified in
- bytes e.g MAXSEQSIZE 10000
( This means 10000 bytes )
- Kilobytes e.g MAXSEQSIZE 10K
( This means 10K bytes or 10*1024 bytes )
- Megabytes e.g MAXSEQSIZE 2M
( This means 2M bytes or 2*1024*1024 bytes )
Note that for Kbyte and Mbyte specifications the characters
'K' and 'M' should be UPPERCASE ONLY and there should be no
space between the number and the specification character.
If maximum sequence size is not specified for a table/company
combination, default is 512K.
- Whether sequences can be reused or not.
If existing sequences can be reused without deleting first,
the keyword "SEQREUSE" or "seqreuse" should be put in the
specifications. If this is not wanted, don't put the keyword
in the specifications. If this is not specified for a table,
the sequence reused is not allowed.
- Security Assigned to current table/company number
audit files.
This is specified as keyword "SECURITY" or "security" followed
by a security number. Note that there should be a space
between the keyword and the security specifications. E.g
SECURITY 4
or
security 4
The different security levels along with their codes are as
follows:
SECURITY VALUE
-------- -----
PRINT 4
CLEAN 8
MAINTAIN 16
APPLICATION DEFINED 32
The combination of these is explained in Authorizations.
To specify CLEAN + MAINTAIN, security is specified as security
24 ( which is sum of 8 + 16 ).
APPLICATION DEFINED cannot be combined with PRINT and CLEAN.
It can be combined with MAINTAIN permissions ONLY.
Note that these specifications can be put in any order.
IT IS STRONGLY ADVISED THAT THE AUDIT_SPEC FILE SHOULD HAVE A DEFAULT
ENTRY COVERING ALL TABLES/COMPANY NUMBERS.
This can be done with a "*" for table and company number fields. This
should be the last line of the file.
A sample audit_spec file may look as follows:
ttadv999:000:TOSEQ 99 FROMSEQ 000 MAXSEQSIZE 2M
tt:000:seqreuse toseq 49 maxseqsize 1M
tf:000:toseq 49 seqreuse maxseqsize 20K
*:*:FROMSEQ 000 TOSEQ 499 MAXSEQSIZE 100K
NOTE
It is important that the audit_spec file should have read
permission for all users and write permission for the "root" user
only. Thus, users other than "root" will not be able to change the
security parameter in the file, allowing them illegal access.
Baanboard is shutting down on 31-dec-2023. See: 
Audit file structure
Hybrid Mode
