Differences

This shows you the differences between two versions of the page.

--- audit_trailing [2022/03/25 17:11] – yves
+++ audit_trailing [2022/04/04 10:58] (current) – [Notes on storage requirements] angelos
@@ Line 1: / Line 1: @@
 ====== Audit trailing ======
-Describe what audit trailing is and why it exists; Probably needs to link to [[21_cfr_part_11|21 CFR.11]] at some point...
+An **audit trail** (also called audit log) is a security-relevant chronological set of records that provide documentary evidence of the sequence of activities that have affected at any time a specific operation, procedure or event. In PMA.core audit trailing is implemented as part of [[21_cfr_part_11|21 CFR.11]]
 ==== How does it work? ====
-How did //we// implement audit trailing in PMA.core?
+PMA.core implements audit trailing as a middleware between the application and the connection to the SQL server database. This middleware captures all requests from the application to the database and, depending on the operation, creates records on a sub-schema of the database with all the required information to recreate them.
+The sub-schema of the audit trail database can be seen in the table diagram bellow
+{{ ::audit_trail_diagram.jpg?600 |}}
+  * EntityTypes: All types known to audit trail i.e Root Directory, User, Form, FormData
+  * Entities: All specific entities that are available in PMA.core i.e. a specific Root Directory, specific user etc.
+  * EntityModificationEvents: This is the main table of audit trail that contains information about an entity that has changed on the PMA.core database. This contains the header information about the change.
+  * EntityModificationDetails: The detailed fields that was changed. This describes in detail a specific change in the //EntityModificationEvents// table with an one to many relationship(master/detail)
+The **EntityModificationEvents** table in detail contains the following columns
+  * ActionType: The type of action this record represents, one of Create/Delete/Update
+  * UserLogin: The user that performed this action
+  * CreatedOn: The date and time the operation occured in the database
+  * EntityId: A foreign key to the //Entities// table for which the operation occured
+The **EntityModificationDetails** is connected to the **EntityModificationEvents** and contains the following columns:
+  * EventId: A foreign key to the **EntityModificationEvents** that this record describes in detail
+  * FieldName: The name of the database field that was changed. This is a field of the entity that was changed
+  * OldValue: The value before the change. This is null for Create operations
+  * NewValue: The value after the change. This is null for Delete operations
+==== Consulting ====
+ The easiest way to consult the audit trail is via PMA.core's pre-defined audit trail views for the main entities of //Root Directories// and //Users//:
+  * [[rootdir#logging|Root directories]]
+  * [[user_monitoring#audit_trail|Users]]
+For more advanced use cases you can execute SQL Queries in the sub-schema of PMA.core database which was described in the previous paragraph.
 ==== Notes on storage requirements ====
-We have a way to monitor this in the installation check view, don't we?
+Keeping an Audit trail of all changes happening in a live production PMA.core server can be quite space consuming. You can always check all storage requirements of PMA.core in the [[installation_check#storage_checks|Diagnostics -> Installation Checks -> Storage Checks page]]
+{{ :storage_checks.jpg?600 |}}
+In that page you can check the total size of SQL database including the audit trail but also check other storages used such as the tile cache directory and the cloud cache
+If the usage of Audit trail storage is getting large you can always backup the tables **EntityModificationEvents** and the **EntityModificationEvents** and safely delete all rows from these tables.
-Also: insert some suggestions on what people can do when the audit trail becomes too large.