Iron Speed Technical Forums
Register Latest Topics
 
 
 


Reply
  Author   Comment  
jjlov

Registered:
Posts: 6
Reply with quote  #1 
I needed a better, uniform way for auditing a database app for a financianl client.  Wanted to trap the log in a single table which could be collated by table, date user id and machine id (I know it isn't accurate outside a LAN but at least you knew that it probably occured there as opposed to inside the LAN).  Then I could use a custom reporting tool to show me the required data in a coherent structured format, as it would be saved in an XML format within the SQL audit record, allowing me to use a single table for all tables.

After doing a bit of research, I came upon something that Bryan Patrick posted last year that led me to this solution.  See attached word doc.  And if you like it, let the folks at Ironspeed know that you would like to see this as part of the app designer tool - allowing you to pick tables from the solution to be audited this way.

Note, that the example does not address insert, delete or multi-row operations (edit table pages and panes), but it does show you a technique to approach the problem.

 
Attached Files
doc Adding_a_Custom_Auditing_Log_function_to_IronSpeed_Apps.doc (43.50 KB, 276 views)


__________________
John J. Lovrinic
Lov IT, LLC
Office: (312) 788-9314
Cell: (312) 659-4534
E-mail: john.j.lovrinic@att.net

miles

Avatar / Picture

MVP Consultant
Registered:
Posts: 1,660
Reply with quote  #2 

The concept of auditing belongs in the business layer, where you only have to place it once.  I would suggest refining your article to get it working there. I think it has promise, but also I don't see how you propose to display the data to users, given that it is stored in XML.

 

Thanks for submitting the article!

 


__________________
Miles Gibson, MScIS
Iron Speed MVP
Senior Consultant, Principal
Milestone Software Inc.

http://www.ironspeedmvp.com\localizer 
Email: miles@milestone.ca
Milestone Localizer: Full Localization for your Iron Speed applications!
porphi

Registered:
Posts: 647
Reply with quote  #3 

I think this would have been much simpler.  To reconstruct the record extract it from the table and recast it.

ArchiveRecord aRec = new ArchiveRecord();
MemoryStream mStream = new MemoryStream();
System.Xml.Serialization.XmlSerializer xmlSerializer = new System.Xml.Serialization.XmlSerializer(recordToArchive.GetType());
xmlSerializer.Serialize(mStream, recordToArchive);
ASCIIEncoding ascii = new ASCIIEncoding();
aRec.Parse(ascii.GetString(mStream.ToArray()), ArchiveTable.FieldToStoreMyArchiveRecord);
aRec.Save();

 

You would be amazed at how much code gets copied from this forum and applied to production projects at least the ones I’ve taken over.  You might want to at lease take the field elements in the XML document that you’re creating and escape them for special XML characters.

__________________
Thank you,
Phil Porter
PPG&A, INC.
pporter@ppgainc.com
http://www.ppgainc.com
https://rapidsprout.com
678-362-2035
jjlov

Registered:
Posts: 6
Reply with quote  #4 

Thanks!  Much better way.  Was going to use XML writer in the final version.  However only want the changed fields so still some work to do.

 

Am also going to put the mods in the  table classes, probably with a table based flag that will determine whether or not this table is audit logged.   Currently converting base source to c# as I hate VB coding.  Probably will have the next installment done in a couple of weeks.


__________________
John J. Lovrinic
Lov IT, LLC
Office: (312) 788-9314
Cell: (312) 659-4534
E-mail: john.j.lovrinic@att.net
jjlov

Registered:
Posts: 6
Reply with quote  #5 

OK.  Made some significant progress.

Gave up on converting the BaseClasses code from VB to C#.  Just not worth the effort.  So redid with VB.  Added a class to to baseclasses called Audit. 

In audit, created an overridable method that will contain logic to do the actual posting of the information to the database audit log file.  Created 3 helper functions (CopyRecordU, GetRecordDataI, GetRecordDataD), the GetRecordDataI function is not required (and not tested), but created as an artifact.
Then rebuilt BaseClasses.

Created a new source file (c# this time), which is the Audit1 class which is added after the project is created to the Baseclasses folder.  This contains the overriding method for writing to the database.  Needs to be tweaked with each implimentation to address the fact the programmer may want to use a different audit log table name for each app, hence that add-in after the app creation.  I recommend that the audit log table be segretated in a separate database from the rest of the database tables for security purposes (every little bit helps).  Haven't found a way to do the file copy and add to the project automatically in the manual, so this is a manual process for now.

Also changed the code generation templates in BaseRecordClass to address record inserts, deletes and updates.  This took a while - again due to the lack of docs on how things actually work.  Ran into a few issues with unassigned entities in unexpected places.

Changed the xml structure and posting technique to bring it up to production standard (as opposed to fooling around).  This included implimenting an xmlstream writer and UTF-16 encoding (for SQL 2008 et al compatibility.)  Added the field data type so we will be able to decode this properly for the report (a later project).  Also made a change to the audit log table definition so that the xml field containing the change data is stored as xml.

Lastly, you have to include the audit log table when you build the app.  However, once the app is created, you must remove all the pages that were generated for the audit log table, or you get some nasty recursion loops going.  Then, when you build the audit log report app, use the original baseclass file so you don't run into the recursion issues.

Have tested this for base table inserts, updates and deletes, and to a lesser extend, for the same operations on rows.  Haven't run into anything egregious yet.  Have not implemented this with a table level implementation flag as I want auditing on all tables for the apps I write, or none at all.

Reasoning for methodology:
Considered doing this in the Business Class, but that would have required getting into the brain of the original engineers a bit too much. Biggest reasons were: 1) addressing the undefined entity issues properly and 2) getting access to the old data in the Save routines (which currently only deal with the updated record, so accessing the old data at this point would have required another database access-which I wanted to avoid), while still having the audit table insert occur in the same SQL transaction as the other file operations that are pending.

New files attached.

Disclaimer:
Use/implement at your own risk.  Recommend reviewing the code carefully before using on anything important.  Haven't done VB in years, so may have missed something.  Feel free to comment and criticize.  Note that the attached files are for Ironspeed 7.1/.Net 3.5/Visual Studio 2008 only.  For any other version, you will have to implement yourself.  So don't just copy the xsl over your original and expect things to work.


Regards,

John
 

 
Attached Files
zip Ironspeed_AuditOption.zip (23.29 KB, 145 views)


__________________
John J. Lovrinic
Lov IT, LLC
Office: (312) 788-9314
Cell: (312) 659-4534
E-mail: john.j.lovrinic@att.net

jjlov

Registered:
Posts: 6
Reply with quote  #6 
Have successfully implemented this.  However, there are some shortcomings:
1. Lacks capture of keys for inserted records.  Issue is you don't know the key before the insert and another read op appears to be required to capture it.  Also, we don't want the inserting app able to read the newly written record.
2. This would best be done as TSQL in the data layer using a set of three generic stored procedures (corresponding to the insert, update and delete default stored procedures for every data table) to accomplish what I did here with VB code.  This would be more efficient (everything server side, with minimal overhead), be able to capture all the data, less subject to tampering, and more reliable (in the case of unreliable connections).  This would necessitiate passing the userid and program name in every table writeable call - which would be a bit trickier to manage, but doable.

However one approaches it, there are issues, however, having it done automatically, during the design/app generation process is still the best way to address this.


__________________
John J. Lovrinic
Lov IT, LLC
Office: (312) 788-9314
Cell: (312) 659-4534
E-mail: john.j.lovrinic@att.net
JimiJ

Avatar / Picture

MVP Developer
Registered:
Posts: 1,954
Reply with quote  #7 
We have recently added a special feature on all our delivered applications that we call it Audit Surveillance because it captures everything the users done on every page of the application using the Business Layer.

[image]

FYI

Jimi J

__________________

  Jaime Jegonia
[jts_logo]

Iron Speed MVP Developer
 

". . . and whoever sows generously will also reap generously" 2 Cor 9:6

miles

Avatar / Picture

MVP Consultant
Registered:
Posts: 1,660
Reply with quote  #8 
I did something similar earlier this year.  Except, I use the ISD object model with generic code so that any table can be used.  What it does is construct an HTML table of all changes to a record (Add/Update/Delete), and then write that HTML chunk to an Audit table in the database.  Then admin users can view changes to the entire record at once via a web interface.

Next steps would be to implement a roll-back feature if requested.

HTH,

__________________
Miles Gibson, MScIS
Iron Speed MVP
Senior Consultant, Principal
Milestone Software Inc.

http://www.ironspeedmvp.com\localizer 
Email: miles@milestone.ca
Milestone Localizer: Full Localization for your Iron Speed applications!
JimiJ

Avatar / Picture

MVP Developer
Registered:
Posts: 1,954
Reply with quote  #9 
Roll-back must be a nice feature as well.

AuditSurveillanceRB.png 

Jimi J


__________________

  Jaime Jegonia
[jts_logo]

Iron Speed MVP Developer
 

". . . and whoever sows generously will also reap generously" 2 Cor 9:6

Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.

Download Iron Speed Designer

Terms of Service Privacy Statement