Using KingswaySoft’s auditlog in CRM

In the latest version of their toolkit for CRM, KingswaySoft added a functionality to tap into the Audit history using fetchxml, to target a specific entity. This can be used for a lot of purposes, but the one I want to focus on here, is for a client I am working on with Nordic Computer. They wanted view the audit log on a subgrid on a given entity, so the user can view a subgrid with changed made to the record. I created a custom entity for this purpose, and there are a few basic fields on the form that needs to be created, which I will see to first.

• A lookup to the target entity (Projects)
• The action taken (Create, update, win etc)
• Lookup to systemuser
• Change date
• AuditGuid (single line of text, used for upserts)
• Fieldname
• Old value (multiple lines of text)
• New value (multiple lines of text)

When the fields on the form have been created and published, I start to set up the job in SSIS.

We access the auditlog in the Dynamics CRM Source, by selecting the option AuditLogs in the Source Type.

When you select the Auditlog, you are presented with a fetchxml area, where you can specify which entity you wish to target. I would recommend using a simple fetchXML statement, in which you define all attributes, instead of doing them one-by-one, as you only really need the connection to the entity.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
 <entity name="opportunity">
 <all-attributes />
 </entity>
</fetch>

When the auditlog is selected as the type, the source has 2 outputs: CRM Primary Output and CRM Secondary Output (Audit Details). They each contain some of the data we want on the same row for each changed field – and before we can do that, we need to sort the data and merge them together. The first thing we need to do, is to sort the data from each output. In the audit details output, I will select two columns for both old value and new value, because they hold different data, which I want to work with later.

Each data stream has an attribute that makes them unique to each other. They have a column called auditid, which we can match the 2 outputs to each other – and we will this column to sort by, as this is our unique key. We then select the columns we want to pass through the sort. You can see the screenshots below, for the fields I elected to use.

When we have sorted the 2 outputs, we want to merge them together to one output – and for this, we want to use the Merge Join function in SSIS. You want to make sure, the inner join type is selected. You then select the two auditid’s as join keys, and then select the columns you want to pass through below.

We are now ready to return the values back to CRM. But before we do so, we need to decide what kind of information we want to return. For both the old and new value fields, there are 2 columns. One with the value and one with the label. For look-up fields, the field value is the guid and the label value is the name of the record. In my case, I need the label values, as the data is going to be used as a subgrid on the corresponding record.

But what I’ve found is, that date fields are shown as NULL in the labels, but they are shown correct in the value fields. I will therefore throw in a derived column, where I use an if/else function, to get my correct values.

And I’m using the same derived column for the new value. I am also throwing the auditid in a string as well, because this is one of my matching criteria.

Then all I need is an CRM destination, and map the values I want. The destination needs to be set to Upsert with the manually specify matching criteria. On the column list, you need to match on two columns. Firstly, the auditid – and secondly, the fieldname. These two in combination, makes each row unique.

And this is the result in CRM: