ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

(0) Share

Report

Posted on by Community Member

I was developing a report with User, user permissions, its corresponding worker and assigned position details. For this report I need to add Modified By and Modified DateTime fields of User record.

I enabled Modified Date Time and Modified fields on SysUserInfoTable.

In DP class I tried two scenarios

1) Getting sysuserinfo ModifiedDateTime and ModifiedBy fields

when I change username in User form its not updating in ModifiedDatetime field, the field value is remaining same as createdDateTime field. I changed the user relation on user then sysuserinfo ModifiedDatetime field is updated with latest value.

1) Getting Userinfo ModifiedDateTime and ModifiedBy fields

I was able to get the right ModifiedDatetime, ModifiedBy fields when I change values like user name and email etc but the issue here is its displaying ModifiedDatetime, ModifiedBy values only from today, the user information that I changed yesterday is displaying changed name but ModifiedDatetime, ModifiedBy values were showing blank ideally they should display yesterday date and time and Modified by user Id.

Am I missing any step here or is there a better way of getting USER record created by, createddateTime, ModifiedDatetime and ModifiedBy values.

I am using AX 2012 R3 and the report purpose is for User permissions and worker records auditing.

Thanks

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (3)

Community Member on at

Like (0)

Report

RE: ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

Yes, Thanks

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

RE: ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

No problem.

Please note that logging would only be for future record insert/update/delete from the point which you have set up the database logging.

Mark the helpful answers as verified next to each post to resolve the thread, thank you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

This is the outline of the report we were trying

I will look at SysDatabaseLog

Thanks

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report

RE: ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

You already have a report for Database logged information, which you just have to set a filter for to only pull user information table data.

If you would like to combine it with more details in a custom report, that should not be a big change to include SysDatabaseLog entries too.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

Thanks Vilmos, Yes we can use change tracking or Database Log but we were trying to put all user information together in a SSRS report so trying to figure out a way if we can add modified fields into the report.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

Thanks for reply Martin.

I understand that UserInfo and SysUserInfo. I've enabled the fields just on SysUserInfo, which won't get updated if the change actually happens in UserInfo.

Would you please help me in understanding this behavior when I select a record from userInfo system table ModifiedDatetime, ModifiedBy in code I am getting the datetime and modified values correctly for records changed today but the values for the records that were changed yesterday are displaying blank for the same code (which were shown correctly yesterday).

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report

RE: ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

AOT > System documentation > Tables

Anything sitting in here are locked by the kernel, thus you cannot add or take away fields, or change table properties UserInfo is there. SysUserInfo as Martin has expressed is not a locked object so you could change it.

If you would like to do auditing, why don't you use the built-in feature of Microsoft SQL Server, namely Change Tracking?

Unfortunately that will not give you the AX UserId of who has modified/created the record (unless UserInfo already has the field/flag set, which I have not verified), but at least you would have a trace of what has happened to the table.

msdn.microsoft.com/.../bb726006(v=sql.110).aspx

docs.microsoft.com/.../about-change-tracking-sql-server

Additionally you could use the AX Database Log framework, which is exactly for auditing purposes and you might capture/query the required information easily.

technet.microsoft.com/.../dd362089.aspx

Was this reply helpful? Yes No
Verified answer

Ajit 8,755 on at

Like (0)

Report

RE: ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

Correcting typo here - (And you can not add those fields for UserInfo, because it's a system table.)

Was this reply helpful? Yes No
Martin Dráb 235,928 Most Valuable Professional on at

Like (0)

Report

RE: ModifiedBy and ModifiedDateTime fields of User record(userinfoTable).

I think you don't realize that there are two tables: UserInfo and SysUserInfo. You've enabled the fields just on SysUserInfo, which won't get updated if the change actually happens in UserInfo. (And you can't add those fields to UserInfo, because it's a system table.)

[Correction applied. Thanks, Ajit.]

Was this reply helpful? Yes No