Skip to main content
Solved

Can't get field level auditing to work

I have enabled field level auditing.

In the Audit screen, I selected the following:

When I change the name of a vendor and save it (changed name to eQ Bank Test)

then click Tools - Audit History, there are no changes shown:

It looks like I’ve done everything correctly.

I’ve tried this in multiple installations of Acumatica on different versions but no luck.

~Joe

13 replies

Laura02
Rank
Badge +18

Hi @Joe21,

What do you see in User SEcurity, Inquiries, Audit History when you select the Vendors screen at the top?

Laura ~ Klear Systems, Inc. Irvine, CA. ~
Joe Schmucker
Rank
Userlevel 7
Badge +3

hi @laura01 

This is what I see

 

Joe Schmucker, CPA
Laura02
Rank
Badge +18

Choose table...

Laura ~ Klear Systems, Inc. Irvine, CA. ~
Joe Schmucker
Rank
Userlevel 7
Badge +3

I just realized that there are more tables to select from in the Audit setup screen.  I clicked the Next screen button and checked this table:

 

When I go to the audit screen I do see some changes.  It shows the change to the Vendor table but not the Address table.

Back on the User Security Audit History, I can see the changes to both the Vendor table and the Address table.

 

So it looks like it is working!  I’d like to know why I am not seeing the Address changes on the Tools - Audit History screen, but at least I know it is working.

Thanks @laura01 for your quick assistance!

 

Joe Schmucker, CPA
Laura02
Rank
Badge +18

I like to use the Customize → Inspect Element option in the Vendor screen to click on individual fields and see their names/tables. Changing Vendor Remit Address is a popular way to commit fraud, so I recommend to my clients to monitor the Remit Address. I suggest, use Inspect Element to make sure you have the exact tables and field names selected in your Audit.

Best regards,

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~
Naveen Boga
Rank
Userlevel 7
Badge +19

Hey, @joe21  Audit History changes will be shown with the fields only when we select the Parent table (In this case it is VENDOR), and using this reference system will show the child table changes.

Naveen B
Joe Schmucker
Rank
Userlevel 7
Badge +3

Hey @Naveen Boga  thanks for the tip!  Sorry if I am conflagrating here...I don’t have a technical issue with this (yet) but, FYI, the whole reason for this Audit stuff...The current audit screen and reporting is unsatisfactory to the customer.  For example, if you run any reports on the Audit History, it shows “Id’s”, not the actual values associated with those Id’s.  The auditor doing the analysis wants to know the actual customer number here, not the table Id of the customer.  The same goes with many other fields like Branch, Location etc.

I’m tasked with creating a more readable report from the audit tables.  I’m planning to do this with a SQL view where I can do my own joins to get useful info.

For example, this ID is useless to an Auditor:

 

If you try to make a GI against the AuditHistory table, there is only one table you can join to

I don’t see a way to use Acumatica out of the box to be able to create reporting from the AuditHistory table where you can join to other tables to get Vendor Numbers etc.  

Curious to know if you have ever done this before.  I’m thinking using SQL Views is probably the best approach.

Joe Schmucker, CPA
Naveen Boga
Rank
Userlevel 7
Badge +19

Hi @joe21  I just checked and it is showing proper Customer CD, but not ID’s.

 

Please find the screenshot for reference.

 

 

Naveen B
Joe Schmucker
Rank
Userlevel 7
Badge +3

OMG.  Sorry...the customer is using NUMBERS for the CD value.  My bad.  HA!  :-)

They still want a way to run an actual report, but there isn’t a report we can find to run which will present data the way the “Tools - Audit History” screen shows.

If you run a GI on the AuditHistory table, you get this

The changes are mashed into a single column.  They want this info parsed out into a useable format.

Also, just running the data out of the table doesn’t give you the “changes” like the on screen inquiry gives.  SO, this will be a complicated query.

 

 

Joe Schmucker, CPA
Naveen Boga
Rank
Userlevel 7
Badge +19

@joe21  Yes, that is a bit complicated query and we cannot achieve using the Generic Inquiries, but we can try with the custom generic inquiry using code we can extract the data from the Audit History tables and show them on the screen in proper format

Naveen B
Joe Schmucker
Rank
Userlevel 7
Badge +3

@Naveen Boga  Do you think using a custom DAC that references a SQL View would be the best approach?  This would be the same process I used for creating a custom Data Provider for the export scenario you helped me with a month or two ago.  The custom DAC would pull from the SQL View and then it could be used on the GI screen.  I’d LOVE to get the code Acumatica uses to pull the data to the Audit History screen, but I don’t think they would give it to me.  I could use that to simply cycle through the changes and dump them to a report.  

I think I could also do this in C# instead of using a SQL View.  It would be pretty nasty to do, but it might be easier to do that and just export the results to a report rather than using a GI and SQL.  SQL is a bit harder to work with for looping etc.

Love to hear your opinion.

 

Joe Schmucker, CPA
C
Rank
Userlevel 3
Badge

@Naveen Boga regarding your previous comment. I’ve enabled field level audit and now I’d like bring in the last modified date of a particular field in a GI. Example, I would like to know when the last time a Completed Percent on a Project Task was modified. I’m getting close in the GI but cannot link CombinedKey from the AuditHistory table like I had hoped. The CombinedKey seems to be a combination of the Project ID and the TaskID but when I link it breaks it. What’s even more odd is that when I export the AuditHistory table to excel the CombinedKey value displays as the whole string (project ID & task ID) but the cell actually only contains the project ID.

Based on your comment, are you suggesting linking these tables don’t actually work in a Generic Inquiry?

 


UPDATE: I was linking TaskID instead of TaskCD. This worked as expected. Now I just need to figure out how to not bring all of the history details.

Joe Schmucker
Rank
Userlevel 7
Badge +3

@cshaheen26 

The CombinedKey field is a “null” delimited field that uses whatever the Keys are for the respective table on the line.  I don’t think it is going to be possible for you to pull the key values out of that field without using C# coding.  It is possible to do it in TSQL and C# but I don’t think Acumatica has a feature to allow you to parse out a field based on a null delimiter in a field and break it into multiple columns for a join to another table.

I managed to do it but I had to use C# coding to do it.

Joe Schmucker, CPA

Reply


Terms & ConditionsCookie settings