Danoweb In today’s era of Sarbanes Oxley, OSI compliance, and thousands of security attack vectors it’s important to understand what is happening inside an application. To this extent I have come to develop a process which easily monitors and tracks actions in an application. The success of this is in operating similar to the functionality of the windows event log system. This is important becuase it is process agnostic, meaning there is little to no overhead when using this process and extend the functionality of the application.
The process relies on a single database table and a set of fields. This are defined as:
- log_id – Auto Increment primary key field
- log_type – The type of log, useful for segregating the data later. Examples: General, Login, Account Add, Account Update, Order, etc.
- log_descr – The Log details, this is a “text” type which can contain an wide variety of information. This is the “human readable” part of the log.
- acting_users_id – Assuming the system has access management, this would be the currently logged in user’s unique identifier
- affected_users_id – Many times one user may be performing an action which affects another user (name change, password reset, account lockout, etc.) this is the affect user’s unique identifier
- ip_address – This is the Remote Users IP address. (This is useful for cases of stolen identity. The IP utilized can help to differentiate legitimate, and spoofed actions in the application.
- create_datetime – This is the Date and Time the record was entered
- edit_datetime – This is the Date and Time the record was modified (if appropriate) otherwise it should match the create_datetime.
- active – This is used for soft deletes as a purely Database Administration principle.
Once added to the database, a single function call within the code should write to this table. By utilizing one function, this keeps things consistent, and all audit and logging follows the same requirements and process. This data can then be handed off to a reporting engine, or data mining systems to leverage key information about how an application is being used, what a given user may be doing in the system and when, or the remote locations that are accessing the system.
