C H A P T E R  15

Auditing What Your Users Do

Auditing is sometimes also called change tracking, but that's really only one part of the auditing process. Put simply, auditing is who did what, and when they did it. That can be adding, modifying, deleting, or even viewing data.

When you discover a problem with an entity's details, it's extremely helpful to be able to see who it was that added or edited the entity, and also when, in order to be able to ask questions about the information.

LightSwitch doesn't come with any out-of-the box way of tracking these details, so we need to create our own.

For some businesses it's just as important to know who's been viewing information as it is to know what changes have been made to it. Although a certain level of trust is involved when giving someone the permission required to add or modify data, keeping track of who's viewing that data can also be useful.

Audit logs can be used to catch those who use the system out of curiosity, rather than as part of their job, and those users could then be counseled, or disciplined, if necessary. It may even be required by law for a business to log who's accessing sensitive data.

We've seen several techniques for saving auditing information, some of which are fairly elaborate, but with a few clever techniques we've managed to reduce the complexity of the code to an absolute minimum by moving the majority of it to extension methods (see Listing 15-1), therefore reducing the amount of code needed to implement auditing in your project to just a few lines.

We've added the extension methods to the Utilities class library (in an Extensions namespace), so you'll be able to use this code in any LightSwitch project. Just remember to add a reference to it for any code that calls for it (as we explain later in the chapter, this will be LightSwitch's Server project) and to add an imports/using statement.

In this chapter, we'll describe two approaches for auditing the data that's inserted, or updated, in your application. We've called these the general and specific approaches. The main difference between the two approaches is where the audit data are saved:

  • General (all audit details are stored in a single table).
  • Specific (a separate audit table is used for each entity).

And, as with just about everything in life, there are pros and cons to each technique. It's pretty much a trade-off between convenience and functionality.

The advantage of the general approach is simplicity. This technique tracks changes to all of the tables in your application. You only need to add the auditing code into the save pipeline once. Any new table you add to your application will automatically be audited without you having to do anything more.

The specific technique allows you to choose the tables you want to audit. You can also create a relation between your actual table and the audit table. This allows you to easily create screens to view your audit data. However, this technique involves writing more code.

Figure 15-1 illustrates how these two techniques work. It shows what happens when the save button is clicked on a customer/order screen. We'll describe the methods that are illustrated in this diagram later in this chapter.

Of course, auditing is a wide subject area. If you're using attached SQL databases, auditing could also be implemented at the database level, using SQL triggers, so the methods we're describing here aren't the only ways to capture auditing information.

images

Figure 15-1. Auditing changes using the general and specific approaches.

images Tip While the code in this chapter intercepts the save pipeline to track changes that users make to the data, the query pipeline can also be intercepted to track who's viewing the data.

Basic Table Properties

You'll need to store your audit details in a table, and this is the most important part of both techniques. In the general approach, we'll create a table called change. When using the specific approach, we'll create separate change tables for each table you want to track.

The tables required for the two different approaches actually share quite a few basic properties (Table 15-1). We'll extend the schema of this table later when we describe the general and specific approaches in more detail.

images

When you're auditing the creation of new records, OldValues will always be null. Therefore, make sure to uncheck the required checkbox for the OldValues property.

Figure 15-2 shows a screenshot of the final solution (using the general technique). It highlights the typical values that would be stored in the change table. In particular, you'll see how the OldValues and NewValues fields contain a concatenated list of all the properties that have changed for each entity.

images

Figure 15-2. Screenshot based on the change table

The common code that you'll use for both techniques is shown in Listing 15-1. We've created the extension methods inside a new class library called Central.Utilities. Therefore, you'll need to have Visual Studio 2010 Professional or above installed (because the basic edition of LightSwitch doesn't support class libraries). If you only have the basic edition of LightSwitch, don't worry. You can add the IEntityObjectExtensions VB module or C# class directly into your server project. To work with the server project, you'll need to switch to File view using Solution Explorer.

Listing 15-1. Extension methods

VB:

File: Central.UtilitiesAuditingIEntityObjectExtensions.vb

Imports System.Runtime.CompilerServices
Imports System.Text
Imports Microsoft.LightSwitch
Imports Microsoft.LightSwitch.Details
Imports Microsoft.LightSwitch.Security

Namespace Extensions

    Public Module IEntityObjectExtensions

        <Extension()>
        Public Function StorageProperty(Of T As IEntityObject)(
            entityObject As T, name As String
                 ) As IEntityStorageProperty

            'Here's how to check that entityObject isn't null
            If entityObject Is Nothing Then
                Throw New ArgumentNullException("entityObject")
            Else
                Return TryCast(
                    entityObject.Details.Properties(name),
                    IEntityStorageProperty)
            End If

        End Function

        <Extension()>
        Public Function TrackedProperties(Of T As IEntityObject)(
            entityObject As T) As IEnumerable(
                Of IEntityTrackedProperty)

            Return entityObject.Details.Properties.All.OfType(
                Of IEntityTrackedProperty)()

            'You can modify the results to exclude properties
            'Here's how you'd exclude the surname property
            'Return entityObject.Details.Properties.All.OfType(
            'Of IEntityTrackedProperty)().Where(
            'Function(x) x.Name.Contains("Surname") = False)

        End Function

         <Extension()>
        Public Sub GetChanges(entityObject As IEntityObjectImage
            , ByRef oldValues As StringImage
            , ByRef newValues As StringImage
            , Optional valuesFormat As String = "{0}: {1}{2}"Image
            )

            Dim newResults = New StringBuilder
            Dim oldResults = New StringBuilder
            Dim properties =
                 entityObject.TrackedProperties(excludedProperties)

            For Each p In properties
                Select Case entityObject.Details.EntityState
                    Case EntityState.Added
                        newResults.AppendFormat(valuesFormatImage
                            , p.Name, p.Value, Environment.NewLine)

                    Case EntityState.Modified
                        If (p.IsChanged = True) Then
                            oldResults.AppendFormat(valuesFormatImage
                                , p.Name, p.OriginalValue,Image
                                Environment.NewLine)
                            newResults.AppendFormat(valuesFormatImage
                                , p.Name, p.Value, Environment.NewLine)
                        End If

                    Case EntityState.Deleted
                        oldResults.AppendFormat(valuesFormatImage
                            , p.Name, p.Value, Environment.NewLine)
                End Select
            Next

            'trim any trailing white space
            oldValues = oldResults.ToString.TrimEnd(Nothing)
            newValues = newResults.ToString.TrimEnd(Nothing)
        End Sub

        <Extension()>
        Public Sub UpdateFromEntity(Of T As IEntityObject)(
              entityObject As TImage
            , changedEntity As IEntityObjectImage
            , actioned As DateTimeImage
            , user As StringImage
            )
            Dim oldValues = ""
            Dim newValues = ""
            Dim action = changedEntity.Details.EntityState.ToString

            changedEntity.GetChanges(oldValues, newValues)
             With entityObject.Details
                .Properties("Actioned").Value = actioned
                .Properties("Action").Value = action
                .Properties("ActionedBy").Value = user
                .Properties("OldValues").Value = oldValues
                .Properties("NewValues").Value = newValues
            End With
        End Sub

    End Module

End Namespace

C#:

File: Central.UtilitiesAuditingIEntityObjectExtensions.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Details;
using Microsoft.LightSwitch.Security;

namespace Extensions
{
    public static class IEntityObjectExtensions
    {
        public static IEntityStorageProperty StorageProperty(
            this IEntityObject entityObject, string name)
        {
            //Here's how to check that entityObject isn't null
            if (entityObject == null)
            {
                throw new ArgumentNullException("entityObject");  
            }
            else
            {
                return (entityObject.Details.Properties[name] as
                    IEntityStorageProperty);
            }
        }

        public static IEnumerable<IEntityTrackedProperty>
             TrackedProperties(
                 this  IEntityObject entityObject)
        {
            return entityObject.Details.Properties.All()
                .OfType<IEntityTrackedProperty>();
             //You can modify the results of this method to exclude properties
            //Here's how you would exclude the surname property
            //return entityObject.Details.Properties.All()
            //   .OfType<IEntityTrackedProperty>().Where (
            //    x=> x.Name.Contains ("Surname") == false );
            
        }

        public static void GetChanges(this IEntityObject entityObjectImage
            , ref string oldValuesImage
            , ref string newValuesImage
            , string valuesFormat = "{0}: {1}{2}"Image
            )
        {
            var newResults = new StringBuilder();
            var oldResults = new StringBuilder();
            var properties =
                entityObject.TrackedProperties(excludedProperties);

            foreach (var p in properties)
            {
                switch (entityObject.Details.EntityState)
                {
                    case EntityState.Added:
                        newResults.AppendFormat(valuesFormatImage
                            , p.Name, p.Value, Environment.NewLine);

                        break;
                    case EntityState.Modified:
                        if (p.IsChanged == true)
                        {
                            oldResults.AppendFormat(valuesFormatImage
                                , p.Name, p.OriginalValue,Image
                                   Environment.NewLine);
                            newResults.AppendFormat(valuesFormatImage
                                , p.Name, p.Value, Environment.NewLine);
                        }

                        break;

                    case EntityState.Deleted:
                        oldResults.AppendFormat(valuesFormatImage
                            , p.Name, p.Value, Environment.NewLine);
                        break;
                }
            }

            //trim any trailing white space
            oldValues = oldResults.ToString().TrimEnd(null);
            newValues = newResults.ToString().TrimEnd(null);
        }
         public static void UpdateFromEntity(
              this IEntityObject entityObjectImage
            , IEntityObject changedEntity Image
            , DateTime actionedImage
            , string userImage
            )
        {
            var oldValues = "";
            var newValues = "";
            var action = changedEntity.Details.EntityState.ToString();

            changedEntity.GetChanges(ref oldValues, ref newValues);

            entityObject.Details.Properties["Actioned"].Value = actioned;
            entityObject.Details.Properties["Action"].Value = action;
            entityObject.Details.Properties["ActionedBy"].Value = user;
            entityObject.Details.Properties["OldValues"].Value = oldValues;
            entityObject.Details.Properties["NewValues"].Value = newValues;
        }
    }
}

The code shown includes four methods that we'll use in the general and specific approaches. These are summarized in Table 15-2.

images

When writing extension methods, it's good practice to check that the object that it extends isn't null. In the StorageProperty method, we perform a check on entityObject to make sure it isn't null. If it is, we'll throw an ArgumentNullException. It's a good idea to carry out the same checks on the remaining methods. However, we've left these out for the sake of brevity.

The TrackedProperties method returns all of the properties that belong to an entity. If you want to prevent certain properties from being audited, you can adapt this method as required. We've added a commented out section that shows you how to exclude properties named surname. We've hardcoded the value surname, but in practice you'll want to pass this value into the method to improve reusability. You could even create a string array, or IEnumerable<string>, to pass in a list of properties to exclude.

The General Approach

The general approach involves using a single table to hold the changes for all entities. Although it's quicker and easier to set up than using the specific approach, it has some downsides as well.

General Approach Pros

  • You don't have to create a table for every entity for which you want to track changes.
  • You can easily track deletions.
  • You can view all changes to all entities in a single screen.

General Approach Cons

  • No relations are possible between the entity table and the table that tracks its changes (depending on how you want to use the tracking data, it could result in more work to display it than if there were a relation between the two tables).
  • The ID property value of an added entity is not available at the time of saving the entity. This means that it isn't possible to record the ID value that LightSwitch automatically generates for the added entity.

The General Changes Table

In addition to the shared basic properties listed earlier (Table 15-1), the general table will need two more properties. These are:

  • EntityID (the ID of the entity that was changed).
  • EntityName (the name of the entity type).

The table design is shown in Figure 15-3. Due to the fact that this table will be storing changes for more than one type of entity, it's not possible to create a relation between each entity and the change table. So instead, we'll store the entity's ID, along with the name of the entity's type.

images

Figure 15-3. General changes table

images Note We've added EntityID as an integer property because all intrinsic LightSwitch entities use integer IDs. An attached database could use another type, or even have a multikey. In this case, you might want to consider changing the data type of EntityID to string and adapt the general changes code as appropriate.

The General Changes Code

The code that we'll need to add to implement this approach will need to go in the ApplicationDataService class (see Listing 15-2).

The following example is designed to work with the tables you've created within LightSwitch. This is because the changes are tracked using the ID property of the table (LightSwitch automatically creates an ID property for every table so we know it always exists). If you're tracking the changes of entities in an attached SQL database, it's unlikely that all of your tables will contain a primary key field called ID. Therefore, you'll need to adapt the code shown as necessary. You'll also need to add the code to the data service class that relates to your external data source (rather than ApplicationDataService).

The ApplicationDataService class is located in the Server project. The easiest way to get there is by right-clicking the ApplicationData data source in Solution Explorer, then selecting View Code.

We'll be intercepting the save pipeline (also known as the submit pipeline), just before the changes are persisted to the data store. One of the down sides to tracking changes this way is that entities that are being added won't have an ID value at this point yet, so the ID will always be zero.

The first code method (SaveChanges_Executing):

  • Saves the current date/time (so that all changes have the same timestamp).
  • Saves the username of whomever is logged in to the application.
  • Specifies the SaveEntityChanges method for each entity that has changed. Changed entities include added, modified, and deleted entities.

In order to retrieve the current date/time, we've used the DateTime.Now method. However, you could use the HttpRequest.Timestamp instead. This value will be consistent throughout any point in the request/pipeline processing. This might be helpful if you need to refer to a common date/time value within different methods inside your save pipeline code.

The second code method (SaveEntityChanges) is used by the first to:

  • Add a new record to the changes table.
  • Retrieve and store the entity's ID (if the entity's not being added, as discussed earlier).
  • Retrieve and store the name of the entity's type (remember, there is no relation defined with the entity itself).
  • Retrieve and store both the original values and the changed values of the changed properties.

Listing 15-2. General approach code

VB:

File: ServerUserCodeApplicationDataService.vb

Imports System.Text
Imports Microsoft.LightSwitch
Imports Microsoft.LightSwitch.Details
Imports Central.Utilities.Extensions

Namespace LightSwitchApplication

    Public Class ApplicationDataService

        Private Sub SaveChanges_Executing()

            Dim actioned = Now
            Dim actionedBy = Me.Application.User.Name

            For Each entity In Me.Details.GetChanges
                SaveEntityChanges(entity, actioned, actionedBy)
            Next

        End Sub

        Private Sub SaveEntityChanges(Image
              entity As IEntityObjectImage
            , actioned As DateTimeImage
            , actionedBy As String
            )

            Dim changeRecord =
               Me.DataWorkspace.ApplicationData.Changes.AddNew

            With changeRecord
                Dim idProperty = entity.StorageProperty("Id")

                 .EntityID = CInt(idProperty.Value)
                .EntityName = entity.Details.DisplayName

                changeRecord.UpdateFromEntity(entity, actioned, actionedBy)
            End With
        End Sub

    End Class

End Namespace

C#:

File: ServerUserCodeApplicationDataService.cs

using System;
using System.Text;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Details;
using Central.Utilities.Extensions;

namespace LightSwitchApplication
{
    public partial class ApplicationDataService
    {
        partial void SaveChanges_Executing()
        {

            var actioned = DateTime.Now;
            var actionedBy = this.Application.User.Name;

            foreach (var entity in this.Details.GetChanges())
            {
                SaveEntityChanges(entity, actioned, actionedBy);
            }

        }

        private void SaveEntityChanges(IEntityObject entityImage
            , DateTime actionedImage
            , string actionedByImage
            )
        {
            var changeRecord =
                this.DataWorkspace.ApplicationData.Changes.AddNew;

            var idProperty = entity.StorageProperty("Id");

            changeRecord.EntityID = (int) idProperty.Value;
            changeRecord.EntityName = entity.Details.DisplayName;

             changeRecord.UpdateFromEntity(entity, actioned, actionedBy);
        }
    }
}

This completes the code that you need to write to audit changes using the general approach. As a bonus, any new tables you create will be audited without you having to write any extra code. The only thing that remains is to build some screens based on the changes table. This will allow you to view the change records.

The Specific Approach

Although the more specific approach to tracking changes is a bit more work to set up than the general approach, it does have some advantages.

Specific Approach Pros

  • There is no single, potentially monolithic, table that holds all of the change data (with a reasonable number of entities in your application, even a moderate amount of change activity can result in a large number of records in the tracking table).
  • Having a single auditing table will become a bottleneck for all updates. Having separate auditing tables for each entity increases data independence and removes this bottleneck.
  • Each entity can have its own collection of related changes.

Specific Approach Cons

  • You have to add a separate table for each entity you want to track.
  • You have to write code for each entity (a small amount of code, but it still has to be written).
  • You can only view changes for one type of entity at a time (unless you use a custom RIA service to join the tables together).

In the example that follows, we'll create a relation between the table we want to track and the audit table. The advantage of doing this is that all change records are expressly related to the original entity. You can also easily create a screen that shows the entity and all related change records by using the built-in navigation properties-you wouldn't need to write any complicated queries to do this.

However, a disadvantage of creating a relation is that you can't track deletions. This is because there isn't an entity to associate the change record with. Also, you wouldn't be able to delete an entity without first deleting any associated change records. This wouldn't be very useful if you needed to retain all historical change records.

To overcome these disadvantages, you could choose not to create the relation. Instead, you could audit the ID and type names in the same way that was shown in the general approach.

The Specific Changes Table

If you want to create a relation between the table you want to track and the change table, you'll need to add one more property to the change table that was shown in Table 15-1. This extra property will be a navigation property to an actual related entity (instead of an ID and type name as in the general approach).

The example we'll show you is for the Person entity, but the process is the same for any other entity you might want to track changes for. Just create another change table and create the appropriate relation between it and the entity. The PersonChange table that we'll use is shown in Figure 15-4.

images

Figure 15-4. Example of a specific change table

In our example, the change table will have a Many-1 relation defined between it and its related table. Although we've named our table PersonChange, you could choose a naming convention that prefixes all change tables with the word audit (e.g., audit_Person). The advantage of this is that all audit tables will be grouped together in the auto completion lists that you'll find in the code editor.

Each entity being tracked will have a navigation property (we've called it PersonChanges), which will be a collection of related changes (Figure 15-5).

images

Figure 15-5. Person-PersonChange relation

After you've made your table changes, you'll also need to make a couple of adjustments to the code that implements the entity's tracking. We'll now show you what these are.

The Specific Changes Code

The code that we'll need to add to implement this approach will also go in the ApplicationDataService class (see Listing 15-3). Once again we'll be intercepting the save pipeline, just before the changes are persisted to the data store; whereas in the previous approach we added code to the data source's Saving_Executing method, this time we'll be adding code to the entity's inserting and updating methods. So, for this example, it'll be the People_Inserting and People_Updating methods.

The code for specific changes is actually even simpler than the code for the general changes was, thanks to the extension methods. Each of the two entity event methods:

  • Adds a new record to the entity's change table.
  • Retrieves and stores both the original values and the changed values of the changed properties.

Listing 15-3. Specific approach code

VB:

File: ServerUserCodeApplicationDataService.vb

Imports System.Text
Imports Microsoft.LightSwitch
Imports Microsoft.LightSwitch.Details
Imports Central.Utilities.Extensions

Namespace LightSwitchApplication

    Public Class ApplicationDataService

        Private Sub People_Inserting(entity As Person)
            Dim changeRecord = entity.PersonChanges.AddNew()
            changeRecord.UpdateFromEntity(entity, Now, Me.Application.User)
        End Sub

        Private Sub People_Updating(entity As Person)
            Dim changeRecord = entity.PersonChanges.AddNew()
            changeRecord.UpdateFromEntity(entity, Now, Me.Application.User)
        End Sub

    End Class

End Namespace

C#:

File: ServerUserCodeApplicationDataService.cs

using System;
using System.Text;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Details;
using Central.Utilities.Extensions;

namespace LightSwitchApplication
{
    public partial class ApplicationDataService
    {
        partial void People_Inserting(Person entity)
         {
            var changeRecord = entity.PersonChanges.AddNew();
            changeRecord.UpdateFromEntity(
                entity, DateTime.Now, this.Application.User.Name);
        }

        partial void People_Updating(Person entity)
        {
            var changeRecord = entity.PersonChanges.AddNew();
            changeRecord.UpdateFromEntity(
                entity, DateTime.Now, this.Application.User.Name);
        }
    }
}

The change record is added using the syntax entity.PersonChanges.AddNew(). In this example, entity refers to the person entity and PersonChanges refers to the navigation property. Creating the change record in this way automatically relates the change record with the person entity.

This completes the code you need to write to audit changes using the specific approach. As with the general approach, you can now build some screens to view your change records.

Summary

In this chapter we showed you why auditing data makes good business sense. Knowing who did what, and when, can help solve any data issues (such as who added a record, or who changed a property's value) and may even be required by law for sensitive information.

We explained:

  • That auditing can be both tracking changes that users make as well as who's viewing the data.
  • That SQL triggers can be used to capture audit information for attached databases.
  • How to add audit records into a single table, or multiple tables, in a reusable fashion.

Auditing is carried out by writing code in the save or query pipeline. The methods in these pipelines are executed when data operations are carried out on the server. During this time, you can insert audit records into a change table.

You can store all audit records in a single table, or you can create separate audit tables for each table you want to track. In this chapter, the single table approach has been called the general approach and the multitable approach the specific approach.

We've showed you how to track changes when records are added, updated, or deleted in your application. If you want to track changes when data are read, you'd add similar code in the query pipeline. We've also highlighted how you can improve reusability by writing extension methods that are added to a separate class library. This is especially useful if you want to reuse the same auditing logic in more than one LightSwitch project.

This chapter has demonstrated the basis of how to carry out auditing. Depending on your application, you might need to adapt the code shown to better suit your needs. For example, you might want to audit attached tables that contain combination primary keys. The code shown in this chapter provides a good platform for you to customize, extend, and better fulfill your auditing requirements.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.142.166.31