You need an application log for your systems

Published on: 24th Jul 2012

Updated on: 17th May 2021

Overview

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

• System crashed.
• 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.
• The scheduled process has started.
• The scheduled process has ended.
• The data exchange process has been triggered.
• The system detected some issue in the data and made a report.

Our approach

The table that keeps the application log,

CREATE TABLE tb_log (
    log_id bigint 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 sent.
    app_id INT NOT NULL,                --the application ID
    module_id INT NOT NULL,             --the module ID
    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:

• Error - this indicates that the system crashed at runtime and the value in the msg field is the exception details.

• Information - this indicates that some processes have been kicked off and running smoothly.

• Warning - this indicates that some certain system settings were missing or using default value but the system is trying to function normally.

• Audit Log - this indicates that the security related activities have occurred or certain important settings have been changed by the user.

• Critical - this indicates the system crash is critical. For example, the application license is expiring soon.

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 sent on a daily or weekly basis.
• If the log_type_id indicates that it is an audit log which could be a threat or hacking, the log record must be sent immediately to the system administrator.

On sending notification, please refers to this article: design a custom mail process

Cleanup the application log

The application log will grow every second and it will become irrelevant after a few months. So, you may want to have a scheduled task to perform the cleanup periodically.

This can be done by with the following stored procedure which keeps the latest 100k records:

create proc mgt_cleanup_log_table
as
begin
    declare
        @min bigint
        , @max bigint

    select
        @min= isnull(min(log_id), 0),
        @max = isnull(max(log_id), 0)
    from tb_log

    -- leave 100k records in the log table
    set @max = @max - 100000

    while @min < @max
    begin
        select @min = @min + 1000
        --print 'Deleting log_id < ' + cast(@min as varchar)

        delete from tb_log
        where log_id < @min + 1000
    end
end
go

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.