Extension Documentation

Chapter 5. Enhanced Auditing Feature
Prev Part I. Macrotone Issue Tracker Next

Chapter 5. Enhanced Auditing Feature

Table of Contents

Change History
Change History List
Change History Entry
Triggers
Triggers List
Trigger Entry

The 'basic' Joomla installation provides some tables with a record of who made the last change to an item (i.e. an article or a web link etc.) and when they made the change. Unfortunately it doesn't inform one of what was changed. It might have been something as simple as a correction of a typological error, or it may have been some thing much more extensive. Some sites require much more information about any given change, especially sites that may house data that could be considered 'sensitive'. The enhanced auditing feature component tries to address this requirement and provide a more comprehensive and extensive audit over all of the changes that have occurred upon the Issue Tracker table records.

This feature makes use of the underlying features of the database and creates 'database triggers' that record the changes after they are made in the database. In this way there is no need to change any of the Joomla libraries or core code, which avoids any problems if/when the core code is changed between releases.

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. Most if not all databases provide support for database triggers. Whether a specific database user can create these triggers will depend upon the permissions granted to the database user. The database user that has to have the correct permission in the Joomla environment is the user that is used to perform all database connects as defined in the Joomla installation itself.

[Note]Note

Different databases implement database features such as database triggers in different way, and often with slightly different syntax. For that reason the current version only supports MySQL databases, the most commonly used database for Joomla systems.

The Issue Tracker Audit database triggers (by default) are all created as 'AFTER' triggers, which means that they all are executed 'after' the record has been saved to the database. The implication of this is that no information is saved IF the record itself is not saved in the database. It also means that for 'INSERT' triggers, any information automatically created by the database as a consequence of the insert, such as unique record identifiers are also recorded in the change history.

[Note]Note

It is possible to create BEFORE triggers although in practise they do not always hold all of the required information. An example of this would be the unique identifier (id) field which is usually populated automatically by the database upon a record insertion.

The 'back-up' basic audit feature of Issue Tracker uses 'BEFORE' triggers to populate required database fields if they are not populated by the component prior to storing in the database.

The change history created by the enhanced audit can be used to provide documentary evidence of the change activities that have affected at any time a specific table, or table column of any of the underlying Joomla database tables. The feature will prove useful for companies who need to apply audit trail (log) functionality on their Joomla sites.

For some organisations maintaining a complete audit trail for your database is important not only for internal analysis and process optimisation, but also for compliance with industry standards and regulations. One can view the ongoing activity history of a table record, including what action was taken, by whom, and the date and time the action occurred.

Complying with industry standards is important, time-consuming and expensive. Leveraging the Change History information tracking capabilities can reduce the time and effort it takes to gain and maintain any required certification, and dramatically increases the organisation's chances of successfully passing audits.

Key features:

  • Any Issue Tracker table can be monitored.

  • Any number of the columns of an Issue Tracker table can be monitored.

  • No Joomla core code is modified.

  • Transparent to any other installed Joomla extension.

  • Transparent to any site users.

Please note that the enhanced audit feature is solely an administration product. It has no front end access at all and prevents modification of any collected change history even by the Joomla Administrator(s) themselves.

[Important]Important

It is still possible that a database administrator can modify the underlying data contained in the change history table. Tools such as phpadmin can still function and have the ability to disable triggers, make data changes and then enable the triggers, thus bypassing the change tracking. This is something that no Joomla component can possibly protect against.

Change History

The change history or Audit records result from the database triggers capturing information upon the monitored tables. There are three types of activity that can be captured:

ActivityStored Information
UpdateThe data in the fields both before and after the change is recorded.
InsertOnly the 'new' information is present to be stored.
DeleteThe 'old' information for the field is stored.

In addition to the field changes the following information is also stored for each 'change' or 'audit' record:

  • Table name

    The name of the Issue Tracker table being modified.

  • Column (Field) Name

    The name of the specific field being modified.

  • Action

    The specific activity being performed.(Update, Insert or Delete) as described above.

  • Record Key

    The primary key (where present) of the monitored table used in the SQL command. For example: WHERE 'id' = '6' where id is the primary key of the table.

  • Record Key List

    Where a monitored table has an 'Alias' field (often also known as a 'slug') this is also captured. Where this does not exists the primary key vale of the record is stored. This is often the same as the 'Record Key' above.

  • User details

    Where it can be determined the details of the user (ID) name (or guest) that performed the activity is recorded. Unfortunately this is not always determinable and in these situations the Super User id is stored. See the section about determining who made a change later in this document.

  • Date and Time of change

    Date Exact date and time (time stamp). Stored in the database as a UTC value (Joomla default).

Change History List

From this view you can view the actual audit records. The records may not be changed but they may be deleted.

The Change History list display shows all of the changes captured by the database triggers. It is possible to filter upon the individual tables to display the changes made upon the specified Issue Tracker table.

Figure 5.1. Change History List Display

Change History List Display

Change History Entry

The change history record is the actual details of a specific field changed in a table. The change record itself contains the details of the table and the specific table field (column) that was changed and the details of the specific change.

Changes may be the result of a new record being entered into a table, in which case the 'NEW' field value is populated, there being no 'OLD' value existing prior to the change. An update of a record will result in both a before (OLD) and an after (NEW) value being present for a field. The 'OLD' value may of course be NULL (empty) in which situation the NEW value would definitely not be NULL, or alternatively the 'NEW' value may be NULL in which case the 'OLD' value would not be NULL. When a record is deleted from a table there is no ''NEW' value, but there may be an 'OLD' value.

If a value field is of a character type then the displayed information is a text box and scroll bars would be present to enable one to inspect the record entry.

Figure 5.2. Change History Entry

Change History Entry

The change history records can not be edited by the component. The text area boxes are 'read only' which means that any changes made on the screen would be completely ignored.

Prev Up Next
EMail sending of overdue notifications Home Triggers
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries