Re: Creating an Audit Trail
I have a multiuser CSR application that has some audit functionality to track certain changes to customer records (for instance change of first name or last name, who changed it and what was it before?). It already logs these changes, but I think it could be more efficient. I was wondering if anyone could comment on how they would go about doing this. Right now, it is strictly code-based. The database is SQL Server 2000 and the server is ColdFusion 8. Make use of triggers for INSERT/UPDATE/DELETE. For SELECT I use stored procedures that log the request and then execute it. The downside of using triggers is that there are no parameters. You can overcome this by adding a LastChangedBy column and using it in the trigger. That way I let the database engine do what it does best. Hatton ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292398 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Creating an Audit Trail
If you're using anything like the ColdSpring framework then you can apply this stuff using AOP. I've only recently started working with this concept but it really is a fantastic one. Failing that then I would defiantly recommend building a LoggingService CFC which handles all the logging for the application, it allows you to keep the logging methods consistent across the application. The logging class I've been working on is based on the concept of being able to log to both file and database, with different log levels, such as info, warning, error, critical, debug etc. Without knowing a little about how your current solution is built its hard to advise really, how does the current one work? Do you perhaps have a code sample? Hope that helps, Rob -Original Message- From: Eron Cohen [mailto:[EMAIL PROTECTED] Sent: 31 October 2007 13:56 To: CF-Talk Subject: Creating an Audit Trail I have a multiuser CSR application that has some audit functionality to track certain changes to customer records (for instance change of first name or last name, who changed it and what was it before?). It already logs these changes, but I think it could be more efficient. I was wondering if anyone could comment on how they would go about doing this. Right now, it is strictly code-based. The database is SQL Server 2000 and the server is ColdFusion 8. Thank you ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292403 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Creating an Audit Trail
Our apps have what we call session messages. Usually they're messsages returned to the user after performing an operation (stored in session). This might be something like The user 'Heldt, Ryan' has been created. or The product 'Kitchen Sink' has been deleted. Almost every action generates a message like that. Anway, what I've done before is log those messages along with the UserID performing the operation, date/time, IP, etc. Granted it's not as granular as keeping track of which fields changed, but it can give you a pretty good idea of who was doing what in case there is a problem later on. Ryan -Original Message- From: Eron Cohen [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 31, 2007 8:56 AM To: CF-Talk Subject: Creating an Audit Trail I have a multiuser CSR application that has some audit functionality to track certain changes to customer records (for instance change of first name or last name, who changed it and what was it before?). It already logs these changes, but I think it could be more efficient. I was wondering if anyone could comment on how they would go about doing this. Right now, it is strictly code-based. The database is SQL Server 2000 and the server is ColdFusion 8. Thank you ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292402 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Creating an Audit Trail
Thanks everyone for your comments so far. The current app doesn't have any standard framework, eventually it will probably be rewritten with one. For now I just am taking care of the existing code. Currently when the page loads, the values from the database are put into a variable and then when the page is saved a new list is created and then compared to the first list for changes. Changes are logged in a table with the user's id. It is klunky. I think I will see if we can arrange for the database/trigger route instead. If you're using anything like the ColdSpring framework then you can apply this stuff using AOP. I've only recently started working with this concept but it really is a fantastic one. Failing that then I would defiantly recommend building a LoggingService CFC which handles all the logging for the application, it allows you to keep the logging methods consistent across the application. The logging class I've been working on is based on the concept of being able to log to both file and database, with different log levels, such as info, warning, error, critical, debug etc. Without knowing a little about how your current solution is built its hard to advise really, how does the current one work? Do you perhaps have a code sample? Hope that helps, Rob I have a multiuser CSR application that has some audit functionality to track certain changes to customer records (for instance change of first name or last name, who changed it and what was it before?). It already logs these changes, but I think it could be more efficient. I was wondering if anyone could comment on how they would go about doing this. Right now, it is strictly code-based. The database is SQL Server 2000 and the server is ColdFusion 8. Thank you ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292408 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Creating an Audit Trail
I have done a lot of research into implementing audit trails and agree with what Hatton suggests. Although it depends on the importance of the audit trails. If the data is sensitive in nature or has legal implications attached to it, then it is better to control auditing at the database level, which helps log things if some database administrator or rogue employee tries to update the database directly or bypass the official application. It also protects you if some new programmer comes onto the project and doesn't realize they are supposed to be adding audit-related code. If you just want to track changes as a subtle feature, such as keeping track of the previous email address whenever someone updates it, then that is much easier to do in code. Adding all the database triggers and stored procedures involves significant work. I will say that having an audit trail has been of great help when troubleshooting problems and resolving disputes as to who did what. It is great to know that every single change is logged so I can speak with confidence when someone asks me to research an issue. It is a wonderful feature to have if the business user is willing to make the investment. Good luck, Mike Chabot On 10/31/07, C. Hatton Humphrey [EMAIL PROTECTED] wrote: I have a multiuser CSR application that has some audit functionality to track certain changes to customer records (for instance change of first name or last name, who changed it and what was it before?). It already logs these changes, but I think it could be more efficient. I was wondering if anyone could comment on how they would go about doing this. Right now, it is strictly code-based. The database is SQL Server 2000 and the server is ColdFusion 8. Make use of triggers for INSERT/UPDATE/DELETE. For SELECT I use stored procedures that log the request and then execute it. The downside of using triggers is that there are no parameters. You can overcome this by adding a LastChangedBy column and using it in the trigger. That way I let the database engine do what it does best. Hatton ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292415 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
