none
Audit trails to any datasource (both intrinsic and external databases).

    General discussion

  • I have updated my solution for audit trails. It's possible now to handle also audit trails of external datasources.

    My audit trail solution allows to navigate from the audit search screen back to the original record, this requires a kind of weakly-typed API style. 

    The following post illustrates this approach: http://blog.pragmaswitch.com/?p=291

    An example solution is available.

    Feel free to drop over here a line in case something isn't working as expected or if you have suggestions for improvement.

    paul.


    paul van bladel

    Friday, April 13, 2012 2:33 PM

All replies

  • @Paul, very instructive lesson indeed. I find that audit trails, as well as chain of custody and authorization, are crucial for enterprise LOB applications because of compliance issues. I hope to see your solution integrated in VS soon?

    One more question: you are using a client code as well. How would this fit in the new LightSwithc HTML Client? Is it possible to implement generic audit trails server-side-only?

    Thanks

    Friday, July 06, 2012 3:08 PM
  • Hi Paul,

    Congratulations for the initiative :)

    would provide the code in VB. NET also?

    thus cover the whole community.

    Thanks

    Friday, July 06, 2012 5:24 PM
  • Hi Boian,

    Thanks for the feedback. I didn't try yet to incorporate the audit stuff with the current html client. In fact I don't see too much added value for an implementation on a mobile client, currently. I think that consulting the audit log is merely an admin task and thus best suited for the good old silverlight client. Indeed, using the server-code only is no problem. If you use auditing only for "in case of emergency", then you can simply consult the audit table directly in the database.


    paul van bladel

    Friday, July 06, 2012 7:49 PM
  • Hi Marden,

    Thanks for the feedback Marden.

    Sorry to disappoint you, but i never code in vb . There are tools around to convert, no?


    paul van bladel


    Friday, July 06, 2012 7:51 PM
  • We also covered auditing in Pro Visual Studio LightSwitch 2011 Development, using a similar technique, which also had VB code, if that's any help.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Saturday, July 07, 2012 10:17 AM
  • Indeed, check Yann approach as well and not only because there is also a VB version :)

    Allow me to add, from personal experience, that studying his book, can bring your lightswitch skills to another level. Highly recommended !


    paul van bladel

    Saturday, July 07, 2012 8:00 PM
  • Thanks Paul!

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Sunday, July 08, 2012 11:44 AM
  • Hi Paul,

    Thanks for your efforts. I am having one problem; it appears that choice lists and related items are showing up in the Original Values and New Values fields as:

    RowVersion: System.Byte[]

    The actual values are also present when I click through to the detail view but only after this unexpected line. Any guidance you might offer would be appreciated!


    4tuneate

    Tuesday, September 11, 2012 4:14 AM
  • Hi 4tuneate,

    Are you getting this problem in the 2012 version?

    I tried the choice list functionality in V1 and there it works fine. I minor issue in V1 is that in the audit record, for the choice list value, the underlying value is shown rather than the display name, but that' easy to update. 


    paul van bladel

    Tuesday, September 11, 2012 1:26 PM
  • Hi Paul,

    I'm using VS2012. I added a "State" field with a Choice List to your Customers entity. If I edit Credit Limit I see the Original Value and New Value in the Search Audit screen as expected. If I edit the State field by selecting a different State from the autocomplete box, I see "RowVersion: System.Byte[]" in the Original and New Value fields of the Search Audit Trails screen. When I click through to the Details screen I still see this text but the Original and New Values for State do also appear on subsequent lines of the Values fields.

    FYI I also removed the 255 Maximum Length limit from the Values fields of the AuditTrail entity so more extensive changes would not cause an error.

    Thanks for spending your time replying.


    4tuneate

    Tuesday, September 11, 2012 1:43 PM
  • Hi again,

    I updated to VS 2012, but I do not have any problems with choice lists. They function exactly as in V1.

    The solution needs an update for the rowversion field, which is a field added in VS 2012 and has a pure technical nature (for optimistic locking). Therefor, I think that just excluding this field from the auditing, would be just fine.

    I'll try to upload my updated solution later today to my blog and let you know.


    paul van bladel

    Tuesday, September 11, 2012 2:03 PM
  • Thanks very much. I see now why The rowversion line is the only line that displays on the Search Audit Trails screen. The screen is set to display only one line for the Values fields so if there are multiple changes you won't see them until you click through to the details screen.

    The audit solution is great and you have been most helpful!


    4tuneate

    Tuesday, September 11, 2012 2:12 PM
  • No problem at all. It's good that you triggered for me the update of the audit solution to VS 2012.

    It's also nice to experience that upgrading from V1 to V2 goes so smooth.


    paul van bladel

    Tuesday, September 11, 2012 2:23 PM
  • Hi,

    I have an updated sample available here: 

    http://blog.pragmaswitch.com/?p=427

    take care


    paul van bladel

    Tuesday, September 11, 2012 7:10 PM
  • I'm reading through your book now. Great job!
    Monday, September 17, 2012 8:29 PM
  • Thanks!

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Tuesday, September 18, 2012 1:21 AM
  • First of all - thanks for great job!

    All of you posts in your blog are very helpfull.

    About this solution i found 1 "bug"(not a bug - the feature!!).

    When you trying to make more then 1 changes per time to the entity - only the first changed field is audited.

    For example when you change FirstName, City and Country for some Customer only changes of FisrtName column is auditetd in AuditTrail table.

    Max.


    Tuesday, October 16, 2012 6:48 PM
  • Hi Max,

    Thanks for your feedback.

    Need to check the "feature" :) 

    take care

    paul.


    paul van bladel

    Tuesday, October 16, 2012 6:51 PM
  • Ooops. Sorry, sorry!!

    In solutions all works great! All changes are audited correctly!!!

    But when you take a first look to AuditTrail table( in AuditTrailSearch screen) you see a cutted value in datagrid. When you explore details of auditing record - all fine. All changes are stored correctly.

    Max.

    Tuesday, October 16, 2012 7:02 PM
  • This is because you make this thing:

    oldValues.AppendLine(.....

    String field with multiple lines in data grid shown as only the first line.

    Tuesday, October 16, 2012 7:05 PM
  • Hi Max,

    No problem man. I'm glad you found the issue yourself.

    take care

    paul.


    paul van bladel

    Tuesday, October 16, 2012 7:19 PM
  • Hi Paul,

    Your solution is great. I did just notice that if if I am on a ListDetails screen and do not have a list item selected, a click on the Show Audit Records button will give a null reference exception error pointing to the CommonAuditHelper file line:

    foreach (Microsoft.LightSwitch.Model.IKeyPropertyDefinition keyPropertyDefinition in entity.Details.GetModel().KeyProperties)

    Is there a little code I could add here to deal with this or would it be better to somehow toggle the button visibility/state?

    Thanks


    4tuneate

    Sunday, November 04, 2012 6:33 AM
  • I moved the ShowAuditRecords button to the screen's List command bar as it seems more appropriate there. That made me realize how simple it is to disable the button if no record is selected.

    partial void ShowAuditRecords_CanExecute(ref bool result)
            {
                result = YourEntityName.SelectedItem != null;
            }


    4tuneate

    Friday, November 09, 2012 1:35 AM
  • Paul,

    This is extremely useful, thanks for sharing!  I plan to read Yann's book as well. 

    Friday, November 09, 2012 4:27 AM
  • I hope you find it useful.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Friday, November 09, 2012 4:43 AM
  • @4tuneate: thanks for the improvement. Indeed, the MVVM way is great.

    @Hessc: thanks. You will find similar and especially better stuff in Yann's book .


    paul van bladel

    Friday, November 09, 2012 7:14 AM
  • The solution needs an update for the rowversion field, which is a field added in VS 2012 and has a pure technical nature (for optimistic locking). Therefor, I think that just excluding this field from the auditing, would be just fine.

    Has anyone excluded the "RowVersion" property from auditing? I assume that this is done in the AuditHelper.cs file but I'm not sure how to go about it. This would really clean up the audit table.

    4tuneate

    Monday, April 15, 2013 12:50 AM
  • Answering my own question: Paul has updated the code in the posted example LS2012.AuditTrail to exclude the "RowVersion" property from audit. You'll need to go the User Code folders to get the new helper code and now only the _Inserted method for each entity needs code in the ApplicationDataService.cs. The _Updating and _Deleting methods are handled in the helper.

    4tuneate

    Friday, April 19, 2013 3:40 AM
  • In your post, you mentioned: "The reason why we can’t do the insert audit during the SaveChanges_Executing will be come clear later."  Perhaps, I missed something obvious, but it was not clear to me.  Can you explain why it's necessary to create a new method for each Entity to track inserts?

    Monday, May 13, 2013 2:01 PM
  • If I remember well, it's because I need the Id (or better: the primary key field) of the record under audit. Without Id it would be difficult to be able to jump to the original record later on.

    For a update or delete the Id is known, for an insert not. The Id is returned from the server when the Unit of work was correctly digested by the server pipeline. 

    Let me know if you see any improvement for this.

    Thanks for the feedback.


    paul van bladel

    Monday, May 13, 2013 2:27 PM
  • Hi Paul!

    Just ran across this thread and your post today, and it looks great.  Just like @forty7 said, it wasn't clear why you needed N different insert records (IMHO, you should edit the post to explicitly state why - 'will be come clear later' appears to have been incorrect :).

    Based on your response in this thread, it makes sense that deletes and updates can have their audit trail entries created before SaveChanges (since we know their key values), but inserts cannot.

    However, my guess would have been (and maybe you tried this and it didn't/can't work?) that you could do the audit trail entries for inserts in a single SaveChanges_Executed method instead of having to do N different ones?

    Thanks!


    Saturday, July 13, 2013 6:52 PM
  • Hi James,

    No, I'm not aware of doing it differently. Using  the SaveChanges_Executed is not an option I believe, the changes are already persisted.

    The only potential option is to use a technique called AOP (aspect oriented programming). PostSharp and Unity are both AOP solutions. AOP allows to "weave in" code fragements in an automated way into your code base. 


    paul van bladel

    Saturday, July 13, 2013 8:17 PM
  • Using  the SaveChanges_Executed is not an option I believe, the changes are already persisted.

    AFAICT that's already true for the approach you're taking now with the *_Inserted methods? The problematic scenario is when ID values are being assigned in the database (likely as identity columns), which is certainly the most common case (at least IME).  Because of that, AFAICT you're going to need nested or distributed transactions anyway if you really want atomicity?

    Thanks!
    Sunday, July 14, 2013 3:05 AM
  • To be clear, I'm not suggesting adding transactions, just that if I'm interpreting the behavior correctly and we're already doing the audit for inserts after the database has already committed the inserts, then we can simplify the existing code to use SaveChanges_Executed so those using it don't need to maintain the N different _Inserted methods. :)
    Monday, July 15, 2013 2:25 AM
  • do you see a way to use SaveChanges_Executed for the inserts? How would you know which records are inserted?

     this.DataWorkspace.ApplicationData.Details.GetChanges() won't tell you.

    As a said I only see AOP as an option, but I think it needs virtual methods to inject (weave) code. In lightSwitch we work with partial methods.  Anyhow AOP would need still a kind of declaration by means of an attribute.

    paul.


    paul van bladel

    Monday, July 15, 2013 7:21 AM