audit trail

[melody]

Hi,

I'm building an audit trail for a web app using MS-Access.
The way I'm doing it now is by adding fields
like 'LastUpdated', 'UpdatedBy', etc. to each record in
the tables. I'm sure this method isn't too effective (
previous values for the audit fields are overwritten) or
efficient (tedious if there are many tables).

I very much appreciate any advice/pointers on this

Thanks in advance,
melody

[Ray at]

I suppose there are a few options. If you have a table that has CD rates or
something like:

RateID RateName Rate APR
1 12 Month CD 1.04 1.05
2 24 Month CD 1.34 1.35
3 36 Month CD 1.98 2.00
4 48 Month CD 2.27 2.30



You can create a change log table as:

ID RateID Rate APR Username ChangeTime
1 1 1.54 1.56 Joe 06/12/2003 10:39:31
1 1 1.36 1.37 Joe 08/15/2003 06:47:59
1 1 1.15 1.14 Joe 10/22/2003 14:19:41
1 1 1.04 1.05 Joe 11/01/2003 09:02:33

That would be that every time you execute an UPDATE on the first table, you
also execute an INSERT on the log table with the same info.

Another option, depending on your application, is to just have a generic log
table that you generate statements and insert into. In the first ASP app I
ever made, which happened to be a rates system kinda like this, I log all
rate changes in addition to changes like:

User Joe removed rate 34 from bank Web site
User Joe added rate 39 to bank Web site
User Joe changed the footnote for mortgages on Web site to: blah blah blah

Now, I break it differently than that, like, I actually put the username in
a different column so I can see all the changes that user has made. And I
also keep the rate IDs in their own column so I can see histories of rates,
etc.

Ray at home


"melody" <anonymous@discussions.microsoft.com> wrote in message
news:053201c3aa5d$9a33eac0$a501280a@phx.gbl...

>
> Hi,
>
> I'm building an audit trail for a web app using MS-Access.
> The way I'm doing it now is by adding fields
> like 'LastUpdated', 'UpdatedBy', etc. to each record in
> the tables. I'm sure this method isn't too effective (
> previous values for the audit fields are overwritten) or
> efficient (tedious if there are many tables).
>
> I very much appreciate any advice/pointers on this
>
> Thanks in advance,
> melody
>

[melody]

Thanks a lot for answering my question :-)

Referring to your first solution, does it mean that I will
have to create a log table for each table that needs an
audit trail ? I'm also wondering how many records can
Access handle for the log table.

Thanks in advance,
Melody

>-----Original Message-----
>I suppose there are a few options. If you have a table

that has CD rates or

>something like:
>
>RateID RateName Rate APR
>1 12 Month CD 1.04 1.05
>2 24 Month CD 1.34 1.35
>3 36 Month CD 1.98 2.00
>4 48 Month CD 2.27 2.30
>
>
>
>You can create a change log table as:
>
>ID RateID Rate APR Username ChangeTime
>1 1 1.54 1.56 Joe 06/12/2003 10:39:31
>1 1 1.36 1.37 Joe 08/15/2003 06:47:59
>1 1 1.15 1.14 Joe 10/22/2003 14:19:41
>1 1 1.04 1.05 Joe 11/01/2003 09:02:33
>
>That would be that every time you execute an UPDATE on

the first table, you

>also execute an INSERT on the log table with the same

info.

>
>Another option, depending on your application, is to just

have a generic log

>table that you generate statements and insert into. In

the first ASP app I

>ever made, which happened to be a rates system kinda like

this, I log all

>rate changes in addition to changes like:
>
>User Joe removed rate 34 from bank Web site
>User Joe added rate 39 to bank Web site
>User Joe changed the footnote for mortgages on Web site

to: blah blah blah

>
>Now, I break it differently than that, like, I actually

put the username in

>a different column so I can see all the changes that user

has made. And I

>also keep the rate IDs in their own column so I can see

histories of rates,

>etc.
>
>Ray at home
>
>
>"melody" <anonymous@discussions.microsoft.com> wrote in

message

>news:053201c3aa5d$9a33eac0$a501280a@phx.gbl...

>>
>> Hi,
>>
>> I'm building an audit trail for a web app using MS-

Access.

>> The way I'm doing it now is by adding fields
>> like 'LastUpdated', 'UpdatedBy', etc. to each record in
>> the tables. I'm sure this method isn't too effective (
>> previous values for the audit fields are overwritten) or
>> efficient (tedious if there are many tables).
>>
>> I very much appreciate any advice/pointers on this
>>
>> Thanks in advance,
>> melody
>>

>
>
>.
>

[Ray at]

"melody" <anonymous@discussions.microsoft.com> wrote in message
news:079901c3ae7f$7ed58d30$a101280a@phx.gbl...

>
> Thanks a lot for answering my question :-)
>
> Referring to your first solution, does it mean that I will
> have to create a log table for each table that needs an
> audit trail ?

Yes, I suppose so.

> I'm also wondering how many records can
> Access handle for the log table.

There isn't any fixed number, afaik, but if your table consisted of the data
below, I believe you could hit a million without issues. There isn't a
specific limit on the number of rows in an Access table like in a
spreadsheet, for example.



<quote>
Attribute Maximum
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048; the actual number may be less because of
tables opened internally by Microsoft Access
Table size 2 gigabyte minus the space needed for the system objects
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through
the user interface;
1 gigabyte of character storage when entering data programmatically
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2,048
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object fields)
2,000
Number of characters in a field property setting 255
</quote>


Another option is to log to text files. Create a new file each day, month,
year, whatever. It all depends on what you want to do with the data. If
you want to be able to pull up the change history of something on demand
often, text files would not be a good idea. But if you want something just
for the sake of the once-a-year-research-a-problem, text files may be okay.

Ray at work

>
> Thanks in advance,
> Melody
>

> >-----Original Message-----
> >I suppose there are a few options. If you have a table

> that has CD rates or

> >something like:
> >
> >RateID RateName Rate APR
> >1 12 Month CD 1.04 1.05
> >2 24 Month CD 1.34 1.35
> >3 36 Month CD 1.98 2.00
> >4 48 Month CD 2.27 2.30
> >
> >
> >
> >You can create a change log table as:
> >
> >ID RateID Rate APR Username ChangeTime
> >1 1 1.54 1.56 Joe 06/12/2003 10:39:31
> >1 1 1.36 1.37 Joe 08/15/2003 06:47:59
> >1 1 1.15 1.14 Joe 10/22/2003 14:19:41
> >1 1 1.04 1.05 Joe 11/01/2003 09:02:33
> >
> >That would be that every time you execute an UPDATE on

> the first table, you

> >also execute an INSERT on the log table with the same

> info.

> >
> >Another option, depending on your application, is to just

> have a generic log

> >table that you generate statements and insert into. In

> the first ASP app I

> >ever made, which happened to be a rates system kinda like

> this, I log all

> >rate changes in addition to changes like:
> >
> >User Joe removed rate 34 from bank Web site
> >User Joe added rate 39 to bank Web site
> >User Joe changed the footnote for mortgages on Web site

> to: blah blah blah

> >
> >Now, I break it differently than that, like, I actually

> put the username in

> >a different column so I can see all the changes that user

> has made. And I

> >also keep the rate IDs in their own column so I can see

> histories of rates,

> >etc.
> >
> >Ray at home
> >
> >
> >"melody" <anonymous@discussions.microsoft.com> wrote in

> message

> >news:053201c3aa5d$9a33eac0$a501280a@phx.gbl...

> >>
> >> Hi,
> >>
> >> I'm building an audit trail for a web app using MS-

> Access.

> >> The way I'm doing it now is by adding fields
> >> like 'LastUpdated', 'UpdatedBy', etc. to each record in
> >> the tables. I'm sure this method isn't too effective (
> >> previous values for the audit fields are overwritten) or
> >> efficient (tedious if there are many tables).
> >>
> >> I very much appreciate any advice/pointers on this
> >>
> >> Thanks in advance,
> >> melody
> >>

> >
> >
> >.
> >

[n/a]

Hi Melody,

I have implemented a similar solution in an Oracle environment. The
requirement was to capture all changes to approximately 40 tables. What
I did in this case was to create a log table that captured the name of
the table, the field that was changed and the value of the field that
was changed for a particular record, the userid of the person making the
change and the date/time the change was made.

The 40 tables eventually grew to have roughly 700K records combined and
the audit log table grew to over 2M records. However this was for a Y2K
project, so eventually the changes stopped. I suspect that you'd have
to archive the data on a regular basis (maybe 3-6 months or when the log
table grew to over 500K records) in an Access-based system.

If you need more info, let me know and I'll try to dig up the table
structure and more details for you.

Joel

melody wrote:

> Thanks a lot for answering my question :-)
>
> Referring to your first solution, does it mean that I will
> have to create a log table for each table that needs an
> audit trail ? I'm also wondering how many records can
> Access handle for the log table.
>
> Thanks in advance,
> Melody
>
>

>>-----Original Message-----
>>I suppose there are a few options. If you have a table

>
> that has CD rates or
>

>>something like:
>>
>>RateID RateName Rate APR
>>1 12 Month CD 1.04 1.05
>>2 24 Month CD 1.34 1.35
>>3 36 Month CD 1.98 2.00
>>4 48 Month CD 2.27 2.30
>>
>>
>>
>>You can create a change log table as:
>>
>>ID RateID Rate APR Username ChangeTime
>>1 1 1.54 1.56 Joe 06/12/2003 10:39:31
>>1 1 1.36 1.37 Joe 08/15/2003 06:47:59
>>1 1 1.15 1.14 Joe 10/22/2003 14:19:41
>>1 1 1.04 1.05 Joe 11/01/2003 09:02:33
>>
>>That would be that every time you execute an UPDATE on

>
> the first table, you
>

>>also execute an INSERT on the log table with the same

>
> info.
>

>>Another option, depending on your application, is to just

>
> have a generic log
>

>>table that you generate statements and insert into. In

>
> the first ASP app I
>

>>ever made, which happened to be a rates system kinda like

>
> this, I log all
>

>>rate changes in addition to changes like:
>>
>>User Joe removed rate 34 from bank Web site
>>User Joe added rate 39 to bank Web site
>>User Joe changed the footnote for mortgages on Web site

>
> to: blah blah blah
>

>>Now, I break it differently than that, like, I actually

>
> put the username in
>

>>a different column so I can see all the changes that user

>
> has made. And I
>

>>also keep the rate IDs in their own column so I can see

>
> histories of rates,
>

>>etc.
>>
>>Ray at home
>>
>>
>>"melody" <anonymous@discussions.microsoft.com> wrote in

>
> message
>

>>news:053201c3aa5d$9a33eac0$a501280a@phx.gbl...
>>

>>>Hi,
>>>
>>>I'm building an audit trail for a web app using MS-

>
> Access.
>

>>>The way I'm doing it now is by adding fields
>>>like 'LastUpdated', 'UpdatedBy', etc. to each record in
>>>the tables. I'm sure this method isn't too effective (
>>>previous values for the audit fields are overwritten) or
>>>efficient (tedious if there are many tables).
>>>
>>>I very much appreciate any advice/pointers on this
>>>
>>>Thanks in advance,
>>>melody
>>>

>>
>>
>>.
>>

[jason]

In my opinion, this is the best method:

1. Primary table
1
ItemName: Lord of The Rings
ItemCose: $100



2. AuditPrimaryTable

AudDate: 11/23/2003
AudiID: 1
AudIP: 192.1.3.etc
AudType: INSERT (or DELETE or EDIT)
AudUser: Melody

Everytime you INSERT. EDIT, DELETE you deposit into:
- Primary
- AuditPrimary

You can then conduct searches based on the AudType.

I also suggest you read Allen Brownes excellent article:
[url]http://users.bigpond.net.au/abrowne1/AppAudit.html[/url]

....which helped me get through the growing pains of dooing something
similiar. You could test the application in Access and then retrofit it
to an asp application by using the same fundamentals.



Hth
Jason



"melody" <anonymous@discussions.microsoft.com> wrote in message
news:053201c3aa5d$9a33eac0$a501280a@phx.gbl...

>
> Hi,
>
> I'm building an audit trail for a web app using MS-Access.
> The way I'm doing it now is by adding fields
> like 'LastUpdated', 'UpdatedBy', etc. to each record in
> the tables. I'm sure this method isn't too effective (
> previous values for the audit fields are overwritten) or
> efficient (tedious if there are many tables).
>
> I very much appreciate any advice/pointers on this
>
> Thanks in advance,
> melody
>

[melody]

I'm not sure how I missed this post, but thank you for
answering my questions. The information is helpful :-)

Melody

>-----Original Message-----
>
>"melody" <anonymous@discussions.microsoft.com> wrote in

message

>news:079901c3ae7f$7ed58d30$a101280a@phx.gbl...

>>
>> Thanks a lot for answering my question :-)
>>
>> Referring to your first solution, does it mean that I

will

>> have to create a log table for each table that needs an
>> audit trail ?

>
>Yes, I suppose so.
>

>> I'm also wondering how many records can
>> Access handle for the log table.

>
>There isn't any fixed number, afaik, but if your table

consisted of the data

>below, I believe you could hit a million without issues.

There isn't a

>specific limit on the number of rows in an Access table

like in a

>spreadsheet, for example.
>
>
>
><quote>
>Attribute Maximum
>Number of characters in a table name 64
>Number of characters in a field name 64
>Number of fields in a table 255
>Number of open tables 2048; the actual number may be

less because of

>tables opened internally by Microsoft Access
>Table size 2 gigabyte minus the space needed for the

system objects

>Number of characters in a Text field 255
>Number of characters in a Memo field 65,535 when

entering data through

>the user interface;
> 1 gigabyte of character storage when entering data

programmatically

>Size of an OLE Object field 1 gigabyte
>Number of indexes in a table 32
>Number of fields in an index 10
>Number of characters in a validation message 255
>Number of characters in a validation rule 2,048
>Number of characters in a table or field description

255

>Number of characters in a record (excluding Memo and OLE

Object fields)

>2,000
>Number of characters in a field property setting 255
></quote>
>
>
>Another option is to log to text files. Create a new

file each day, month,

>year, whatever. It all depends on what you want to do

with the data. If

>you want to be able to pull up the change history of

something on demand

>often, text files would not be a good idea. But if you

want something just

>for the sake of the once-a-year-research-a-problem, text

files may be okay.

>
>Ray at work
>
>
>
>
>
>

>>
>> Thanks in advance,
>> Melody
>>

>> >-----Original Message-----
>> >I suppose there are a few options. If you have a table

>> that has CD rates or

>> >something like:
>> >
>> >RateID RateName Rate APR
>> >1 12 Month CD 1.04 1.05
>> >2 24 Month CD 1.34 1.35
>> >3 36 Month CD 1.98 2.00
>> >4 48 Month CD 2.27 2.30
>> >
>> >
>> >
>> >You can create a change log table as:
>> >
>> >ID RateID Rate APR Username ChangeTime
>> >1 1 1.54 1.56 Joe 06/12/2003 10:39:31
>> >1 1 1.36 1.37 Joe 08/15/2003 06:47:59
>> >1 1 1.15 1.14 Joe 10/22/2003 14:19:41
>> >1 1 1.04 1.05 Joe 11/01/2003 09:02:33
>> >
>> >That would be that every time you execute an UPDATE on

>> the first table, you

>> >also execute an INSERT on the log table with the same

>> info.

>> >
>> >Another option, depending on your application, is to

just

>> have a generic log

>> >table that you generate statements and insert into. In

>> the first ASP app I

>> >ever made, which happened to be a rates system kinda

like

>> this, I log all

>> >rate changes in addition to changes like:
>> >
>> >User Joe removed rate 34 from bank Web site
>> >User Joe added rate 39 to bank Web site
>> >User Joe changed the footnote for mortgages on Web site

>> to: blah blah blah

>> >
>> >Now, I break it differently than that, like, I actually

>> put the username in

>> >a different column so I can see all the changes that

user

>> has made. And I

>> >also keep the rate IDs in their own column so I can see

>> histories of rates,

>> >etc.
>> >
>> >Ray at home
>> >
>> >
>> >"melody" <anonymous@discussions.microsoft.com> wrote in

>> message

>> >news:053201c3aa5d$9a33eac0$a501280a@phx.gbl...
>> >>
>> >> Hi,
>> >>
>> >> I'm building an audit trail for a web app using MS-

>> Access.

>> >> The way I'm doing it now is by adding fields
>> >> like 'LastUpdated', 'UpdatedBy', etc. to each record

in

>> >> the tables. I'm sure this method isn't too effective

(

>> >> previous values for the audit fields are

overwritten) or

>> >> efficient (tedious if there are many tables).
>> >>
>> >> I very much appreciate any advice/pointers on this
>> >>
>> >> Thanks in advance,
>> >> melody
>> >>
>> >
>> >
>> >.
>> >

>
>
>.
>

[melody]

Hi Joel,

Thank you for responding to my question (I missed several
replies to this thread so excuse the very late reply..) :-)

I really welcome any any help and advice (provided the
offer is still open and it's not to much trouble to dig up
the material). 40 tables is a lot to manage. Just for your
information, I'm dealing with much much less than that,
only about 9 tables at the moment.

Thanks in advance,
melody

>-----Original Message-----
>Hi Melody,
>
>I have implemented a similar solution in an Oracle

environment. The

>requirement was to capture all changes to approximately

40 tables. What

>I did in this case was to create a log table that

captured the name of

>the table, the field that was changed and the value of

the field that

>was changed for a particular record, the userid of the

person making the

>change and the date/time the change was made.
>
>The 40 tables eventually grew to have roughly 700K

records combined and

>the audit log table grew to over 2M records. However

this was for a Y2K

>project, so eventually the changes stopped. I suspect

that you'd have

>to archive the data on a regular basis (maybe 3-6 months

or when the log

>table grew to over 500K records) in an Access-based

system.

>
>If you need more info, let me know and I'll try to dig up

the table

>structure and more details for you.
>
>Joel
>
>melody wrote:

>> Thanks a lot for answering my question :-)
>>
>> Referring to your first solution, does it mean that I

will

>> have to create a log table for each table that needs an
>> audit trail ? I'm also wondering how many records can
>> Access handle for the log table.
>>
>> Thanks in advance,
>> Melody
>>
>>

>>>-----Original Message-----
>>>I suppose there are a few options. If you have a table

>>
>> that has CD rates or
>>

>>>something like:
>>>
>>>RateID RateName Rate APR
>>>1 12 Month CD 1.04 1.05
>>>2 24 Month CD 1.34 1.35
>>>3 36 Month CD 1.98 2.00
>>>4 48 Month CD 2.27 2.30
>>>
>>>
>>>
>>>You can create a change log table as:
>>>
>>>ID RateID Rate APR Username ChangeTime
>>>1 1 1.54 1.56 Joe 06/12/2003 10:39:31
>>>1 1 1.36 1.37 Joe 08/15/2003 06:47:59
>>>1 1 1.15 1.14 Joe 10/22/2003 14:19:41
>>>1 1 1.04 1.05 Joe 11/01/2003 09:02:33
>>>
>>>That would be that every time you execute an UPDATE on

>>
>> the first table, you
>>

>>>also execute an INSERT on the log table with the same

>>
>> info.
>>

>>>Another option, depending on your application, is to

just

>>
>> have a generic log
>>

>>>table that you generate statements and insert into. In

>>
>> the first ASP app I
>>

>>>ever made, which happened to be a rates system kinda

like

>>
>> this, I log all
>>

>>>rate changes in addition to changes like:
>>>
>>>User Joe removed rate 34 from bank Web site
>>>User Joe added rate 39 to bank Web site
>>>User Joe changed the footnote for mortgages on Web site

>>
>> to: blah blah blah
>>

>>>Now, I break it differently than that, like, I actually

>>
>> put the username in
>>

>>>a different column so I can see all the changes that

user

>>
>> has made. And I
>>

>>>also keep the rate IDs in their own column so I can see

>>
>> histories of rates,
>>

>>>etc.
>>>
>>>Ray at home
>>>
>>>
>>>"melody" <anonymous@discussions.microsoft.com> wrote in

>>
>> message
>>

>>>news:053201c3aa5d$9a33eac0$a501280a@phx.gbl.. .
>>>
>>>>Hi,
>>>>
>>>>I'm building an audit trail for a web app using MS-

>>
>> Access.
>>

>>>>The way I'm doing it now is by adding fields
>>>>like 'LastUpdated', 'UpdatedBy', etc. to each record in
>>>>the tables. I'm sure this method isn't too effective (
>>>>previous values for the audit fields are overwritten)

or

>>>>efficient (tedious if there are many tables).
>>>>
>>>>I very much appreciate any advice/pointers on this
>>>>
>>>>Thanks in advance,
>>>>melody
>>>>
>>>
>>>
>>>.
>>>

>
>.
>

[melody]

Hi Jason,

Thanks you for the information, the article was certainly
helpful to me :-) However I missed your post (and a few
other people's too..) for about more than a month, so I
hope you can excuse the late response.

Thanks again,
Melody

>-----Original Message-----
>In my opinion, this is the best method:
>
>1. Primary table
> 1
> ItemName: Lord of The Rings
> ItemCose: $100
>
>
>
>2. AuditPrimaryTable
>
> AudDate: 11/23/2003
> AudiID: 1
> AudIP: 192.1.3.etc
> AudType: INSERT (or DELETE or EDIT)
> AudUser: Melody
>
>Everytime you INSERT. EDIT, DELETE you deposit into:
>- Primary
>- AuditPrimary
>
>You can then conduct searches based on the AudType.
>
>I also suggest you read Allen Brownes excellent article:
>[url]http://users.bigpond.net.au/abrowne1/AppAudit.html[/url]
>
>....which helped me get through the growing pains of

dooing something

>similiar. You could test the application in Access and

then retrofit it

>to an asp application by using the same fundamentals.
>
>
>
>Hth
>Jason
>
>
>
>"melody" <anonymous@discussions.microsoft.com> wrote in

message

>news:053201c3aa5d$9a33eac0$a501280a@phx.gbl...

>>
>> Hi,
>>
>> I'm building an audit trail for a web app using MS-

Access.

>> The way I'm doing it now is by adding fields
>> like 'LastUpdated', 'UpdatedBy', etc. to each record in
>> the tables. I'm sure this method isn't too effective (
>> previous values for the audit fields are overwritten) or
>> efficient (tedious if there are many tables).
>>
>> I very much appreciate any advice/pointers on this
>>
>> Thanks in advance,
>> melody
>>

>
>
>.
>

[Ray at]

Certainly better than no response. :]

Ray at work

"melody" <anonymous@discussions.microsoft.com> wrote in message
news:04dc01c3c9d0$080bfdd0$a301280a@phx.gbl...

> Hi Jason,
>
> Thanks you for the information, the article was certainly
> helpful to me :-) However I missed your post (and a few
> other people's too..) for about more than a month, so I
> hope you can excuse the late response.
>
> Thanks again,
> Melody

[Mike1500]

Hello All,

I have a form and I'm wondering how I can create an audit trail. The user will
have a userID once login the website. The question is how can I insert that
userID to another record in a different table in my database? Once the user
submit the form I want to capture their userID and store it along with their
information. Please let me know how I can perform this task. Any advice would
be greatly appreciated. Thanks.

Operating System: Windows 2000
Language: ASP
Database: SQL Server