Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 27th April 2007, 13:54
baan_user baan_user is offline
Junior Member
 
Join Date: Jul 2004
Posts: 15
baan_user is on a distinguished road
Baan: IVc4 - DB: SQL Server - OS: Windows NT
Post Audit file structure
Baan: Baan IVc4
C/S: None/Unknown

I need to read the audit files on the server generated by turning auditing on certain tables. We do not have development licence.

Is it possible to read the generated audit files from C / C++ or SQL Server.

I need to understand the structure of these files, so that I can read them and use them to generate our own reports.

Thanks for all your help.
Reply With Quote
  #2  
Old 27th April 2007, 14:33
mr_suleyman's Avatar
mr_suleyman mr_suleyman is offline
Guru
 
Join Date: Aug 2004
Posts: 639
mr_suleyman is an unknown quantity at this point
Baan: Baan IV - DB: Oracle - OS: HP UX, IBM AIX
There are lot's of thread about Audit file .Use search option on board.


G.Luck !
Reply With Quote
  #3  
Old 30th April 2007, 12:39
baan_user baan_user is offline
Junior Member
 
Join Date: Jul 2004
Posts: 15
baan_user is on a distinguished road
Baan: IVc4 - DB: SQL Server - OS: Windows NT
Dear Sir,

I have searched the whole forum for audit file / audit file structure / audit / audit sql server etc. and went through all the links which came up. Then I searched support.baan.com (useless), google etc and came up with no useful information that I am looking for.

Again if anyone knows the file structures of audit files and how they can be read from C / c# / SQL Server or anything outside baan 4gl, it will be a great help.

I have used tools to search for strings in tools objects like ottaad4160, ottaad4460, but this does not come up with any useful information.
Reply With Quote
  #4  
Old 30th April 2007, 14:16
Marioth's Avatar
Marioth Marioth is offline
Senior Member
 
Join Date: Aug 2001
Location: Ireland
Posts: 138
Marioth is on a distinguished road
Baan: BFCS,BMCS,BOCS, Triton 1 until 2.2, BaaN 3 until BaaN ERP (5), Ln 6.1 FP3 - DB: Oracle, Informix, B-Isam, C-Isam - OS: HP-unix, AIX, Sun Solaris, Digital, NT
Try to run the session with option "Run programm" the session is ttaad4461m000
Reply With Quote
  #5  
Old 30th April 2007, 16:09
norwim's Avatar
norwim norwim is offline
Guru
 
Join Date: Oct 2001
Location: Germany Moenchengladbach
Posts: 450
norwim is on a distinguished road
Baan: some - DB: more - OS: many
Hi there,

as I have no Baan system at hand atm, I can only guess that the session mentioned above is the correct one, extracting the audit information from the seq. files they have been written to.
I wouldn't bother and try decrypting these files, any information kept there is compressed to a large degree and I never came across any information about the audit file structure.
Instead I would create a job calling the Baan session that prints out the audit information, call this job periodically and eventually write a small script that processes this output to give you the information needed in a shorter overview.
For this you don't need a development licence.

hth

Norbert
__________________
Have a nice day
Reply With Quote
  #6  
Old 3rd May 2007, 12:59
baan_user baan_user is offline
Junior Member
 
Join Date: Jul 2004
Posts: 15
baan_user is on a distinguished road
Baan: IVc4 - DB: SQL Server - OS: Windows NT
Thanks Norbert. Incidently this is exactly what baan suggested me to do.

My argument to baan was that the audit files are for us (the baan users) so why not allow us to read them and print the information using standard reporting tools. What good is this audit information if the outputs are not user friendly, we cannot change them and we cannot use these files to generate audit reports from other reporting tools.
Reply With Quote
  #7  
Old 3rd May 2007, 16:18
norwim's Avatar
norwim norwim is offline
Guru
 
Join Date: Oct 2001
Location: Germany Moenchengladbach
Posts: 450
norwim is on a distinguished road
Baan: some - DB: more - OS: many
Hi Baan-User,

I think that this is a kind of philosophical question. Think about transaction logs of oracle - as long as you can use them to roll back you really don't need to know their data structure.
Audit information is collected regardless of the DB in use and kept outside the database, which I think is a nice thing to have.
As the script which you may need to change the format of the print output, is indeed easy to create, I don't see a problem here.
If you need help with the script, drop me a line:
norbert.wimmer ATsigN ono.de

hth

Norbert
__________________
Have a nice day
Reply With Quote
  #8  
Old 3rd May 2007, 20:50
shah_bs's Avatar
shah_bs shah_bs is offline
Guru
 
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 387
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
So, back to the original 'request' - assuming you still really want to do this in C/C++ etc (!!).

I assume you are using BW Client (BAAN IVc3).

On the main menu, click on the BAAN IV Tools to highlight.
Then click on the <Help> item on the menu bar, and choose "Contents".

In the popup that comes up, scroll down to "Database Management" and select "Audit Management" - inside of that, there is a link to a document called "Audit Management" - select that and click "Relations" and then browse around under that and you should get to the Audit File formats.
Reply With Quote
Sponsored Links
  #9  
Old 4th May 2007, 15:32
baan_user baan_user is offline
Junior Member
 
Join Date: Jul 2004
Posts: 15
baan_user is on a distinguished road
Baan: IVc4 - DB: SQL Server - OS: Windows NT
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.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Programming Question mr_suleyman Tools Development 37 11th November 2016 09:14
Read audit file SriksAdi Tools Development 3 13th October 2010 09:04
read audit file baan_ghai Tools Development 2 11th October 2005 14:52
Unable to Purge Audit File learner Tools Administration & Installation 4 21st June 2004 11:33


All times are GMT +2. The time now is 23:18.


©2001-2017 - Baanboard.com - Baanforums.com