CRM, SSIS & Integrations ...configuring CRM as a non-coder
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
CRM, SSIS & Integrations
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
  • Home
  • CRM
  • KingswaySoft
  • Using KingswaySoft’s auditlog in CRM

Using KingswaySoft’s auditlog in CRM

June 5, 2016 2 Comments Written by Thomas

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)

CRM Form

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.

auditlog_01

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.

auditlog_02

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.

Sort 1     Sort 2

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.

Merge Join

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.

derived Column

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:

audit_log

CRM, KingswaySoft
CRM 2015, CRM 2016, CRM Online, Data, How To, KingswaySoft, Log, News, SSIS
New ERP from Microsoft
Explode rows in SSIS

2 Comments

  1. fahmeeda fahmeeda
    October 22, 2018    

    Please let em know which entity you are using in your CRM destination for audit log. beacuse i didnt found any entity for loging errors.

    Regards,
    Fahmeeda Yaseen

  2. Thomas Thomas
    October 23, 2018    

    Hi Fahmeeda,

    Thanks for the info – it would seem I forgot to mention, that it is a custom entity I created as my destination. It is now noted in the post.

    Best regards,
    Thomas

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

The author

KingswaySoft MVP & Microsoft certified professional

My name is Thomas and I am a consultant at a company called netcompany A/S in Denmark. My primary focus is working with SSIS and KingswaySoft, where I perform integrations and datatranformations.

Click for more info

Recent Posts

  • Difference between two dates (SQL, SSIS and JavaScript)
  • Easy way to fetch row count for all tables in a SQL database
  • Create Application user in Azure for use in Dynamics 365
  • SSIS Basics: Merge join
  • Open dashboards in a new window

Useful information

  • Installation guide to KingswaySoft
  • Software download list
  • CRM Object Type Codes
  • CRM State & Status codes
  • Script Repository

Archives

  • May 2020 (1)
  • February 2020 (1)
  • January 2020 (2)
  • August 2019 (1)
  • May 2019 (1)
  • February 2019 (2)
  • January 2019 (2)
  • November 2018 (1)
  • September 2018 (1)
  • August 2018 (3)
  • May 2018 (4)
  • December 2017 (2)
  • August 2017 (1)
  • February 2017 (1)
  • November 2016 (4)
  • October 2016 (1)
  • September 2016 (2)
  • August 2016 (5)
  • June 2016 (4)
  • May 2016 (3)

Useful blogs/links

Henrik Jensen, my good friend and colleague, has a blog regarding EVERYTHING Dynamics crm. He's got 50+ certifications, and he is a force to be reckoned with. Link to his blog (danish): crmblog.dk

KingswaySoft have developed an outstanding piece of software for datamanagement to and from CRM, and I can recommend this toolkit for nearly every situation. Link to their website: kingswaysoft.com

Tag Cloud

Azure Bug CRM 2015 CRM 2016 CRM Online Data Dynamics365 ERP Excel How To JavaScript KingswaySoft Log News Office Outlook Personal Solution SQL SSIS Visual Studio

Categories

  • Azure (2)
  • Clubtimiser A/S (1)
  • CRM (30)
  • ERP (2)
  • How-to (14)
  • JavaScript (3)
  • Office (3)
  • Personal (4)
  • SQL (2)
  • Visual Studio (23)
    • SSIS (23)
      • KingswaySoft (14)

Disclaimer

The information given on the website is tested on a mixture of live production environments and test environments, and therefore I cannot guarantee my information will work for you, if you attempt to follow my steps.
If you experience problems, please feel free to contact me.

@June 7th 2016