When you start using Logbox the amount of logged messages can add up quickly, leading to huge logfiles or database tables with many many logEntries. Logbox has many different appender types, and in some cases size of your logs is controlled by external systems such as lucee or for example the logrotate system in Linux. If you are logging to a file you could use a rollingFileAppender which limits the file size, and for this appender you can configure the max number of file archives.

We prefer to use a DBAppender, so I can create some nice display of the logged details in a user interface, but for limiting the size and creating some archive it seems we are out of luck. According to the docs there are no options for limiting the size or creating some archive. So how are we going to fix that?

Well, to start with – and that will satisfy quite a few users already -: the DBappender certainly has some rotation options. They just never made it to the docs (yet). The following options are available:

  • rotate which defaults to true
  • rotationDays which defaults to 30 days
  • rotationFrequency which defaults to 5 minutes

So if you are using the DBAppender, your logEntries will disappear automagically after 30 days. That might not be your favourite configuration. We would like to keep our entries for a much longer time, but we don’t want them to keep all of them in the main logging table. Our desired setup

  • keep the entries in our Table for 30 days (rotationDays)
  • move them to some archivingTable after these 30 days
  • delete them from our archivingTable after 730 deletionDays
  • we like to keep it simple: if our Table is called SomeLoggingTable, our archive table will be called SomeLoggingTable_archive but we make this configurable in the option called archivingTable

So time to investigate the DBAppender. We already copied the DBAppender because we had some more requirements for an ExtendedDBAppender, but if you only need some archiving you can start with a copy of the DBAppender. Actually it is quite easy to modify a DBAppender. You only need some extra properties and replace or override the doRotation() method. But let’s examine how the default rotation is handled in the DBAppender

  1. if a (base) Appender has finished processing its log queue it will fire an onLogListenerSleep event.
  2. The DBAppender has an onLogListenerSleepMethod which fires a rotationCheck() function
  3. The rotationCheck function checks your rotationInterval and once this interval has expired it will call the doRotation() function
  4. the doRotation function deletes your logEntries from the logTable after x rotationDays

So if we just change the doRotation function and add some properties so we know where to archive and when to cleanup the archive we are all set. Let’s start with the properties.

In the constructor init() we add two properties:

	function init(
		required name,
		struct properties = {},
		layout            = "",
		levelMin          = 0,
		levelMax          = 4
	){
		// Init supertype
		super.init( argumentCollection = arguments );

		// UUID generator
		variables.uuid = createObject( "java", "java.util.UUID" );

//....
		if ( NOT propertyExists( "table" ) ) {
			throw( message = "No table property defined", type = "DBAppender.InvalidProperty" );
		}
		if ( NOT propertyExists( "archivingTable" ) ) {
			setProperty( "archivingTable", getProperty("table") & "_archive" );
		}
		if ( NOT propertyExists( "rotate" ) ) {
			setProperty( "rotate", true );
		}
		if ( NOT propertyExists( "rotationDays" ) ) {
			setProperty( "rotationDays", 30 );
		}
		if ( NOT propertyExists( "rotationFrequency" ) ) {
			setProperty( "rotationFrequency", 5 );
		}
		if ( NOT propertyExists( "deletionDays" ) ) {
			setProperty( "deletionDays", 730 );
		}
		if ( NOT propertyExists( "schema" ) ) {
			setProperty( "schema", "" );
		}

		// DB Rotation Time
		variables.lastDBRotation = "";
		return this;
	}

Now we have these properties ready we should just create our `doRotation()` methods which has three parts:

  1. transfer expired log entries to our archiving table
  2. delete the same entries from the log table
  3. delete very old entries from our archiving table

Code looks like this:

	function doRotation(){

		var qLogs      = "";
		var cols       = getColumnNames();
		var archivingDate = dateAdd( "d", "-#getProperty( "rotationDays" )#", now() );
		var deletionDate = dateAdd( "d", "-#getProperty( "deletionDays" )#", now() );		

		// archive to archiveTable after rotationDays
		queryExecute(
			"INSERT 
			INTO #getProperty("archivingTable")#
			( SELECT * from #getTable()#
			WHERE #listGetAt( cols, 4 )# < :datetime )",
			{
				datetime : {
					cfsqltype : "#getDateTimeDBType()#",
					value     :  "#dateFormat( archivingDate, "mm/dd/yyyy" )#"
				}
			},
			{ datasource : getProperty( "dsn" ) }
		);
		// delete content from logTable after rotationDays
		queryExecute(
			"DELETE
			FROM #getTable()#
			WHERE #listGetAt( cols, 4 )# < :datetime",
			{
				datetime : {
					cfsqltype : "#getDateTimeDBType()#",
					value     :  "#dateFormat( archivingDate, "mm/dd/yyyy" )#"
				}
			},
			{ datasource : getProperty( "dsn" ) }
		);		
		// finally delete from archiveTable after deletionDays
		queryExecute(
			"DELETE
				FROM #getProperty("archivingTable")#
				WHERE #listGetAt( cols, 4 )# < :datetime
			",
			{
				datetime : {
					cfsqltype : "#getDateTimeDBType()#",
					value     :  "#dateFormat( deletionDate, "mm/dd/yyyy" )#"
				}
			},
			{ datasource : getProperty( "dsn" ) }
		);
		return this;
	}

We choose to move the logEntries to some other table. Our log table can grow quickly and if you don’t add some indexes, it can be quite slow to access. By moving the entries to another table performance problems will be avoided when analyzing our recent data.
The only thing we had to do now is reconfigure the appender in our LogBox config, e.g:

"extendedDBAppender" : {
	class="models.utils.logbox.ExtendedDBAppender",
	properties =
	{
		dsn 				= "myDataSource",
		table 				= "dbLogMyTable",
		autocreate 			= true,
		async 				= true,
		rotationFrequency	= 60,
		rotationDays		= 30,
		deletionDays		= 730
	}
}

This will do fine for us. You could improve on this simple rotation, for example by sending the archives to another datasource, or create different archiving tables based on the logdate, or just dumping them to some backup file. The possibilities are endless once you know how simple you can override the doRotation()method.