Inquiry on SAP B1 DB Audit Data (DML History, Change/Access Logs, Authorization Tables)

[clara_ryu]

One of B1 partner sent the below questions from the customer. 

I would appreciate it if you could review and provide the answers.

*If certain items are not provided, please let me know. If they are available, please provide the relevant information along with the database tables where we can verify them.

-------------------------------------------------------------------------------------------------

1. Inquiry Regarding DB DML Change History (Data Modification Log)

We need to verify whether any data modification (DML) occurred in the SAP B1 database during a specific period.

Is there a method to query DB-level data change history (DML logs) within the SAP B1 database?

2. Inquiry Regarding Access Log / Change Log / Authorization Log Tables and SQL Queries

We would like to identify which database tables store the Access Logs, Change Logs, and Authorization Logs provided by SAP Business One.

2-1. Change Log – Difference View

In Administration → Setup → General → Users, after selecting a user, the toolbar provides a “Change Log – Show Differences” view.

We would like to retrieve the same information directly from the database.

Please provide the table name(s) and SQL query to extract these change-difference details.

2-2. Access Log

In Administration → Setup → General → Users, after selecting a user, the toolbar provides an “Access Log” view.

We would like to extract the same access log information directly from the database.

Please provide the related table name(s) and SQL query.

2-3. General Authorization Data

In Administration → System Initialization → Authorizations → General Authorizations, we would like to extract the full authorization data for all users directly from the database.

Please provide the related table name(s) and SQL query.

Thank you!

[LoHa]

Hi,

use the newest B1 Version. There are Reports for Change (Documents and so on Audit Trail Report) and you will find a Report for changes according to permissions. The Access-Log shows you everything according to User-Logins. Make a double click on line opens up a detailed report

Otherwise use

USR5 for Access

ADOC for History for Documents

ACRD for History for Business Partner

AITM for History for Items

All Tables starting with an A containing History-Data.

Good Luck

Regards Lothar

[clara_ryu]

Hello,

Thank you for your reply. 

However, the customer is still asking the below:

"Based on your explanation, we understand that the access log can be reviewed within the SAP application screen, and that double clicking and entry displays the detailed information.

However, what we would like to confirm is whether this log data can be directly queried at the database level rather than through the SAP application UI.

Please confirm this"

Thank you.

[LoHa]

Hi,

try USR5

regards Lothar