Query Audit History for Specific Modified Field

  • 9 March 2021
  • 2 replies
  • 206 views

Userlevel 5
Badge +3

I’m investigating the impact of a customization unintentionally modifying fields that it wasn’t supposed to in a previous version, 2018 R1, and if it is still occurring in our current version, 2019 R2. Luckily I have quite a bit of audit history to refer to. I was able to to use this query from the  Acumatica Developers blog to pull data and then parse it out into something more readable, but I’m unsure of how to validate my results to ensure that I pulled every occurrence of the modified field.

I’m specifically looking for every instance where the SOLine unit price was modified by a particular screenID. Would the following query be sufficient in returning all changes to the unit price? Is there a better way to find what I’m looking for?

SELECT CompanyID, 
BatchID,
ChangeID,
ScreenID,
(
SELECT TOP 1 username
FROM Users u
WHERE PKID = UserID
) as Username,
ChangeDate,
Operation,
REPLACE(CAST(CAST(CombinedKey AS VARCHAR(MAX)) AS VARBINARY(MAX)), 0x0, ',') as CombinedKey,
REPLACE(CAST(CAST(ModifiedFields AS VARCHAR(MAX)) AS VARBINARY(MAX)), 0x0, ',') as ModifiedFields
FROM AuditHistory
WHERE ScreenID = 'INSERT_SCREENID'
AND REPLACE(CAST(CAST(ModifiedFields AS VARCHAR(MAX)) AS VARBINARY(MAX)), 0x0, ',') LIKE '%CuryUnitPrice%'
ORDER BY ChangeDate DESC

 


2 replies

Userlevel 7
Badge +4

Hi @Neil Cantral  I see that this post is quite old, were you able to get around this issue? 
Is it still occurring in newer builds?

Userlevel 5
Badge +3

I think I was able to get the information I needed to resolve the customization issue.

Audit history definitely isn’t the easiest thing to work with.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved