By using this website, you agree to our Terms of Use (click here)
- The answer to this question probably also applies to AUG SQL product
- I have access to my MS SQL databases. I am on 2022 R2 but I don't think my question is necessarily version specific
I would like to provide a SQL view/ Generic Inquiry of certain information from the AUDIT HISTORY. This information would be used to identify when certain workflow & process rules are not followed. Can someone please point me to the SQL tables that store this data?
My plan is to create a SQL view on the SQL server and "publish" it via a customization in order to make it available within Acumatica.
I'll be happy to share query (within this topic) once I build it
I have included a sample screenshot of the Acumatica Audit History page where I have field level tracking enabled. ie TOOLS> AUDIT HISTORY
One of these? AUAuditField looked promising.
The table is AuditHistory, but it is not easily readable because of how Acumatica stores the audited data and columns in single fields. Here is a query that looks up the Audit History for document SO123456. I left a couple filters for specifying table name or operation for reference. I have used a version of this when a client deletes a order with a lot of lines and they want to review the data to recreate the order.
select
CompanyID,
BatchID,
ChangeID,
ScreenID,
(Select top 1 username from Users u where PKID = UserID) AS 'User',
ChangeDate,
Operation,
TableName,
REPLACE(CAST(CAST(CombinedKey AS VARCHAR(max)) AS VARBINARY(max)), 0x0, ',') AS 'Document',
REPLACE(CAST(CAST(ModifiedFields AS VARCHAR(max)) AS VARBINARY(max)), 0x0, ',') AS 'Data Fields'
from AuditHistory
Where CompanyID=2 and CombinedKey like '%SO123456%' --and TableName='SOOrder'
--AND Operation IN ('I' ,'U')
Order by CombinedKey,ChangeDate
Good luck.
Kurt
Kurt
Yes thanks for the clarification -- the combinedkey and modifiedfields fields is part of what was causing me heartburn. I'll review and get back you, but I think this will let me move on to next steps