Audit tables date filter

F

fanyfn

New Member
We’re using an ERP system built on Progress OpenEdge (Release 11.6.3.023). I need to extract audit log data for a specific table — in particular, I’m interested in a specific event that occurred between two given dates. I’ve been trying various approaches, but I can’t seem to get date filtering to work properly.


So far, I’ve written something like this:

DEF var cFile AS CHAR NO-UNDO INIT "D:\\audit_dump.csv".
DEF var iCounter AS INTEGER NO-UNDO INITIAL 0.

OUTPUT TO VALUE(cFile).
PUT UNFORMATTED
'"AuditGUID","DataTime","User","EventID","Context","Detail"' SKIP.

FOR EACH _aud-audit-data NO-LOCK:
ASSIGN iCounter = iCounter + 1.

PUT UNFORMATTED
'"' _Audit-data-guid '",'
'"' STRING(_Audit-date-time) '",'
'"' _User-id '",'
'"' _Event-id '",'
'"' _Event-context '",'
'"' _Event-detail '"' SKIP.

IF iCounter >= 200 THEN LEAVE.
END.

OUTPUT CLOSE.
MESSAGE "Saved " + STRING(iCounter) + " Records to: " + cFile VIEW-AS ALERT-BOX.
Click to expand...

But I don’t know how to properly filter by date in the FOR EACH loop. How can I correctly query for records between two specific dates?
 
You should add a WHERE clause, similar to the below.

Code: 
DEFINE VARIABLE dStartDate AS DATE NO-UNDO.
DEFINE VARIABLE dEndDate AS DATE NO-UNDO.

dStartDate = 3/1/2025.
dEndDate = 4/1/2025.

FOR EACH _aud-audit-data NO-LOCK
   WHERE _aud-audit-data._audit-date-time >= dStartDate
      AND _aud-audit-data._audit-date-time < dEndDate :

There's documentation on the audit tables' schemas at Progress Documentation
 
Remember that best practice with OE Auditing is to keep minimal audit data in the source database, and archive (dump/delete) it regularly to an audit archive database. You should do audit data reporting from the archive database.

This approach does the following:
  • Keeps the source database audit data at a manageable and relatively constant size.
  • Allows you to deactivate some audit indexes on the source, minimizing the I/O cost of audit writes.
  • Moves audit query I/O to a non-prod database.
  • Facilitates enforcement of least privilege: business application users do not require access to the archive database and auditors do not require access to the application database.
  • For federated applications (multiple databases), it allows you to consolidate audit data in a single place, simplifying the audit workflow.
Remember to write and implement a data-retention policy for audit data, so the audit archive database does not grow out of control and become a maintenance headache.
 
Thank you all very much for your help. It turned out that the field uses timezone information, which is why a simple date comparison didn’t work. I used the DATETIME-TZ function and it works perfectly!

DEF VAR dStartDate AS DATETIME-TZ NO-UNDO.
dStartDate = DATETIME-TZ(07, 23, 2025, 0, 0, 0, 0).
Click to expand...

I’m not the database administrator – the entire solution is on the ERP system vendor’s side. Unfortunately, the way audit data is presented in the ERP interface is quite primitive. Since my ODBC account is restricted from reading audit tables, I had to try my luck with ABL :)
 
fanyfn said:
Thank you all very much for your help. It turned out that the field uses timezone information, which is why a simple date comparison didn’t work. I used the DATETIME-TZ function and it works perfectly!



I’m not the database administrator – the entire solution is on the ERP system vendor’s side. Unfortunately, the way audit data is presented in the ERP interface is quite primitive. Since my ODBC account is restricted from reading audit tables, I had to try my luck with ABL :)
Click to expand...
Well done for giving it a go. Glad you got sorted. If you need any more help in future please feel free to start a new thread :)
 
You must log in or register to reply here.
Back
Top