Re: Reporting on audit trails (SE)

>From: hugh@nezsdc.icl.co.nz (Hugh Grierson) >Subject: Reporting on audit trails (SE) >Date: Wed, 1 Jun 94 02:02:33 GMT >X-Informix-List-Id: <news.6961> > >I need to access the data written to an audit trail from SE. Using SQL >is preferred, but I can't find the notes I once had on exactly how to >do it. > >It was easy enough to figure out that I need to create a table containing >something like... > audit_flag char(2) > audit_time integer > audit_pid smallint > audit_uid smallint > audit_rowid integer >followed by the same columns as in the table being audited. >Then I can copy the audit file over the top of this table's .dat file. > >Now I can select from the audit table, BUT I only get one row. >Bcheck the dat/idx, and still I only get one row. > >What am I missing? I have a system which recycles old saved mail (to keep my mail storage down to somewhere under 15 MB), and it chose to pass me this old mail on audit trails today. It may help. I don't immediately see the significance of creating an index on the audit-trail table, but I guess that is what you're missing. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> =From: shemminga@cix.compulink.co.uk (Stuart Hemming) =Subject: Info from Audit Trails in SE - =Date: 25 Sep 92 11:38:23 GMT =X-Informix-List-Id: <news.1880> = =Below is a very neat way of accessing the contents of an audit trail file: = =--------------------------------CUT HERE-------------------------- = = PROCEDURE for MANIPULATING an AUDIT TRAIL = ----------------------------------------- = 1. Make a copy of the CREATE TABLE script for each table you want to work = with. These copies will be used to create database tables to hold audit trail = information. = = 2. Edit each of the CREATE TABLE statements to include 5 new columns. The = new columns will hold the header data of each audit trail record. Give each = table a new name. Insert the five new columns shown below at the beginning of = the file: = = au_type char(2) = au_time integer = au_procid smallint = au_userid smallint = au_recnum integer = = 3. Run the CREATE TABLE statements to build each of the new .dat files = = {Example for customer table of stores database} = CREATE TABLE aud_cust = ( = au_type char(2), = au_time integer, = au_procid smallint, = au_userid smallint, = au_recnum integer, = customer_num serial not null, = fname char(15), = lname char(15), = company char(20), = address1 char(20), = address2 char(20), = city char(15), = state char(2), = zipcode char(5), = phone char(18) = ); = = 4. Query the systables catalog to see the 3 digit number assigned to each of = the table names. = = 5. Copy the audit trail log files to the names of the newly created .dat = files. = = Example: cp aud_file stores.dbs/aud_cus109.dat = = 6. Create an index in each new table - one index per file on any column. = = 7. From ISQL, repair each of the new tables = = Example: repair table aud_cust = = Note: This will invoke bcheck with the -y option to rebuild the index = file. = = 8. You can now work with these new tables as ordinary database tables. = = ---------------------------- CUT HERE ---------------------------- = =Thanks to Scott Myers at Informix for the above. = =As an additional point, if after carrying out the above up to point 4 =you update the entry in systables for the logfile to point at the =audit trail file before carrying out points 6 and 7 you should have =access to the up-to-date audit trail for all of your reports. (I =thought of this bit 8-)). = =Hope that this is useful to someone else. =Thanks again Scott.