CVSNT 2.5.03 Auditing Tutorial

Author: Bo Berglund
Notice:
This tutorial is written as a help for adding auditing to a CVSNT 2.5.03 installation.
The actual installation of the CVSNT server is covered by another page of mine.
This tutorial is not yet complete, I have trouble understanding some of the database table columns so I am in need of help with that. Consider this page as a beta of a tutorial for the time being.

Table of contents
CVSNT auditing
Database backend selection
Database structure
SQLite database configuration
Microsoft SQL Server 2000 or MSDE2000 configuration
Generic ODBC configuration
Getting data from the audit database

LINKS
CVS Related links:
Innosetup Installer for CVSNT
CVSNT 2.5.03 installation tips
CVSMailer homepage, Automatic email on commits and other events
ViewCvs Installer download

SQLite:
SQLite homepage
SQLite Database browser A simple database manager application for SQLite
SQLite Spy A graphic interface to the SQLite databases
SQLite Explorer A Query Analyzer style interface to SQLite3
SQLite3Explorer A comprehensive and fast AQLite database browser
SQLite ODBC driver This driver makes it possible to access SQLite from any database aware application

CVSNT auditing

The auditing system in CVSNT is based on a simple database table structure, which is created by CVSNT when the auditing is configured (see below). The purpose of the auditing is to record the CVS operations in such a way that it will be possible extract reports in a very fine-grained fashion at some later time. This includes the timestamping of tag and branch creation and modification, which is not possible with standard CVS. In order for the auditing to work properly CVSNT must have read/write access to the database that will hold the auditing data. For this CVSNT has been prepared with a number of database methods:

MySql
SQLite
Postgresql
Generic ODBC
SQL Server / MSDE

Since I don't have access to MySql or Postgres I will only discuss the other three database types here, but the handling of the others will be pretty much the same anyway.

Database backend selection

The selection of database server backend type is more or less a user preference. It depends on what you are comfortable with and have available. Since CVSNT supports ODBC access there is in effect no limit to the possible database server types you can use.
CVSNT has native support for MySql, SQLite, Postgres and Microsoft SQLServer 2000 and its redistributable MSDE2000 version.
Since I am daily working with SQLServer this is my preferred choice, but for testing purposes and to get acquainted with the auditing functions I can recommend SQLite, since it has a very small footprint and is rather capable.
SQLite has some drawbacks though, mainly because it is not a database server. Instead when you use SQLite the database files are accessed directly through the SQLite dll and there is no concurrency possible. The process will lock the database for the duration of the operation thereby locking out any other calls. This will of course not happen with the server type databases, but they will instead require a more substantial installation and maintenance effort.
Please observe that if you are watching the audit database using for example the "SQLite Database Browser" then the database is also locked and noone can perform any cvs operations! For this reason SQLite is really only useful for a small operation where the likelihood of concurrent cvs calls is small!
Note that SQLite is supported right from the CVSNT installation files, there is no need for any extra download or installation in order to start using auditing if you select SQLite!

Database structure

Irrespective of the type of database server used for auditing, the table structure is the same.

DBDiag
The database tables are written like this:

"SessionLog" is written for every user command received by the CVSNT server. There will be one record for each user command.
"HistoryLog" is written in a way I don't yet fully understand. There seems to be two records for each file committed.
This table needs some explanation by Tony Hoyle...
"TagLog" receives data for each tag operation for each file. There will be one record for each file being tagged.
"Commitlog" receives data for each commit operation for each file. There will be one record for each file being committed.

Note that the data tables hold a reference called "SessionId" to the "SessionLog.Id" column. This is how the various entries are tied together and is the basis for any query into the database. If you join on these identifiers then you can get all data specific to a certain cvs operation.

Known problems

The CVSNT auditing has some problems that could have been solved in later versions, but here goes:

2.5.03.2260
In this build there is a small problem concerning the auditing and branches:
Whenever a new file is added on a branch and committed this is not recorded in the auditing database. The bug has been reported but no word has come from the developer that it is fixed or will be fixed.
FIXED!
I have now tested this against the 2.5.03.2382 CVSNT server and it now looks like the bug has been fixed. :-)

SQLite database configuration

The SQLite database is the simplest to use with CVSNT. You need to do the following preparation steps:

With Windows Explorer create a folder where the database should reside, for example C:\CVSREPOS\Audit
Then create a new empty file by rightclicking in the new folder and selecting "New/Text Document"
Give the file the name "CVSNT" and accept the question to change the extension.

Now the remaining tasks are done inside the CVSNT control panel.
Go to the "Plugins" tab and select the bottom line "Repository auditing extensions" and click the "Configure" button.

Check the checkbox "Plugin enabled"
Select the database type as SQLite
Enter the full path to the file you created above into the "Name" box
Click the button "Test Connection" to verify that CVSNT is able to handle the database
Click the button "Create Tables" to initialize the database
Check all checkboxes in the Logging frame, this will get you full auditing
Click OK followed by "Apply" on the following page to enable the auditing.

Now the auditing is enabled on your CVSNT server and the various operations will be recorded in the database.

Microsoft SQL Server 2000 or MSDE2000 configuration

If you are a Microsoft Visual Studio programmer then chances are high that you are already familiar with the SQL Server 2000 or the MSDE 2000 database servers. In this case you could use this type of database for your auditing. The advantages are that you will be using a full-fledged server with support for concurrency and you are probably already familiar with the tools to extract data from the database.
The following initial steps are needed before you begin:

Start SQL Server Enterprise Manager and connect to the server you will use for auditing
Create a new database named CVSNT (or whatever you like)
Create a database login for this database with DBOwner permissions (User CVSNT with some suitable password)

Now the remaining tasks are done inside the CVSNT control panel.
Go to the "Plugins" tab and select the bottom line "Repository auditing extensions" and click the "Configure" button.

Check the checkbox "Plugin enabled"
Select the database type as "SQL Server / MSDE"
Enter name of the database into the "Name" box
Enter the database server name into the "Host" box
Enter Username and Password as defined for this database above
Click the button "Test Connection" to verify that CVSNT is able to handle the database
Click the button "Create Tables" to initialize the database
Check all checkboxes in the Logging frame, this will get you full auditing
Click OK followed by "Apply" on the following page to enable the auditing.

Now the auditing is enabled on your CVSNT server and the various operations will be recorded in the database.

Generic ODBC configuration

If you use any other than the natively supported database types then you can always set up an ODBC connection and then CVSNT can audit into virtually any database type there is.
Just make sure to create a System DSN so it will be available for the CVSNT service. Then use that in the same way as is described above for the native connections.

Getting data from the audit database

Here is where the fun starts! When you have the auditing data inside the database you can play with it using standard database queries anyway you like to! Just open the appropriate Query Analyzer type program and enter your SQL queries there.
I have listed links to a number of tools appropriate for SQLite above, these all come with some form of query entry and execution function. For the SQL Server type database the Microsoft Query Analyzer is a very good tool for analyzing the database contents.
But you can also create stand-alone programs to extract data from the database more easily. For example you could create a program that can be used bya a person who is not a database expert and does not know any SQL at all. It is possible in almost all programming languages, they just need some database support in the form of ODBC or similar.

Afterwords

This tutorial is written 2005-12-30 and is based on CVSNT version 2.5.03.2182.
I have also checked the bug mentioned above on 2006-11-22 with CVSNT 2.5.03.2382
The test system is Windows Enterprise Server 2003 with SP1 installed running in Virtual PC 2004 SP1 on my development PC. The server is not member of a domain.

Comments? Send me a message: Email Bo Berglund