You need an application log for your systems

All systems requires a common area to keep track certain information. For example,

• The exception/error which was not handled properly - this eases the system maintenance.
• The user login and logout - you may use this log to keep track all security related activities. For example, append a "warning" log when someone is trying to hack the system especially the web base system.
• When certain system settings were missing and the system continues to work using default value - this is very important if you are distributing your system to more than one site. For example, the system administrator forgot to set the new sales email address.
• When user clicks on certain function which handles using stored procedure in the database - this is important because the user might already have clicked it multiple times and the system like no-effect. By creating log record into this table, the system administrator will be able to tell whether the stored procedure is running or not and carry out necessary recovery.

Table design:

CREATE TABLE tb_log (
    log_id uniqueidentifier NOT NULL,        --primary key
    log_type_id INT NOT NULL,                --type of log.
    workstation NVARCHAR(255) NOT NULL,        --the workstation name
    uid NVARCHAR(255) NOT NULL,                --the current user ID.
    msg NVARCHAR(max) NULL,                    --the details of the log
    remarks NVARCHAR(255) NULL,                --the remarks/procedure name.
    is_sent INT NOT NULL,          --if '1', means the log has been email to admin.
    app_id INT NOT NULL,                    --the application ID
    module_id INT NOT NULL,                    --the module ID
    modified_on DATETIME NOT NULL,            --the last updated date/time
    modified_by NVARCHAR(255) NOT NULL,    --the person who perform last update.
    created_on DATETIME NOT NULL,     --this is the current date/time when the log record is inserted.
    created_by NVARCHAR(255) NOT NULL        --the current user ID while creating this record.
);

The allowed values for the log_type_id field:

1. Error - this indicates that the system crashed at runtime and the value in the msg field is the exception details.
2. Information - this indicates that some processes have been kicked off and running smoothly.
3. Warning - this indicates that some certain system settings were missing or using default value but the system is still able to function normally.
4. Audit Log - this indicates that the log is security related or certain important data has been changed by the user.
5. Critical - this indicates the system crash is critical or someone is trying to figuring out the login ID or password and requires immediate attention.

Add-on module to be build on top of this log table:

• Email the log records to the system administrator (and mark "is_sent" with value of "1"). The email can be send in daily or weekly basis.
• If the log_type_id indicates that it is a "threat" or "hacking", the log record must be send immediately to the system administrator.

Note: for process logging, please refers to "Tracking the progress of a long run process" article.