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:

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.

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).

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

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!