Audit Trails AX

Dynamics Ax 2012 : Exploring Data Base logging for Insert ,Update and Delete

Dynamics Ax 2012 provide the functionality to log insert update and delete functionality on table as well as selected filed of a table. When tracking on any table in dynamics Ax, a new entry will be created in sysDatabaselog table.

Inside database logging required table will find with the help of label of function. For example if  we want to enable logging on table name Customer then we can find customer in table with label “Customer”. You can find the label of table by exploring the property of by selecting table.

Let’s we enable logging on our custom “Student” table.

For this purpose you have to go “System Administrator” Module of Dynamics Ax 2012. Under Step tab you will find database node, there you find “Database log” Click on it. Like this.

After that Database base logging Wizard will start

Press Next. On next page select “Not Specified” node of tree. All custom table/ user table will be placed under “Not Specified” Node. Dynamics Ax Default tables are placed under their respected module name.

 Expend the not specified node.  And select the student table there.

Here you can track not only table but also possible you can track one or more fields of specific table. In current example we enable tracking or logging on whole student table.

 

 Now question is how we can see what will comes in in sysdatabaselog table on insert update or delete on table.  You can view from this link

 All those table where “Save per company “ option enable, all log files will be stored in sysDatabaselog with respect to their legal entities where the insert update or delete occurs. All those tables where stored data gobally in “Dat” legal entities.  Logging records will be found in “Dat” company.

Now we explore the database structure of SysDatabaselog table. This will help us to get the logged information in X++ code.

Followings are some important fields of SysDatabaseLog table.

1.    Table:  This filed contains the integer value for each table. A unique integer number attached to each table in dynamics Ax. This fileds tells us on which table operation is performed.

2.        Creation Date: This contains the date and time on which entry in sysDatabaseLog is created.

3.    Data:  X++ container type this contains the data.

4.    LoggType: This described insert, update or delete operation is pefromed at the result current entry is done in sysDatabaselog. It is of enum type. DatabaseLogType::Insert

  

For example, if database logging is enabled on our student table, we can track all insert, update or deleted record form database log table. For example if we have to pull all inserted today records form sysDatabaselog we can do by following way.

 Insert Case:

SysDataBaseLog sysDataBaseLog;

utcDateTime CurrentDate;

container tableRecord;

container recordInstance;

str selectedField;

str fieldName;

str fieldValue;

int idx;

int tableID;

str  databaseLogSourceCompany;

CurrentDate=DateTimeUtil::newDateTime(today(),str2time(“00:00:00″));

tableID = tableName2id(‘Student’);

while SELECT sysDataBaseLog

WHERE sysDataBaseLog.table ==  tableID && sysDataBaseLog.createdDateTime > CurrentDate && sysDataBaseLog.LogType == DatabaseLogType::Insert

{

selectedField = “RollNumber”;

fieldName=”";

fieldValue = “”;

tableRecord = (sysDataBaseLog.Data);

for( idx = 1; idx <= conLen(tableRecord); idx++)

{

recordInstance =  conpeek(tableRecord, idx);

fieldName = conpeek(recordInstance, 1);

fieldValue +=” ” + conpeek(recordInstance, 2) + ” , “;

break; 

} 

}

Update case will return three containers inside Data field of sysDataTable for each record, first one describe the field Name, second container returns the new value and third container will return old values. like

while SELECT sysDataBaseLog

WHERE sysDataBaseLog.table ==  tableID && sysDataBaseLog.createdDateTime > CurrentDate && sysDataBaseLog.LogType == DatabaseLogType::Update

{

selectedField = “RollNumber”;

fieldName=”";

fieldValue = “”;

tableRecord = (sysDataBaseLog.Data);

for( idx = 1; idx <= conLen(tableRecord); idx++)

{

// fields name in container.

recordInstance =  conpeek(tableRecord, idx);

fieldName = conpeek(recordInstance, 1);

// new values in container in same sequence as first container describe.

fieldValue +=” ” + conpeek(recordInstance, 2) + ” , “;

// Old set of value in same sequence.

fieldValue +=” ” + conpeek(recordInstance, 3) + ” , “;

}

Delete case:

Like Insert Delete case: you can find 2 containers first one contains fields name and second one contains the delete value in same sequence. But very important you can get recId of delete record as follow.

info(int642str(sysDataBaseLog.LogRecId)); complete code will be look like

while SELECT sysDataBaseLog

WHERE sysDataBaseLog.table ==  tableID && sysDataBaseLog.createdDateTime > CurrentDate && sysDataBaseLog.LogType == DatabaseLogType::Delete`

 {

 fieldName=”";

 fieldValue = “”;

 tableRecord = (sysDataBaseLog.Data);

info(int642str(sysDataBaseLog.RecId));

info(int642str(sysDataBaseLog.LogRecId));

 for( idx = 1; idx <= conLen(tableRecord); idx++)

 {

 recordInstance =  conpeek(tableRecord, idx);

 fieldName = conpeek(recordInstance, 1);

filevalue=conpeek(recordInstance, 2);

 info (fieldName + ” Value : ” + fieldValue);

 }

}