Good practices for storing logs

Question

• What information should be stored?
• It’s good practice to use TRIGGER in the database for storing logs or via code is something safer and easy to maintain?

Answer 1

Good logging practices follow two basic principles:

1. A log should be efficient
2. A log should be safe

Efficiency of a log

The log must contain verbose values, for example key=value (name="john doe"), so that it can be read by a human. The recording of binary information in logs should be avoided.

The timestamp should always be recorded at all events. The correct date/time is essential to understand if something has gone wrong.

Unique ID’s must be used for various events/transactions. The use of UID, and its logging, makes it possible to uniquely identify a log. It is also very useful in debug processes.

As a basic principle the following information should be kept:

• Who (who)
• What ()
• When (when)
• Where (where)

Security of a log

A log to be safe, must be ensured ideally at database level. When logging is ensured through programming, there is the possibility for the programmer to forget to log a certain event. If the log is ensured at the level of the database, the recording of all events is ensured natively.

A log should follow the WORM principle (Write Once Read Many).

A log must have a checksum/hash so that its consistency can be assessed.

Ideally, the log should be stored off-site (on another machine) so that it can be compared retrospectively with the "original log".

Disclamer: The best practices I indicate here are suitable for critical business applications.

just my 2 cents

Answer 2

Trigger there is an extra SQL consumption, because it will be monitoring everything, then an Overload in all processes. I do not recommend. Of course, developing external software is better, but as long as you can develop, know a language, etc. But if possible opt for external software instead of Trigger. NOTE: Trigger and Cursor in MSSQL are 2 things that consume many resources, avoid using them always.

What to store? I don’t understand your question, it depends on you, what do you think is important? Is it some software we’re talking about? You must remember some factors:

• Is this information important? If so, how should I store it? (varchar, int, etc)
• How long should I store the information ? If the information has a useful life, that is, after X time will serve no further purpose, then create a purge tool to remove old information.
• What will be the size of this table, each field exists its consumption, eg: int=8bytes, varchar consumes in bytes its own size, nvarching to an Overload of +1bytes per field. , summed all columns, multiply by the amount of records, so you can know which size, 20mb, 200mb, 2gb per day, week, month.
• Another tip, you have to think about it, if it’s just a log, I imagine it’s an audit of everything the user is doing, so avoid creating indexes in this table, or think about it before creating, every time you have an index there is a consumption to enter records.

If you want to specify better, show an example of the table.

Answer 3

All the ways are correct, however as the application grows your bank will inflate and your audit tables will get giant and slow.

In various applications for which I worked that had audit, all ended up having this same problem. At first it seems like a good idea but in the long run it ends up becoming a huge headache.

In new applications I try to abstract this problem (not only this type of problem, but also others whose application has no responsibility to deal with) using a Saas. This is a great solution because you will let a corporation specialized in auditing solve your problem. You simply have to consume an API that they will make available to you, and they will solve your problem. In addition, all audit data will be outside your database as it will be within this service and it will be their responsibility to resolve issues of slowness etc, and you will focus on what is important, your business rule.

I recommend you use the Auditingship, Saas audit. Some companies are already using it and are enjoying it very much. Some advantages:

• is quick to make a "Hello Word", matter of 5 minutes or less;
• you can use with any language that consumes REST, it even shows some examples with Javascript and Ruby;
• simple and dynamic documentation.

Answer 4

It depends a little on the technology you are using but @guerra is right.

Because Trigger makes maintenance difficult and you will have to implement Trigger in n tables. It is not practical if you have many tables and generally triggers do not facilitate maintenance and debugging. On the other hand, it’s a fast and practical method if you have a few tables being audited, for example if you’re just auditing a table of logons. Another advantage is not having to touch legacy or third-party codes.

By code would be the "most correct" from the OO point of view when you are creating a system. You can simply create an Auditable class for example and inherit your BD access classes from that class, automating the audit process, reusing code, and centralizing the maintenance of that functionality in that class. You can use Extension methods or anything like that for the same purpose.

If you use it. Net you can use Log4nnet for example which is a framework that already automates you much of the work of creating logs of your applications. Look to see if the technology you use no longer has any similar tool.