Dailycode.info

Short solution for short problems

Audit trail (History) using the entity framework and self-tracking entities (Part1)

This post will describe an automated way to audit all database changes of your entities. It took some search and programming to get a satisfying result. Reading several posts, and combining them. Still I needed to invent some tricks to get all information. On the code project I found the most information here! You can find all code needed in this post.

Improved insert auditing implemented in part 2: here 


The result audit table looks like this:


 

The implementation starts at the heart of the  entity frame work. The context. There you can catch the event:  SavingChanges. In my case this code is written in the process layer.

  

private MyEntities.Entities ents;
…
 

ents.SavingChanges += new EventHandler(ents_SavingChanges);

  

This is triggered each time a change is persisted to the database. So exactly what we need. Next we will get all changed entities and create a new record in the audit table. We will trace if its an update, insert or delete. We will log the user, time, key fields and values, original values and new values and last the columns that contained changes.

We will need to declare an enum and 2 variables:

  

privatestring UserName = "notloggedin";
  
List<GP_CMDB_DBAudit> auditTrailList = newList<GP_CMDB_DBAudit>();
  
public enum AuditActions
{
    I,
    U,
    D
}

 

The username should contain the logged in user, you can also get it somewhere else. The list of audit entities is just used over several functions and for easy put as private variable. Then the enum will be used to tell the database what happened, Insert, Update or Delete.

Now let’s look at the SavingChanges function:

 

 

void Ents_SavingChanges(object sender, EventArgs e)
{
    try
    {
        StaticEnts.DetectChanges();
        IEnumerable<ObjectStateEntry> changes = StaticEnts.ObjectStateManager.GetObjectStateEntries(EntityState.Deleted | EntityState.Modified);
        foreach (ObjectStateEntry stateEntryEntity in changes)
        {
            if (!stateEntryEntity.IsRelationship && stateEntryEntity.Entity != null && !(stateEntryEntity.Entity is GP_PROJ_DBAudit))
            {
                //is a normal entry, not a relationship
                GP_PROJ_DBAudit audit = this.AuditTrailFactory(stateEntryEntity, UserName);
                auditTrailList.Add(audit);
            }
        }

        if (auditTrailList.Count > 0)
        {
            foreach (var audit in auditTrailList)
            {//add all audits 
                StaticEnts.GP_PROJ_DBAudit.AddObject(audit);
            }
        }
        auditTrailList = new List<GP_PROJ_DBAudit>();
    }
    catch (Exception er)
    {
        //Audit falied, need to log it.
    }
}

 

Very important and took me some time to find, start with the DetectChanges method. If this is not called, it could happen that insert or delete is not detected.

Next we will get the entities that are changed from the objectstatemanager.  We start to loop over them and create an audit record using the AuditTrailFactory.

So that’s the next function we will take a look at:

 

 

private GP_PROJ_DBAudit AuditTrailFactory(ObjectStateEntry entry, string UserName)
{
    GP_PROJ_DBAudit audit = new GP_PROJ_DBAudit();
    audit.AuditId = Guid.NewGuid().ToString();
    audit.RevisionStamp = DateTime.Now;
    audit.TableName = entry.EntitySet.Name;
    audit.UserName = GetUserFromEntry(entry);

    if (entry.State == EntityState.Deleted)
    {//entry in deleted
        AddKeyFields(entry, audit);
        audit.OldData = GetEntryValueInString(entry, true, AuditActions.D);
        audit.Actions = AuditActions.D.ToString();
    }
    else
    {//entry is modified
        AddKeyFields(entry, audit);
        audit.OldData = GetEntryValueInString(entry, true, AuditActions.U);
        audit.NewData = GetEntryValueInString(entry, false, AuditActions.U);
        audit.Actions = AuditActions.U.ToString();

        IEnumerable<string> modifiedProperties = entry.GetModifiedProperties();
        //assing collection of mismatched Columns name as serialized string 
        audit.ChangedColumns = XMLSerializationHelper.XmlSerialize(modifiedProperties.ToArray());
    }

    return audit;
}

 

The AuditTrailFactory will instantiate an GP_CMDB_DBAudit entity.  Depending on the action we need to get different information.  First we fill in the properties we have like a GUID, a date time, the table name and the user.  Next we add the key fields and their values. This is done the AddKeyFields method:

  

private void AddKeyFields(ObjectStateEntry entry, GP_PROJ_DBAudit audit)
{
    var key = entry.EntityKey;
    var keyValues = entry.EntityKey.EntityKeyValues;
    StringBuilder sb = new StringBuilder(@"<?xml version=""1.0"" encoding=""utf-16""?><keyvalues>");

    foreach (EntityKeyMember member in keyValues)
    {
        if (member.Value.ToString() == "0" || member.Value.ToString() == "")
        {
            sb.AppendFormat("<{0}>new</{0}> ", member.Key);
        }
        sb.AppendFormat("<{0}>{1}</{0}> ", member.Key, member.Value);
    }
    sb.Append("</keyvalues>");
    audit.KeyValue = sb.ToString();
}

 

 

 

 

 

This is a very simple function that collect the key field and value and creates a xml structured record to save in the audit entity. Is structured like <ID>321546</ID>

Then we get the old data and the new data from the entry. In case of an insert,  we will insert all fields as xml in the newdata field, the old data will be empty. Also after I collect the data, I perform a accept changes because only then the primairy key fields are filled in, then I call the addkeyfields function. In case of delete,  we fill in all fields into the olddata field of the audit record. In case of an update, we only log the changed fields. The original values of these fields are logged in the olddata, the new values are logged in the newdata.

Here’s an example of the xml from the old data in case of an update:

 

<Remarks>dit is een test</Remarks>
<Modified>19/03/2012 11:26:55</Modified> 

Then the newdata will ook like this:

<Remarks>dit is een test van mark</Remarks>
<Modified>20/03/2012 16:56:53</Modified>

 

Now, let’s take a look at the function GetentryValueInString: (I added a function: FormatSetterValueToXML because some characters are illegal in xml, I'll explain more after this snippit)

 

 

private string GetEntryValueInString(ObjectStateEntry entry, bool isOrginal, AuditActions action)
{
    try
    {
        if (entry.Entity is IObjectWithChangeTracker)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"<?xml version=""1.0"" encoding=""utf-16""?><data>");
            object target = CloneEntity((IObjectWithChangeTracker)entry.Entity);


            if (action == AuditActions.I || action == AuditActions.D)
            {
                // Iterate over the members (i.e. properties (including complex properties), references, collections) of the entity type
                foreach (EdmMember member in (StaticEnts.ObjectStateManager.GetObjectStateEntry(entry.Entity).EntitySet.ElementType.Members).Where(t => t.TypeUsage.EdmType is PrimitiveType))
                {
                    object setterValue = null;
                    string propName = member.Name;

                    if (action == AuditActions.I)
                    {
                        setterValue = entry.CurrentValues[propName];
                    }
                    else
                    {
                        setterValue = entry.OriginalValues[propName];
                    }
                            
                    if (setterValue != DBNull.Value)
                    {
                        //PropertyInfo propInfo = target.GetType().GetProperty(propName);
                        //propInfo.SetValue(target, setterValue, null);
                        FormatSetterValueToXML(ref setterValue);
                        sb.AppendFormat("<{0}>{1}</{0}> ", propName, setterValue);
                    }
                }
            }
            else
            {

                foreach (string propName in entry.GetModifiedProperties())
                {
                    object setterValue = null;
                    if (isOrginal)
                    {
                        //Get orginal value 
                        setterValue = entry.OriginalValues[propName];
                    }
                    else
                    {
                        //Get orginal value 
                        setterValue = entry.CurrentValues[propName];
                    }
                    //Find property to update 
                    //PropertyInfo propInfo = target.GetType().GetProperty(propName);
                    //update property with orgibal value 
                    if (setterValue == DBNull.Value)
                    {//
                        setterValue = null;
                    }
                    //propInfo.SetValue(target, setterValue, null);
                    FormatSetterValueToXML(ref setterValue);
                    sb.AppendFormat("<{0}>{1}</{0}> ", propName, setterValue);
                }//end foreach

            }
            sb.Append("</data>");
            return sb.ToString();
        }
        return null;
    }
    catch (Exception er)
    {
        //Handle audit exception
        string test = er.Message;
        return null;
    }
}

private void FormatSetterValueToXML(ref object setterValue)
{
    if (setterValue.GetType() == typeof(string))
    {
        setterValue = setterValue.ToString().Replace("&", "_");
        setterValue = setterValue.ToString().Replace("-", "_");
        setterValue = setterValue.ToString().Replace('"', '_');
        setterValue = setterValue.ToString().Replace("'", "_");
    }
}

 

  

As you can see I created the function FormatSetterValueToXML. Because nvarchars can hold any character, XML doesn't. All the audit data is stored in xml columns, so we will need to format the following signs if they appear in the string:& - " ' You can choose yourself in what king of string or character you want to replace it to. For now I just replace them by _

 

After the clone you can see the function is divided into 2 parts. Insert and Delete will follow a different procedure than the update.

The insert and delete will simply loop over all (simple) properties and transfer them to a xml structured string. To loop over the properties of the ObjectStateEntity we call the GetObjectStateEntry method and restrict the properties return to primitive, because I do not want to loop over the linked entities of this object and only the properties.

 

.Where(t => t.TypeUsage.EdmType is PrimitiveType)

 

 

For the modified entities we simply loop over the modified properties that you can get using the GetModifiedProperties method on the entry.

I must say that the first test are really positive. The auditing implementation is really simple and the result is very satisfying. It will audit all changes on the database and helps you to track when, what and who.  That’s exactly what the auditors like ;-)


 

DBAuditLarge.png (214.34 kb)

Here you have the source code that could help you to implemnt it. It also includes the sql for the audit table.

ENT_DBAudit.zip (23.75 kb)


Implement Auditing using MessageQueue and WCF

In my new application, I started to implement extensive auditing, for this was required by the business. After some thoughts and a course of WCF, I discoverd that MessageQueue combined with WCF is a perfect solution for auditing. Since auditing can have a lot of inserts, but doens't need a return, we can implement this as one way traffic. From client to server.

So I created a function that writed audit records that have following information: User, Message, Extra information (Original and changed values, or a guid to the record or ...) and a state. The state will make sure the audit records end up in the corresponding audit tables.

If you decorate the operation contract with the IsOneWay=true flag, you can use it for message queue. The service interface looks like this:

 

    [ServiceContract]
    public interface IAuditManager
    {
        [OperationContract(IsOneWay=true)]
        void Audit(AuditRecord auditRec);
    }
 

Then the implementation looks like this: 

public void Audit(LabCollect.ServerComponents.SharedClasses.AuditRecord auditRec)
{
    ColumnCollection columns = new ColumnCollection();
    columns.Add("Message", auditRec.Message);
    columns.Add("EXTRAINFO", auditRec.ExtraInfo);
    columns.Add("TIME_STAMP", auditRec.TimeStamp);
 
    GLPDA.Insert("LA_AUD_"+auditRec.State, columns, _dbString, auditRec.User);
}

The last update is automatically logged by the server. This can be different then the time stamp of the audit record, because in message queue the server time is not always the same time as the record was created by the client.

I used a channelfactory on the client to address the WCF service. I'm not using transactions for writing single audit records. You can initiate the AuditManager like this: 

string auditEndPoint = "net.msmq://localhost/private/LabCollectAudit";
NetMsmqBinding
bindings = new NetMsmqBinding();

 

bindings.ExactlyOnce=false;
_auditMgrProxy = ChannelFactory<IAuditManager>.CreateChannel(bindings, new EndpointAddress(auditEndPoint));

The ExactlyOnce property needs to be set to false when you are not using transactions. So All that is left is to configure the endpoints on the host, which is very simple. By example I show you how to do it using the configuration file:

<system.serviceModel>
    <services>
      <service behaviorConfiguration="LabCollectWCFAuditService.Service1Behavior"
        name="LabCollectWCFAuditService.AuditManager">
        <endpoint address="net.msmq://localhost/private/LabCollectAudit" binding="netMsmqBinding" 
contract="LabCollectWCFAuditService.IAuditManager" bindingConfiguration="MSQueueBinding">
          <identity>
            <dns value="localhost" />
          </identity>
        </endpoint>
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="http://localhost:7100/LabCollectWCFAuditService/AuditManager/" />
          </baseAddresses>
        </host>
      </service>
    </services>
    <bindings>
      <netMsmqBinding>
        <binding name="MSQueueBinding" exactlyOnce="false"></binding>
      </netMsmqBinding>
    </bindings>
    <behaviors>
      <serviceBehaviors>
        <behavior name="LabCollectWCFAuditService.Service1Behavior">
          <!-- To avoid disclosing metadata information, 
          set the value below to false and remove the metadata endpoint above before deployment -->
          <serviceMetadata httpGetEnabled="True"/>
          <!-- To receive exception details in faults for debugging purposes, 
          set the value below to true.  Set to false before deployment 
          to avoid disclosing exception information -->
          <serviceDebug includeExceptionDetailInFaults="True" />
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>

Do not forget to create the Message queue on your machine or server. When you insert records using the clients and make sure the services on the "server-side" are not running, then you can see them in your mesage queue:


Auditing in MOSS 2007

MOSS 2007 auditing. In many cases it is useful to know how many times a document is opened.Sometimes even when and who? Moss 2007 offers auditing as a standard solution for this need. This is what Microsoft has to say about Auditing: Office SharePoint Server 2007 goes even further and enables auditing at the list or document library level, and control over what types of events should be recorded in the audit log. Office SharePoint Server 2007 also provides a reporting function that uses Microsoft Office Excel workbooks to display and keep records of audit logs. Therefore, Office SharePoint Server 2007 enables you to take advantage of the Windows SharePoint Services auditing support without having to write any custom code. In MOSS 2007 it is possible to track several events on list items.This is done with information management policies. Here’s a good definition about the extra’s Sharepoint server 2007 provides: Office SharePoint Server 2007 provides an administrative user interface that allows you to enable and configure auditing without having to write any code. And office SharePoint Server 2007 provides a reporting aspect. More specifically, Office SharePoint Server 2007 makes it possible to generate Excel workbooks that contain the information about audit events within the Windows SharePoint Services audit log.  So MOSS makes it easy for us to track and report usage of documents and lists. Here how Microsoft describes the configuration of auditing: 

We start by examining the built-in Office SharePoint Server 2007 support for configuring auditing within a site collection. Go to the site setting page of any site within a server farm that has Office SharePoint Server 2007 installed. You find that many links are added here by Office SharePoint Server 2007-specific features. Inside the Site Collection Administration section, locate a link with the caption Site Collection audit settings as shown in Figure 7.

 

Figure 7. Site Collection Administration

 

 

Clicking the Site collection audit settings link opens an Office SharePoint Server 2007-specific application page named AuditSettings.aspx. This page provides a user interface (Figure 8) for enabling and configuring auditing settings for the current site collection. As shown in the Figure 8, the AuditSettings.aspx application page makes it possible to configure site collection auditing at various levels of granularity.

 

Figure 8. AuditSettings.aspx page

 

 

In addition to configuring auditing at the site collection level, Office SharePoint Server 2007 also enables you to configure auditing at a much more detailed level. This support is made possible through the information management policies feature that is included in Office SharePoint Server 2007.

An information management policy consists of a set of rules that a site administrator can define and then apply to a certain type of content. The rules for an information management policy are created and configured in terms of policy features. Features that are provided by default with Office SharePoint Server 2007 include support for auditing, expiration, document labels, and bar codes. The programming model for policy features is also extensible, which allows developers to create their own custom policy features. For developer resources, see the appendix at the end of this article.

Office SharePoint Server 2007 enables you to create and configure an information management policy for a specific instance of a list or document library. You can also create an information management policy for a content type, which then applies to list items and documents in any list or document library that is defined in terms of that content type. The ability to apply information management policies to content types is valuable because it provides more granular control than site collection level auditing. It also mitigates the need to configure audit settings at the level of list instances or document library instances.

Office SharePoint Server 2007 also enables you to define information management policies at the site collection level. This provides extra manageability, because you can define an information management policy once within a site collection, and then apply it to your choice of lists, document libraries, and content types within that site collection.

The Site Settings page has a link with the caption Site collection policies. If you click this link, it opens an application page named Policylist.aspx. This allows you to configure a custom policy, which is scoped to the current site collection. Figure 9 shows an example of a custom policy that was created to configure a standard set of audit events that can be applied to any list, document library, or content type.

 

Figure 9. Custom policy page

 

 

After you define a custom policy at the site collection level, you can then go to the List Settings page for a list or document library. You find a link with the caption of Information management policy settings, as shown in Figure 10. If you click this link, it redirects you to an application page named Policy.aspx, which enables you to create a new information policy. Alternatively, you can apply a policy that is already defined at the level of the site collection. On a server farm with Office SharePoint Server 2007 installed, the Windows SharePoint Services page that enables you to create and configure content types also provides a link that takes you to Policy.aspx. Here you can also create or apply policies.

 

Figure 10. List Settings page

 

 

Figure 11 shows the options that are available through the application page policy.aspx. This is a simple way to apply a custom policy created at the site collection level. It also enables you to define and configure a unique policy that applies only to the current list, document library, or content type. In many cases it promotes a higher level of manageability. For example, you can create all of your policies at the site collection level, and then simply apply your policies to the appropriate lists, document libraries, and content types.

 

Figure 11. Applying custom policies

 

 

This  can also be found at:http://msdn2.microsoft.com/ look for: OfficeSharePointServer2007 ValueAddedSupportforAuditing  When you start using this nice feature, you may find strange results when generating a report. This is because most events only work with office documents. If you try to check how many times a specific pdf document in a library has been viewed, it's impossible. So if you need to now how many times a certain non-Office document has been viewed, then you find yourself writing custom code. Maybe I'll try to figure this out later on.