By Ross L. Rooker, Sr. - 6/14/2011
I have to report back to our development team on Thursday on the auditing within SF. I have seen many threads but need to know if the auditing (as far as saving data to the audit tables) is working within the SF Framework. If so what we need to do to make it work. We can build our own viewer if one is not available yet.
The SFSAuditDataChanges, SFSAuditDataFields, and SFSAuditEvents are deployed to the same database as my other RBS tables.
I am assuming I need to turn on Auditing by user (although I really want this turned on for ALL users). In the security I can't find where I set this property. Where is it? Can it be set programatically?
Next I am assuming I need to set properties on each BO such as "AuditDataChanges". What properties need to be set on the BO?
Do any properties need to be set at the column level?
If these properties are all set, will the SF Framework automatically track new rows added, column changed and rows deleted in the 3 audit tables above? Or is there something that is needed to be done in addition to the above?
Is it possible to set a property the track changes and deletings but not additions?
Above my questions relate to tracking columns in tables and not events. IE.... tblCustomer.CustName
Finally, if I wanted to send a record to the 3 tables above such as "Attendee attemtped to register twice", what would be the method to have this reflected in the audit tables?
|
By Ross L. Rooker, Sr. - 6/14/2011
Also as an FYI, I do see where Auditing can be set in the Security Editor but these never seem to be enabled in the interface.
|
By Ivan George Borges - 6/14/2011
Hi Ross.
Yep, I have been using the Auditing feature extensively and am able to get the results I need.
I rolled my own viewer and basically I am using the InsertAuditEvent to log an Audit event whenever and wherever I need it.
'-- insert Audit entry for the Unhandled Exception
MicroFour.StrataFrame.Security.Auditor.InsertAuditEvent(lcEventKey, _
RetrieveTextValue("PFNTText - UnhandledException"), _
New System.Text.ASCIIEncoding().GetBytes(e.UnhandledException.ToString()))
So, places like AfterAddNew, AfterDelete, AppMain.UnhandledExceptionFound and so on will usually be the places I use for Auditing.
|
By Ross L. Rooker, Sr. - 6/15/2011
Thanks for the overall info but it would be helpful if you could answer the following questions:
I am assuming I need to turn on Auditing by user (although I really want this turned on for ALL users). In the security I can't find where I set this property. Where is it? Can it be set programatically? Whenever I see it in the Security Editor it is disabled.
Next I am assuming I need to set properties on each BO such as "AuditDataChanges". What properties need to be set on the BO?
Do any properties need to be set at the column level?
If these properties are all set, will the SF Framework automatically track new rows added, column changed and rows deleted in the 3 audit tables above? Or is there something that is needed to be done in addition to the above? If not, then what else do I need to do?
Is it possible to set a property the track changes and deletings but not additions?
Above my questions relate to tracking columns in tables and not events. IE.... tblCustomer.CustName. Just an example with tblCustomer.CustName would be helpful.
Is there any writeup step by step to implement this feature?
|
By Ivan George Borges - 6/15/2011
Nope, I am not using any of these nor setting anything at the Security module. I am programmatically deciding what I want to audit and then adding an event to the Audit tables through the InserAuditEvent method.
|
By Ross L. Rooker, Sr. - 6/15/2011
You mentioned " I am programmatically deciding what I want to audit and then adding an event to the Audit tables through the InserAuditEvent ".
Is the reason you are not setting these as properties in the Security and BO because this was not fully implemented into the framework?
If the above does not work and as you pointed out that you are not using them, could you given me an example assuming 5 columns in a table that has a bo. IE.... tblCustomer with 5 columns. Where would you need to insert code to track changes that could show before and after, deleted rows, and added rows.
What I am asking here is what event code would need to be added and the also what that code would look like. I know that the best place for this code would be in a base bo and then have the bo's inherit from the base bo but to simplify my question just show me a single BO.
I am okay on what you showed on tracking the events. Now I need some on enumerating through the columns of a BO and determining if the column data has changed and if so then then command to send it to the log files. I also know that this would be more efficient done on the database, but need to know about the BO approach.
|
By Ivan George Borges - 6/15/2011
Yep, the original reason was that, and from then on I never got back to trying differently, so this is the way I am handling it.
This post will show you an example of before/after field values that you could use to check changes if you will audit that:
http://forum.strataframe.net/FindPost11127.aspx
As for deleted rows, added rows, I guess the BO's AfterDelete, AfterAddNew, AfterSave and so on would be the places. A base BO certainly would be wise for the task, and you can check the BO.TableName and properties like that to decide whether you want to audit or not.
|
By Ross L. Rooker, Sr. - 6/15/2011
Then is it correct to assume at this point that the SF Framework doesn't check all of the columns under the hood to determine what was changed in a row to update the log tables? IE... we would need to iterate through the current row and determine if a column changed and if so then pass the info to a command in SF to add a rows to the SFS tables?
|
By Ivan George Borges - 6/15/2011
I wouldn't be able to tell you if that is correct to assume currently, I would have to check with the guys to confirm it. I just gave you the way I am dealing with it.
|
By Ross L. Rooker, Sr. - 6/15/2011
Please check because our team needs to know what direction we are going to take and need to know what SF can do now or realistically in the next few months.
|
By Ross L. Rooker, Sr. - 6/16/2011
Trent, I have my meeting today with my Dev Team at 2PM and I need a definitive answer to this thread. Does Auditing work or not as advertised? If so, I need to know and how to make it work with any small example. We do not want to waste our time trying to chase something that does not exist.
|
By Trent L. Taylor - 6/16/2011
Ross,
There is a reason that the "just check it" approach hasn't taken hold. We don't even use it ourselves in the medical world due to several factors:
- The check it and forget it approach would produce records client side, a lot of records, then have to move those records to the server. This in and of itself would be horribly inefficient as there would be WAY to many audit records produced for every live record.
- Ambiguous and monotonous data would be created making it very difficult to filter through.
- Prevents triggers and/or client side threads outside of the BO basic logic to be implemented improved performance and reducing overhead.
In our medical application we audit a lot. But it is thought out and implement where it is necessary instead of just pulling the "log it all" card. If you want to log it all, then get a SQL Server side auditing tool that interfaces at the server level that will do this for you. But if you want to create intelligible viewers and log records for your client to use daily, then you will want to think about what you want to log. Below are a couple of screenshots from our medical application:
In our event viewer (1st image) we have many events, but they are organized and thought out. This includes field level changes in different areas of the application that captures. But some of these are not turned on by default and is an option within our application:
As you can see in one of our audit options screens, there are warning signs next to anything that will produce a vast number of audit records. They can turn it on, but it is an option. You can wrap security around this as well. But it is extremely important to think out your audit approach and needs.
You can combine approaches too. You can slap an SQL auditor on the SQL side to catch everything. Then you can get more pointed and specific on the app side.
Hope this helps.
|
By Ross L. Rooker, Sr. - 6/16/2011
Thanks Trent. This gives me and our Team what we need for our meeting.
|
By Ross L. Rooker, Sr. - 8/24/2011
As an FYI I finally completed the Auditing for New Rows, Changes and Deleted rows using a BaseBO and the property AuditDataChanges on each BO. Then created a UserControls using an Infragistics grid that can be instantiated to any form's panelManager with a single line of code and of course passing some parameters to it to filter what shows in the grid.
|
By Guillermo Vilas - 9/15/2011
Hello Ross, you PM me a while ago, I´m unable to replay from forum system, error message said unable to find user or user doesn´t exists.
|