PowerObjects Blog 

for Microsoft Business Applications


Using SSIS/KingswaySoft Adapter to Obtain Audit Information

Post Author: Joe D365 |

A common request we see is the ability to extract and report on audit data gathered by CRM. If you have done any work or research around this topic, you may have found that it can be tricky. Lucky for you, today's blog will help you solve this issue by teaching you how to use KingswaySoft Adapter to obtain audit information!

If you have worked with SSIS/KingswaySoft Adapter before, you may be tempted to use CRM as a source and then select the Audit entity:

KingswaySoft Adapter

Although you can pull data from the Audit entity, you will not be able to get the column that stores the actual changes made since that column is not exposed and the data that is accessible is not fully usable.

The good news is that obtaining readable data from a CRM Audit is now a lot easier thanks to a feature in KingswaySoft CRM adapter. The new feature is a source type called AuditLogs.

Let's take a look at how it works!

1. Create a new Dynamics CRM Source and select the Source Type as AuditLogs.


2. Now you can use FetchXML to specify the entity that you want to pull data for by adding with the entity and the primary key of that entity:   






Keep in mind that you can use FetchXML conditions to pull the desired data set.

KingswaySoft Adapter

3. Refresh your CRM Metadata and click OK.

Now it is time to map your output. You will notice that the CRM source has two outputs:

  • Primary Output - The contents of the audit table minus the changeddata column.
  • Secondary Output - The actual changes (aka the missing piece when you pull data straight from the audit entity).

KingswaySoft Adapter

Once you map the two outputs to their respective destinations, your dts will look like this:

KingswaySoft Adapter

NOTE: We let SSIS create the table for us based on the metadata provided by each outputs.

Finally, you can query the data in the two tables by joining on the auditid column as shown below:

SELECT
a.[action]
,a.[actionname]
,a.[callinguseridname]
,a.[createdon]
,a.[objectidname]
,a.[objecttypecodename]
,a.[operation]
,a.[operationname]
,a.[useridname]
,d.fieldname
,d.oldvalue
,d.oldvalue_label
,d.newvalue
,d.newvalue_label
from [dbo].[INT_Audit_PrimaryV2] a
inner join [dbo].[INT_AUDIT_DETAILV2] d
on a.auditid = d.auditid

The results are as follows:


The last four columns give you the value and label or display value for the old and new values in the transaction. These are especially useful when the change involves a lookup or an option set as the value columns will display the GUID or integer respectively while the value_label columns will display the friendly name.

We hope you found today's blog helpful. Keep checking our PowerObjects blog for more tips and tricks, and if you are looking for more information on SSIS, check out other blogs on the topic!

Happy CRM'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

PowerObjects Recommends