I just had another inquiry (in a recurring series) about the possibility of maintaining some kind of audit trail. And having given it some thought, I figured it might be worth leaving a few comments here in case others want to express an interest. (None of the inquiries in the past have resulted in anything being implemented inside A-Shell, but as the issue seems to be coming up more often, that could change, and it certainly could be affected by the level of interest.)

Typically, these requests are driven by one or more of the following objectives:

1. the desire for mid-day incremental backups

2. a requirement to export changed records to an external system

3. security/management concerns (i.e. ability to identify/audit changes to a database).

There are many obstacles in designing/implementing a general-purpose audit trail that provides sufficient information to be useful, without being so complicated as to destroy performance or just being too difficult to configure or use with confidence. There is also the problem that generally speaking, A-Shell has no idea of the semantics of your data. So it's nearly impossible for A-Shell to satisfy all auditing requirements with the application's help, although it might well make the creation of a useful auditing facility a lot easier to implement (vs. the application having to do everything).

In the latest go-around, the idea is simply to maintain a list of each logical record that has changed, i.e. been written to (file id, record #), based on a list of files to be audited (perhaps specified in the miame.ini).

This limited information would allow you to easily satisfy #2 above, and possibly #1, by supplying your own logic to process the audit trail data.

It might also get you part way towards #3, although it wouldn't be sufficient to generate a report containing information like:

"At time T, field X was changed from X1 to X2 by user U running program P".

If anyone is seriously looking for something like this, now would be a good time to discuss it further. I would say there is about a 50% chance of something like the above being implemented in the next 90 days, but the odds, timing, and details are subject to developer input.

I keep an audit trail, of sorts, that simply tracks information that has been looked at or updated for HIPAA. Right now I keep time, user name, program name, patient name, and an activity code that tells something about what was done with the record (updated/displayed) and what record it was. I can generate a report by user name, date range, etc. It has grown to be almost of no use and the file has become so large it is virtually unmanageable with millions of records of almost useless information.

Of course the "At time T, field X was changed from X1 to X2 by user U running program P" is what would be great, but how to get there is certainly daunting. To do this, the file layout would need to be know and a name and type for each field of interest. I would also need the ability to tell if someone had looked at a record. Of course, you wouldn't know if a field had been display or not in A-Shell.

I would use a built in audit trail as described in #3, but how to get there I am at a loss as I was when I had to implement my version in a couple of days.

Sorry for the rambling, but I'm really not sure how to get there, just that I would use it if it had the information I needed.

I also think that it should be on a file by file basis. Maybe in the open statement have a flag such as audit'trail that involkes it. This may be a given and forgive me if it is.

Regarding how to activate it, I was thinking it would be better to use something like AUDITFILES=fspec in the miame.ini, where fspec was a file containing a list of the files to be audited, along with some kind of numeric file ID to use in the audit trail for compactness and ease of interpretation later. The list can be loaded in memory and then checked automatically whenever a file is opened (which won't add any measurable overhead; in fact we already have something like this with the MMAPLIST= feature.)

That approach eliminates the need to modify the compiler, runtime, and application source code, which I think is a good way to get started.

It might also be possible to allow the contents of the audit file to be configured via options on the AUDITFILES= directive, allowing you to specified whether you wanted time stamps, user id, program, etc.

But, getting to the field level would require another big step. I suppose each file entry in the AUDITFILES list could specify a record layout definition, allowing the WRITE routine to first read the existing data and compare it with the data to be written, field by field. I'm not sure how much that extra read-before-write would affect performance.

I like your idea of using the miame.ini file.

Field level could be limited to only the fields you are concerned with such as S[12;23;Patient name] or S[12,34;Patient name] for a string starting in position 12 with a length of 23 and a label of "patient name". Something similar to a map statement might be good:
Numeric file ID=
S,23,Patient name
B,4,Drug cost

Would it be faster just to save the last read and when you write compare it to what is saved from the last read? Is there still a way to write a record without reading it first in some applications? What about new records?

I suppose it would shave a few microseconds from the operation if we limited the fields to be examined. And it might help eliminate some of the unnecessary clutter in the output (assuming that fields were being changed that you didn't care about). But it wouldn't simplify the implementation any, since once we come up with a syntax for describing the fields and logic to parse it and compare the data, it won't matter if we are dealing with 1 field per record or 100. Which is not to say that it isn't a good idea. The other alternative, which avoids inventing new syntax, would be to just link to actual MAP statements, but that may open up complications (such as when the MAP statements contain overlays.)

As for saving the last record read, it may be more trouble than it is worth, since there is no law requiring an application to read before write. Or to only write the last record read. You might read 100 records, then rewrite the first and twentieth, followed by a new record which was never previously read.

On the other hand, comparing what user X wrote to what the same user previously read, may be different from comparing what user X wrote to what was in the file immediately before the write. (The difference being that maybe someone else managed to change the record in the interim.) Then again, trying to check the contents of the file immediately prior to a write is subject to race conditions (which I wouldn't want to have to add locking logic to deal with), while comparing it to the data previously read by that user is not subject to race conditions (although it is subject to the other complications just noted).

You may have inadvertently just rediscovered one of the reasons why this project gets stuck in the mud before ever getting out of the starting gate.

A file change audit trail sounds like a very useful feature. Perhaps, all A-Shell should do is save recs Before and After each write. A-Shell needn't compare any data, just prepend a date/timestamp, jobname, and ashell login-id to each rec. Then developers could write their own programs to read through all or part of audit trail files and do application-specific comparisons as needed. Herman's idea of having the option to save only portions of a rec is good, but if performance and disk space aren't issues, saving entire recs offers more potential and would be preferable.

Addendum: It would be important for the prepended info to include the program name, too.

Interesting idea. The one problem I can see (assuming massive audit files aren't an issue) is that this would require the audit file to be in binary format (and have variable length records). That isn't necessarily a show-stopper, but it would make working with the audit files quite a bit more challenging, since you would have to use some kind of raw input mechanism to separate the headers or tags (time stamps, context info) from the data.

Also, if the objective was #1 or #2 rather than #3, there would probably be no extra value to all that data, since you would generally only be interested in the current value of the records that had been updated. On the other hand, have the actual data in the audit file would overcome one of the possible problems with just storing the record number - the data at that record could have been changed between the time the audit file was sequestered, and the time that it was processed. In many cases that might not matter, particularly if the audit file processing took place immediately, but it does add another possible disconnect between the reliability/precision of the data and the expectations of the user.

Sounds like some positive and interesting "Auditing" feed back here or maybe a Can of Worms? :rolleyes:

We currently after "2. a requirement to export changed records to an external system"

From our point I think we could just have the file name and record numbers changed at first I was just thinking something like STOCK.DAT updates an STOCK.AUD (in the same ppn) with a list of record numbers changed, and when I copy the file (along with the datafile) to the PC/Server with say Madics DVerto I then delete the audit and start again x minutes/hours later, but all your thoughts/suggestions have have solved and improved my first basic idea.

Field level audit is a nice idea but can see that a whole new ball game and a COW , there again as I think many of us would use it if it was there, We have been asked for many more audits over the years and have dozens and dozens already but this would be an easier way to implement many more.

My 2 cents would be towards #3 above... as in Herman's case... but this being the most difficult to achieve due to complexities of the individual data records themselves.

Steven Shatz' idea of saving before/after records is a good start here... and the program could deduce the changed variables from this.

If this becomes a data/cpu hog... it could be added as an application option that requires minimum server and storage requirements.. but as was said.. these are cheap compared to the wealth of data acquired from the data.

My interest in saving only the portion of the record that changes was due to the amount of data we are going to have to weed thru if we save everything. I now only save a small amount of data after the record is updated, but it is totally out of control. I don't think anyone could really get anything useful out of it because of the overwhelming amount of data.

I also got to thinking about the program name. In my case, I call a lot of sbxs from my main menu, rxmenu, so all I would ever get is RXMENU!

I agree Herman, If ashell could deduce what variable changed that would be ideal, i didnt think it could do that... unless we provide ashell with a channel and a map?

Or ashell could report the variable offset and we could write the program to show the before and after values?

OK, so you want to be able to define an audit format specification, allowing you to specify which bits of information you want recorded with each audit record, something like:

...

from which we create an audit trail, perhaps in CSV format. That way you could omit the fields you don't care about.

I'm inclined to agree with Herman that recording the entire record, before-and-after each update, might just get too out-of-hand, even in the modern world of multi-TeraByte drives. (It's not only a storage issue, but a performance issue - that would approximately quadruple the amount of disk bytes written.)

Of course the trade-off won't be uniform across applications and record sizes - a 32 byte record would more efficient to handle as a single 'field', while a 4096 byte record probably won't. But you could always supply your own record 'map' which treated the entire record as 1 field.

I'm also thinking that even at the cost of increasing the processing and audit trail storage overhead, formatting the audit trail in CSV would probably be a worthwhile convenience, almost completely eliminating the need to write endless numbers of audit-trail parsing routines.

There is a bit of a complication though in how to handle multiple field changes in the same record. We could output repeating sets of fieldname,data-was,data-new columns, but unless we set some reasonable limit on how many there could be, it would be very difficult to analyze the data. For example, how do you search for changes to field X if field X could appear in any of an unknown number of columns? Also, consider that any new record would by definition involve a change in every field, so in a 4096 byte record with 250 fields, that could make for a long audit record, or a ridiculous 250 individual field-change records. This almost begs for database 'normalization', splitting the audit trail into tables to avoid redundant repetition of information.

Confidential to Ty: maybe this would be a good way to stimulate interest in ASQL. If we use it to implement the audit trail in a MySQL database, we get to sell a copy to anybody wanting the (otherwise free) data auditing subsystem?

Going back to the formatting, we'd definitely have to come up with some kind of record-definition syntax that would allow you to specify how binary fields are to be formatted. (That could be difficult with some of the crazy binary date and other encoded formats, but if all else fails, you could always just output it in hex and then do your own conversion later.) Such a scheme would also allow you to map out sections of records where you didn't care about changes. (The way the world is going, where everyone wants more of everything, that may not be necessary, but I could imagine a situation where the management just wanted an audit trail indicating any changes to, say, customer credit limits. Plopping a 50 thousand page spreadsheet of all changes to all fields at the end of the day might not be all that appreciated.)

I must say it's fun to design the perfect system in a hypothetical world. Implementation, on the other hand...

Just to clarify on how A-Shell would detect changes, I think we'd have to rely on some kind of record definition mechanism (similar to MAP statements, but with additional information about how fields are to be interpreted/formatted, what to call them, and which areas of the record to ignore).

Merely identifying an offset (file #n, record #r changed at byte positions 51, 93-96, 127...) would be easy enough for A-Shell, but kind of a mess for you to have to interpret. Not that I'm trying to deprive you of programming challenges, but it seems that since all of this auditing stuff can be handled by each application, the only reason for A-Shell to do it for you would be to free your time up for more interesting projects (selling new systems?)

Then again, one man's burden is another's joy. And I sure don't want the AADU (Ashell Application Developers Union) accusing me of stealing their members' livelihoods!

Very funny... Actually, i decided not to pay the AADU dues in 2010 in preparation for the increase in the new health care tax i will have to shell out...

With that being said, i like your:
...

proposal a lot... perhaps on a new record, we could ignore or flag off somehow the audit.. there would be no reason to record the fact that all 250 fields went from null to something!

As far as multiple changes to the same record... we could go with multiple rows, or just multiple fields in the same row...

Im not sure how difficult that would be for ashell to do.. but would be a great tool for us.. and something we could use heavily.

Thanks for the consideration.

1. I don't think we need a "fileid" field. It would be more useful to give A-Shell (via separate miame.ini statements), the full file spec for each file we want audited. Then A-Shell can track changes to each file separately.

2. If you save entire records instead of just a few fields, you would have more flexibility at a later date to revise your original audit file processing specs (eg: what changes did this disgruntled employee make to the system) or to restore lost data (eg: an undelete option and for the more ambitious among us, journaling).

3. I think it would be important for the audit trail to include new records and deleted recs. Perhaps there could be a transaction code to indicate Added, Changed, or Deleted.

4. Though it would be nice for A-Shell to do half our work for us, why put the burden of identifying record changes on A-Shell? It's easier for developers to identify field differences and more importantly, we know our own file record layouts. Some files have multiple record layouts. We also know when record layouts change - which would add complexity to A-Shell if it had to keep track of that as well.

5. I don't like the idea of saving records in binary or hex. Would it be possible to save them as "X" type fields? That is, save each record in a format that when input into an A-Shell program, would still match the corresponding map statements? No matter how the audit file data is stored, perhaps all we need is an SBR to retrieve each record. The "data-was" and the "data-new" would arrive as 2 separate records, with the control info as a 3rd. A-Shell would do whatever conversions were necessary behind the scenes, but we would end up with records that conform to their original map statements.

Overall, I think this would be a very useful feature. Perhaps you can give us enough options, so we can efficiently define audit trail usage for our own purposes? Some audit files can merely save control info. Others can save entire records. And still others can save portions of records.

Not to be too greedy, but perhaps you can allow for "hooks" into the audit process, in which a program can determine whether to save a changed record or not. I know this would kill performance, but it would make the resulting file smaller and prevent A-Shell from having to make such decisions.

wow leave the office drive home in the slush and snow and get home and find this has moved on a dozen chapters :rolleyes: maybe I should not have tea or go to bed until the West coast US does at this rate.

All all sounding good to me, especially as I was only originally needing "what records have changed since x time ago" and the format
sounds like it would do the job as I could pluck out the file and recno, maybe a blank or some ID somewhere of -1 if its a new record? and sort the file to filter out any duplicate writes.

Only two question (well two so far) spring to mind:

1) A way to delete/purge the audit, or maybe having a rolling file set at x size limit, but also having a method to identify where last time we check for updates but i guess the field would over this.

2) Thinking ahead, what if we reorganise a good old random data file so all the delete records are removed and all the records are shuffled up, we could end up with a billion zillion trillion dillion audit records, the other option maybe an XCALL MIAMEX,Audit Off/On option along with an XCALL MIAMEX,Audit Reset etc etc what we call in the Reorg/Expand Program. (just a thought, but sure your all think of something better) :rolleyes:

Now, off to get warm and have some tea.

This is starting to sound more and more like an ISAM file all the time...

Or like Jack said earlier... a full blown SQL database....

On that note.. go make yourself a nice hot toddy..

I decided to let Steve get some rest before putting more wood on this fire. But now that he's safely in bed (or maybe just waking up), let me try to respond to Steven's 5 points:

1. On the 'fileid' field, this would only be necessary if we had a single audit file, which is what I was leaning towards, although if we decided that having a separate file.AUD for each file.DAT, then indeed, that could be dropped. But in the consolidated audit file case, we certainly wouldn't want to have record the full filespec of the file for each record update, so the idea would be to reduce that to some kind of numeric ID that cross-referenced with the list of files to be audited. As I mentioned previously, I think the MMAPLIST= concept would work well for identifying the files to be audited.

2. Full records vs. fields: I can see you're not going to just give up quietly on this issue. I can also see there is some merit to each approach, depending on the circumstances and objectives, which are obviously going to vary. I'm going to have to keep thinking about it...

3. Adds/deletes vs. changes: Indeed, it would be nice to identify these, but unfortunately, there is no universal way for A-Shell to know whether a particular write represents an add, change or delete, except in ISAM files. For custom random files, a "delete" might not even involve a write of the the record being deleted. And even if it does, it may be hard to identify a "delete" since it may just consist of a few bytes written. And "adds" are likely to look like changes where nearly every field is changed. So these are potential problems which may require the application to somehow assist in identifying these operations. (Which opens the door to the question: if the application has to tell A-Shell what it is doing in order for A-Shell to audit it, why not just have the application audit itself? Answer: I'm not sure.)

4. Wisdom of just having A-Shell log the entire before/after data and letting the application decipher it later: All good points. In particular, the problem of multiple record layouts would add substantial complexity to any scheme for defining record layouts for human-readable logging.

5. Wisdom of recording raw "as-is" (X-format) records, as opposed to some kind of human-readable formats: Again, you make good points. We could (we'd have to) create an XCALL to allow you to read the audit data, one logical transaction at a time. You could use this method to read the raw records back into their original record structures, without A-Shell needing to know anything about the record data other than its length. Upside: maximum information availability allows for maximum flexibility in deciding how to use the data. Downside: quite a bit of work on your part to make use of the audit data, and potentially huge data overhead.

Indeed, considering the variety of approaches even amongst the few people engaged in this discussion, we would certainly want to come up with a scheme that provides a lot of flexibility. Unfortunately, the raw before/after record format you propose is more than just a variation on the human-readable/CSV scheme I was suggesting - it's an entirely different kind of approach. That's not to say that we couldn't implement them both, but obviously it increases the size of the hill to climb.

On the "hook" idea, we once discussed the possibility of implementing a hook for file i/o operations that would allow you to essentially execute an SBX or custom function before or after each file i/o operation. Depending on how much work you want to do on the application side, the existing XREAD and XWRITE replacements for READ and WRITE lend themselves to creating your own function wrappers for READ and WRITE operations, where you could have all of the special custom logic you wanted. But my sense what that recoding your file i/o statements would be such a major undertaking that few would do it. A hook into the existing READ/WRITE would perhaps make it easier. Seems like we discussed this once elsewhere in this forum...? Anyway, a lot more pondering is needed...

Now, on to Steve's two questions:

1a. How to delete/purge the audit trail or figure out where the "checkpoint" was: My thinking here was for you to just rename the audit file(s) when you wanted to process them. The implementation would have to detect this and start new audit files. (At least under UNIX, this would be fairly straightforward since files can be renamed even when in use. Under Windows, it would be a bit trickier, perhaps requiring some kind of handshake signal and a system-wide delay of several seconds while all the jobs were forced to close their audit file(s) and opening new ones.)

2b. File reorganizations (or sorts): Yes, this would be a nightmare scenario for the audit trail. In the case of a sort, some kind of special entry indicating all records written could be automatically generated. But for a custom file rebuild, you would have to use some kind of new XCALL (just as you suggest) to inform A-Shell to save a file reorganization transaction and then stop auditing until further notice, rather than recording zillions of useless "change" transactions. But this again raises the concern posed in #3 above regarding how much we want to ask of the application and how much of A-Shell.

All sounding very good to me.

One thing that keeps coming to my mind is the expansion of the size of a field or record. I do this on occassions were standards are changed in the industry that might increase the size of a data element, maybe even change a numeric value to now allow negative numbers, or simply add so many new data elements that the record size would have to increase. Right now, with just storing elements of the record as I do, this works nicely. I'm not sure what would happen if the entire record was to be stored. Just thoughts, regretfully no answers.

MOOOoooooo........... :p

Herman that sounds like it falls into the 2b. File reorganizations (or sorts) category (we still have to do that time to time, but far less lately). So I guess we would have to do something like send the audit "special entry indicating all records written" indicator in the expansion/conversion program + update the audit data file map structure. - Well just thought.

I think this is getting a little off base ... again, Jack's original suggestion of:

...

Seems the most straight forward and most flexible... it can be viewed, reported on and programs written around...

Herman's latest concern reveals another potential dichotomy in the way one might expect to use the audit trail files. It sounds to me that Herman's main objective is compliance, meaning that some regulatory body wants him to maintain records of who changed what over long periods of time, just in case they want to do a security audit. In that case, the ideal would be to not have to do any processing of the files - just copy them, say, once a day, to some archive location. To handle the possibility of a change in data layouts, you'd also have to copy all your MAP structures at the same time, which might be a little bit of a hassle, and would certainly complicate the task of writing some kind of data mining utilities later (should someone really want to investigate who changed my monthly prescription from viagra to ex-lax).

For this kind of application, clearly it would be advantageous to be able to just turn over the audit files to the auditors (saving you a lot of work under pressure, as well as eliminating any question about whether the audit files are being manipulated by your ad-hoc programs to interpret them). And, obviously it would be nice if you didn't need to hire a team of out-of-work data technicians for a week to copy your 87 TB of audit files from your hard drive juke box to 145 thousand CDs to give to the auditors.

On the other hand, Steven (Shatz) would probably argue that the best audit trail is the one with the most, least-filtered data in it, since you may never know for sure how it is going to be used. If we try to save space and external processing by outputting just formatted (printable) data from fields that changed, we might later miss some clue that would have been critical in a forensic investigation.

As an aside, if you really want to maintain sufficient information for security audits, you need to keep track of who even looked at a record. This, after all, is how they nail the hospital workers who can't help themselves from sneaking a peak at (and then selling to the highest media bidder) celebrity medical records. So for Herman's 500+ MB prescription file, let's figure that if the file is scanned for searching purposes a few dozen times a day, we'd have maybe 20 GB of daily audit trail records from that alone, not even counting any changes! Yet clearly the actual field-level data being read in this case isn't of much importance; moreover, just because the program had to read a record to determine it didn't fit a particular search criteria, is that the same as "accessing" the record? This points out that it is not only hard to make these kinds of distinctions, but probably impossible to do at a level below the application. Which brings us back to the possibility that maybe we should just leave all this auditing to the applications?

Steve (Evans), on the other, knows exactly what he wants to do (i.e. what he wants A-Shell to do for him), and that is simply identify all the records that have been written in the last time period, so he can export them to an external but related system. He doesn't need all the raw data, since he can just re-read it from the active file using the record number. But, Steven (Shatz) would probably say that even here, the raw data wouldn't complicate his life (in fact, it would make it simpler, since he could do the export from the audit trail itself, without even having access to the original/current data files), and the problem of accumulating massive audit files would be easily addressed by just deleting them after processing.

Frank may be on to something....

Who came up with the idea of field and not just record number auditing... :rolleyes: dont worry Jack it still a good idea of your, even if it is a COW, ots better than a xmas turkey talking about xmas, we (Madics) break tomorrow (once all our customer have gone home) til the 4th Jan so everyone have a great holiday break (and not too much Auditing).

I think I second Frank's idea.

Reading on, I am beginning to think along the lines of Jack's most recent post

"Which brings us back to the possibility that maybe we should just leave all this auditing to the applications?"

Yes, probably so for me.

Those looking for further reading might want to review this post in which the idea of creating hook functions for the file I/O operations was discussed:
http://www.microsabio.net/ubb2/ultimatebb.cgi?ubb=get_topic;f=5;t=000875#000002

Given how complex this auditing idea is becoming, perhaps the hook approach is worth taking another look at. (Auditing would then become entirely the application's responsibility, but the hook mechanism might allow you a way to implement it without rewriting the entire application.) Warning though: SBX-based hooks would probably be just too much like the tail wagging the dog to be practical. I think they would have to be functions, meaning you'd have to recompile to change them. Unless maybe we defined a scheme to implement them as linkable libraries. Oh, I'm with Steve (let's take the next couple of weeks off!)

this is starting to sound like the latest healtchare bill...

Starts off with a noble and good idea... lets provide healthcare for anyone who either cant a) find it or b) afford it... now who would argue that point? I mean even NRA card carriers living in hidden cabins wouldnt have much to say about it... (er, at least i would hope not!)

And now, its gotten to death panels and free hospitals in Nebraska.. all it takes is a few politicans to completely ruin a simple and noble idea...

Oh ya, that reminds me... audit system... hmmm...

Happy Christmas to all and to all a good nite!

(i think i need an audit system to follow this thread!!)

Do you feel we may start going Round in circles I did think about the sbx/functions in that thread yesterday then read my own post there pointing out to myself we here At Madics still can't compile a good few programs with /x:2 due to reserve words and other issues what I've even forgot about.
What about back to just logging the record number changes while keeping in mind a phase 2 for field auditing next Xmas

free healthcare ? Can't argue against that, well ok maybe paying more tax/insurance a pain but over the past year I can't moan at all about out good old british NHS as pipa been nearly living there with her slipped disc/back and other things over past year or so and they been great and no other costs.

I feel strongly about this also.
I'll print the discussion over the weekend and add a more detailed post.

Essentially I want a MIAMI.INI reserved word AUDITFILES with an address of a file to hold a list of files to be audited. This list (6.3 or 10.3) would load at A-Shell IPL for simplicity of discussion, an ARRAY of filenames that hte OPEN, ADD UPDATE and WRITE statements lookup.
(exclude IDX files)

ASHAUDIT.LOG (right next to ASHLOG.LOG

ASHELL USER NAME
JOBTABLE USER DEFINED FIELD (another discussion)
DATE TIME
FILENAME
RECORD NUMBER (ISAM OR RANDOM) NOT KEY.

Since this is getting complicated, I favor separate implementations of audit files for different purposes, starting with the simplest and getting more complex as you go along.

I see little point in trying to come up with one solution for every possible scenario (although isn't that what INFLD, INMEMO, XTREE, etc. do? If anyone can accomplish this Jack, it's you.)

Anyway, there could be a separate solution for purpose 1, another for purpose 2, one just for ISAM files with purpose 3, another for Random files with purpose 3, and perhaps separate solutions for saving individual fields versus full records. Start simple and build up over time. Whatever you give us, it would be better than what we have now. Thanks for even considering this.

Happy holidays to one and all! May the New Year be peaceful for everyone and may you be blessed with good health, good fortune, and abundant happiness in the year to come.

Hi,

In first place, I want to apologize for being away from this discussion, several times I was about to start writing but, realized that will not add anything relevant because, in my reality, I can't see any straight solution that could bring enough benefits compared with the development hours needed to build a really useful tool.
I'm not saying that this is not useful, I'm sure that who asked for it has real needs that deserve all the efforts to make it work, no matter which could be the final model so, I don't want to become the guy who only point to problems instead of solutions.

This said, allow me to add here some thoughts but, do not consider any of it as an obstacle and, if any, just take whatever could contribute for a useful solution.

Starting with the issue of "where and how to log the file activity", what I would like to have is daily based log files by partition, for example:
DSK1_257001_20091201.LOG
DSK1_257001_20091202.LOG
...
DSK1_903001_20091201.LOG
...
In my case, each customer has different appllications (Invoice, Production, Account,...) in different partitions and, usually, they have more than one area for each application, which means that, one file (e.g: CUSTOMER.IDA/IDX) can exist in different partitions.

On the subject of "how to activate/control file Audit", I would prefer something simple like:
(in the MIAME.INI)
AUDITLOGFOLDER=.... (where to save the log files)

The above statement defined in MIAME, would not be enough, all the DEVICES to audit should be switched to ADEVICE= (Audit DEVICE), which means that, all the DEVICE= would be out the audit control but, maybe OPTIONS=AUDITALL could be used to say that all DEVICES should be audited.

(I'm not going to get into much details about possible inclusions/exclusions from the audit process, regarding partitions/files but, obviously that, if something like this become real, it's something to consider but, easy to solve)

Going deeper, "what kind of data is saved into the log files" but, first, we must define the goal of the audit; is it to allow to recover/rebuild previous deleted/changed data or, is it just to trace who and what did with the data during a certain period?
I'm sure that w'all (is this also correct in LA? :p ) would love to have a magic wand to be able to restore some previous situation keeping the integrity of the overall data but, I'm not so sure that could be achieved without additional procedures to recalculate records relations, totals and a lot of other things so, if we can have something that informs us about the sequence of changes on each file/record, that would be a great step, to start.
So, what I would like to have is something like:
date/time; user; device:[p,pn]; filename; action (new/delete/update); rec size; index key(s); rec buffer

Let me add that, I use ISAM-A and ISAM files and RANDOM files with a single record for application settings.

As for the human-readable data inside the log files, I think that would not be advisable because, it will introduce some complexity in the process, that should be as straight as possible, and would expose private data, what about EFS?

The "rec buffer" part should be saved in X format where, via application, only applying the corresponding MAP structure over it will turn it fully readable and, here is a great project to develop which is, a standard XTREE based program to read/filter Audit Trail log files and identifying field changes between records in sequence.

I'm surely forgetting many things but, if some of the above become useful, following are a couple of things that should be considered:
- one setting to define for how long should log files be kept;
- in the log file, identify the computer name and windows user;
- the critical process here is the log of "updated" records, which means the record was saved but, surely, dozens of records will be logged w/o any relevant data changed. The only way, I'm able to idealize, to avoid this, would be to compare the hash-code (whatever it means) of the record before and after save but, it will not work because, first, there are always one irrelevant bit changed and, second, it will have impact in performance if we are updating a big bunch of records due to a global update process. So, maybe an alternative would be to run a scheduled program to purge the log files from similar records considering the relevant part of the data.


Well, if some of you are still reading this, let me add my wishes of a great 2010 where, hopefully, we will meet somewhere, isn't it?

Could a trigger be implemented per file? SQL has these. Whenever the table (file) is 'touched' the trigger is given user credentials, 'before' and 'after' records as well as the operation performed: create (add), read (select), update (write) or delete. With this capability and information the developer can create his own audit trail in one place and every program 'executes' the trigger automatically.

Just a thought......

Well Jorge, that's usually my job, but you're welcome to help!

I was pretty much convinced that I liked my consolidated audit file better than having a separate for each data file, but your requirement to have audit files that relate to groups (or ppns) of data files pretty much demolishes my plan.

As for the idea of using the audit file to rebuild/reconstruct/recover data, that was never really considered at all. That sets a very high bar, and requires that cross-file 'transactions' (integrated sets of operations on multiple files) be supported, which also means 'commit' and 'rollback'. If that were ever to be considered, it would have to be in "Phase 4" of this ever-expanding project.

But, don't forget that in the case of ISAM-A, we already support Transactions and Logging and Recovery . That won't help those not using ISAM-A, and offhand I'm not sure how appropriate that mechanism would be for any of the objectives which initiated this discussion, but it may be something to consider.

As for triggers, that is essentially a variation of the "hook" idea mentioned above (and discussed at length in the thread referenced previously), and I must admit that it is sounding more and more like the best way to go about this (since it would effectively insulate me from having to consider all the permutations of requirements, while allowing you the flexibility to do a lot of things besides constructing audit trails.) On the other hand, deciding on and implementing a sufficiently efficient and flexible mechanism is decidedly non-trivial.

In the SQL world, triggers are typically implemented with SQL statements. In the A-Shell world, it seems appropriate to for the trigger language to be AlphaBasic, which suggests that they be Functions or SBX calls. Public opinion seemed to favor the latter, since, like triggers, they could be coded and updated independently of the application. But the overhead and complexity seemed daunting to me, because of the interplay between the original application environment and the SBX environment (which in turn could call additional SBXs and perform other file I/O, which in turn could invoke other hooks, ...)

Thinking of them as "triggers", rather than "hooks", however does give me the idea that maybe the way to implement them is via an independent process (in the same sense that the database typically runs in an independent process.) For example, perhaps we could run a special instance of A-Shell which was in charge of all file I/O. For each file operation, it would call the associated trigger (in the form of an SBX) if one had been defined. This would completely separate the trigger/audit logic from the application. But surely it would slow down file I/O by requiring the application to wait for the 'database engine' to get a time slice, process the operation, including any triggers, and return a success/failure packet. (Databases can more easily get away with this extra overhead because database operations tend to be applied to sets rather than individual records.)

This could be simplified in "Phase 1" by having the separate instance only handle logging/auditing, but not the actual data I/O. In that case, the main application instance would still handle its own I/O like it does now, while the external instance only concerned itself with auditing operations (eliminating the need for a two-way handshake between the two.)

Well, it's time to forget about all this for now and go tend to the egg nog.

Thanks to all who have participated on the forum and in the A-Shell community this year - may your holidays be bright and your new year bug free.

-Jack

One quick thought - if all I/O changes are logged to a single file, to group together multiple related file updates resulting from a single action, it would be useful to have application submitted comments or starting/ending indicators logged to the audit file surrounding the related I/O's. Thus, if several users were updating files at the same time, even though their transactions could get interleaved in the log, we could still trace a single user's action by finding that user's start and end indicators. Similarly, if a user performs multiple sets of file updates in a row, we could easily find where one action ends and the next begins. I don't know how we would be able to relate multiple file I/O's caused by a single action if each file has its own audit log.

Sorry I missed this discussion but I was away on a well deserved holiday!

After reading (or should it be wading through) this long topic I'm not sure what the conclusion was? Jack, is anything now planned?

I already have a very basic audit trail and I am just on the point of developing a new system for recording field changes after much pressure from our customers.

The suggestion is exactly what I need preferably with the ability to turn the recording on/off under program control.

My solution was probably going to be a SBX which would be passed whole records before and after modification together with field layout definition to indicate just which parts of the record should be compared.

Obviously if Ashell will be able to provide the trail I need then I'll put this on hold.

Barry

Must be nice to go off on a "well-deserved" holiday while we grunt laborers toil away in the bit factory on problems you hadn't even told us about yet!

Unfortunately, other than a lot of mental calisthenics and visualization exercises, no actual coding on this audit project has yet begun (partly because, as you've discovered in your wade through it, there are a lot of competing goals, requirements and ideas lurking about.)

But as it turns out, your timing is particularly fortuitous, since a certain other A-Shell developer just up the motorway from you happens to also be at that critical point where a decision has to be made - either to develop the necessary audit trail logic entirely at the app level, or, if A-Shell is going to provide any kind of low-level support, aim to interface with that instead.

Naturally, his requirements are at the opposite extreme from yours, but if there's anything we can all agree on in this area, it's that any service provide by A-Shell is going to have to have "flexibility" as its middle name (if not the first and last as well).

So, with that in mind, I'm "about ready" to commit to at least starting down a path towards installing the necessary plumbing to facilitate the kinds of things discussed in this thread, leaving the specifics towards the end.

The basic infrastructure envisioned goes something like this:

1. One or more mechanisms to identify files for which the audit facility is to be activated. (Some kind of INI-type file referenced in the miame.ini, and/or some kind of runtime option.)

2. A set of "events" (things to be audited), consisting of two types:

2a. Built-in or pre-defined events, such as:
- prior to READ
- after READ
- prior to WRITE
- after WRITE
- before or after OPEN
- before or after CLOSE
etc.

(The 'before' events may eventually lead to the possibility of the event hook returning a code indicating whether the operation should proceed or not, thus allowing file operations to essentially be redefined on the fly. But that's several stops down the road...)

2b. Application-extended 'events', by which the application can insert information into the audit stream, such as "file reorganized", "file deleted", "billing run started", etc.

3. Some method of defining just what information is emitted into the "audit stream". I'm still fence-sitting on whether it is better to define some kind of syntax for specifying which bits of information you want (i.e., job name, program name, file id, rec #, contents of record, etc.), or just output (or "make otherwise available") everything imaginable so that you can then pick and choose what's of interest to you.

4. Aside from the content of the audit stream, we'll need some flexibility about the mechanism of outputting it. The choices which come to mind are:

4a. One or more files
4b. One or more FIFOs
4c. One or more TCP sockets

The latter two would require a program (created by the application developer in A-Shell/Basic) to be at the other end receiving the data (and presumably filtering/formatting it at the same time). That approach is a bit more complex, but offers a number of potential advantages:

- Eliminates the problem of accumulating vast amounts of unwanted log data, since you would discard the stuff you didn't want as it came in via the socket or pipe.

- Allows you to move the audit filtering process to an entirely different computer (at least in the TCP case.)

- Allows for the potential of supporting "triggers" (or fully programmable hook routines), although this would require that the original process wait for a response, obviously slowing it down.

Obviously, compared to all this stuff, just creating your own SBX wrappers for the file operations of interest is something you can get started on right away. But I would be concerned that as flexible as SBXs are, they would introduce some complexities that might eventually cause you debugging headaches. (A function would be cleaner, but requires that they be compiled into each program, which might make tinkering with them over time a lot more complicated than you'd like.) So having a reasonably well-defined hook mechanism built-in to A-Shell that could then be filtered/extended/customized by you via one or more standalone programs seems like it would ultimately be the most workable over time.

So I guess it comes down to how urgent your requirement is. But if I were you, I would stall for a few weeks to see if any of these grandiose ideas start to take shape.