Skip to main content

Celonis Product Documentation

Microsoft Dynamics AX: How do I extract the SysDatabaseLog Table

The data extraction from Dynamics AX works like for every other database. However, there is one special case which is the SysDatabaseLog table storing the change log events.

As change log tables can grow pretty large and consume a lot of memory and storage, Microsoft decided to store the relevant columns of this table as a container data type. That means it is encrypted when trying to access the table via Microsoft SQL Server Management Studio (SSMS) or JDBC.

In order to still read from the table, we have developed a decryption algorithm, with converts the encrypted container into a readable string.

As we cannot read directly from the SysDatabaseLog container table, a view needs to be created first in the following way:

CREATE View [dbo].[SYSDATABASELOG] as Select

[USERNAME]

,[DESCRIPTION]

,[LOGTYPE]

,MASTER.dbo.Fn_varbintohexstr(DATA) as DATA

,[TABLE_]

,[LOGRECID]

,[CREATEDDATETIME]

,[DEL_CREATEDTIME]

,[CREATEDBY]

,[CREATEDTRANSACTIONID]

,[DATAAREAID]

,[RECVERSION]

,[RECID]

from <Database_Name>.dbo.SYSDATABASELOG

This view can then be added to your extraction. The extraction will create two target tables, one called SysDatabaseLog which will extract the table as is and one called SysDatabaseLog$audit which decrypts the encrypted DATA column into readable strings.

In order to apply the decryption algorithm, make sure to specifically select Microsoft Dynamics AX when creating a data connection:

50726399.png

connector-jdbc.jar