Smartsite Documentatie > Advanced > Concept > Logging > System status reports

System status reports

Release 1.3 - ...

A system administrator can consult the logging information using a Manager action like View Log Entries. There may however be a vast amount of log entries, making it hard to spot the entries of interest.

The system administrator may inspect the logging only occasionally, and is probably not willing to analyse hundreds of new log entries. Instead it is possible to write queries that perform the analysis, and that produce status reports or health reports for various operational aspects of the system.

This approach builds on the use case where detailed logging goes to log files, where important log events are mailed, and where logging related to regular system maintenance is written to the database. The status reports are generated from the information in the database.

Note that the approach described here is site building and not configuration of some prebuilt tool.

Example

It is expected that module X performs a scheduled import once each 12 hours. When done the module writes a log entry to the database, amongst other entries. A query looks for the newest log entry of the module, of type information, and with some configured success message, for example 'Module X: successful import'.

Smartsite SXML CopyCode image Copy Code 
<se:sqlquery mode="scalar" save="TimeStamp" default="">
    <se:parameters>
        <se:parameter name="sql">
            SELECT TOP 1 TimeStamp FROM vwLogEntries
            WHERE TypeName='Information'
            AND CategoryName='MODULEX_RECORD'
            AND Message=?:MessageForSuccessfulImport
            ORDER BY Nr DESC
        </se:parameter>
        <se:parameter name="params">
            <se:collection>
                <se:member name="MessageForSuccessfulImport">
                    {buffer.get(MessageForSuccessfulImport)}
                </se:member>
            </se:collection>
        </se:parameter>
    </se:parameters>
</se:sqlquery>

This is followed by interpretation of the result. The result is summarized to a simple failure indicator -1 if the last successful import was more than 24 hours ago, or if no successful imported is recorded at all. The result is summarized to a success indicator 1 if the last successful import was within 24 hours as configured in $SuccessfulImportWithinNHours.

Smartsite SXML CopyCode image Copy Code 
<se:if expression="$TimeStamp EQ ''">
<se:then>
    {buffer.set(Result, -1)} <!--// Failure. -->
    {buffer.set(Description, 'No successful imports are recorded.')}
</se:then>
<se:else>
    {buffer.set(Hours, convert.toint(math.round(datetime.hoursbetween(datetime.now(), $TimeStamp))))}
    {buffer.set(Description, 'Number of hours elapsed since the last successful import: ' + $Hours)}
    <se:if expression="$Hours LTE $SuccessfulImportWithinNHours">
    <se:then>
        {buffer.set(Result, 1)} <!--// Success -->
        {buffer.set(Description, $Description + '. This is within ' + $SuccessfulSynchronizationWithinNHours + ' hours.')}
    </se:then>
    <se:else>
        {buffer.set(Result, -1)} <!--// Failure -->
        {buffer.set(Description, $Description + '. This is more than ' + $SuccessfulSynchronizationWithinNHours + ' hours ago.')}
    </se:else>
    </se:if>
</se:else>
</se:if>

And this is followed by some presentation: thumbs up or thumbs down, SUCCESS or FAILURE, green traffic light or red traffic light etc. The presentation is on a restricted page intended for the system administrator.

Other queries

The status page may contain the result of various checks, for example:

  • The above check for a successful import in the past 24 hours.
  • A check if there are module error messages, hence log entries of type Error and category MODULEX_RECORD. There may be old entries that are no longer relevant because there was a successful import since. The query should therefore include a criterion to test that the error entry is newer than the success entry. The test can be based on the Timestamp column or on the Nr column; the latter yields a better performance.
  • A check whether the import resulted in an expected number of content items. For example if it is expected that the import results in at least hundred items of a certain contenttype add a check that this number of active items is indeed present.

Summarizing results

The status results of a particular module could be summarized to a single result:

  • 1 / success / green traffic light, if all module results indicate success
  • -1 / failure / red traffic light, if all module results indicate failure
  • 0 / attention / yellow traffic light, in all other cases, specifically in case of a mixture of success and failure indicators.

The status results of multiple modules could in turn be summarized to a subsystem status result, and so on.

Prerequisites

  • The queries make use of column Timestamp. As the number of log entries grows it is important for performance to have this column indexed. The standard site already contains the index; an existing site database may lack the index. In that case create a non-unique index on table LogEntries column Timestamp.
  • The above example makes use of database view vwLogEntries, as an alternative to querying a join of the tables LogCategories, LogEntries, LogEntriesLogCategories and LogTypes. The view is part of the standard site. For older site databases this may again not be the case. Consider creating the view below, which is the standard view.

SQL CopyCode image Copy Code 
CREATE VIEW vwLogEntries AS
SELECT
    le.Nr,
    le.Timestamp,
    le.LogLevel,
    le.Message,
    le.ContextData,
    lt.Nr AS TypeNr,
    lt.Name AS TypeName,
    lc.Nr AS CategoryNr,
    lc.Name AS CategoryName
FROM LogEntries le
    INNER JOIN LogTypes lt ON le.NrLogType = lt.Nr
    INNER JOIN LogEntriesLogCategories lelc ON le.Nr = lelc.NrLogEntry
    INNER JOIN LogCategories lc ON lelc.NrLogCategory = lc.Nr

  • If the index on timestamp has been added and the performance of the queries is still poor it may be required to update database statistics. Logging typically starts with three empty log tables, and table LogTypes containing a few records only. The database may create statistics based on this initial situation, and establishes query execution plans bases on these statistics. As the tables start to fill the initial statistics are no longer suitable. If the database continues to use the out-of-date statistics the execution plan may be poor and the resulting performance may be poor. In that case update the statistics. The statements below do the job for SQL Server.

SQL CopyCode image Copy Code 
UPDATE STATISTICS LogCategories;
UPDATE STATISTICS LogEntries;
UPDATE STATISTICS LogEntriesLogCategories;
UPDATE STATISTICS LogTypes;

---
 

Advanced