Database

Capture Settings are set-up separately for each capture channels. Once these settings have been specified, OK or Apply should be clicked. This tab specifies Database settings.

ComCap allows captured data to be saved in a database table. Currently only databases supported by Microsoft Data Access Components (MDAC or ADO) may be used, with testing using Microsoft SQL Servers 2000 and 2005. In theory Access (Jet) could be used, but since SQL Server 2005 Express is free (database limited to 2 gigs), there seems little point. MDAC may be used with any database with an ODBC or OLE DB Provider driver.

For Windows 2000, MDAC 2.8 SP1 (free download from Microsoft MDAC Downloads) should be installed before attempting to configure database support in ComCap. Windows XP SP2, Windows 2003 and Vista include MDAC as part of the core operating system.

ComCap has also been successfully tested with MySQL 5 using the MySQL ODBC 3.51 driver.

If ComCap is configured to capture to a database using the Background Service, the service must be set-up with a local PC account name and password, see Capture Logging.

ComCap includes two SQL files, newdb.sql and storedproc.sql that contain Microsoft SQL 2000 and 2005 statements to create a COMCAP SQL database with four example tables, and stored procedures to add records to those tables and provide limited reporting.

To create the sample database, first access SQL Query Analyser (for SQL 2000) or SQL Server Management Studio (for SQL 2005). Then open the newdb.sql file and change the FILENAME statements with disk paths for the database data and log files to those for the SQL server, then Run all statements into SQL. Assuming there are no errors, open storedproc.sql and Run that in to add the stored procedures. Beware that running in newdb.sql more than once will delete any data already captured by first dropping the old tables, before it creates fresh empty tables.

capture_whole	Three columns: serial_nr, event_time and info, where the whole record is stored. Another application or SQL stored procedures could be used to further analyse the captured data. serial_nr and event_time provide the primary key, see below.
capture_fixed	16 columns for telephone call data record logging: serial_nr, event_time , and various call fields. serial_nr and event_time provide the primary key.
capture_csv	Six columns designed to match the comma separated fields created by the ComGen test data generator, using two columns as the key.
capture_sonicwall	24 columns designed to match the Sonicwall firewall syslog variable named columns. Note the SQL column names are longer than the syslog names. Because there is no obvious unique key, a SQL identity column is used the key.

While ComCap can be used to write directly to these, or any other, SQL tables, it is generally recommended that stored procedures are used instead since these allow data validation and manipulation to be performed using SQL functions and commands. For instance, dates and times may be manipulated using SQL string functions into unambiguous formats that SQL will accept, ideally ISO format (yyyy-mm-ddThh:mm:ss.zzz). All stored procedures called by ComCap must return a single row resultset with two columns, retcode and retmess, with retcode set to 100 for success, anything else results in ComCap reporting an error with the retmess (this is illustrated in storedproc.sql).

ComCap handles two columns specially, serial_nr and event_time which should be the first and second columns in the parameters for the stored procedure, if used. These extra columns are updated with the channel Serial Number and the time that the event was added to the database, if specified, and are usually the unique key for the table. The Serial Number is the same as used for adding escaped text to the captured data and the starting number and length may be specified on the Logging tab. While these two special columns will provide a unique database key where there is nothing guaranteed unique in the captured data, it is generally recommended that the Serial Number and event time are added as escaped text to the captured line, and then selected in the data format as columns for the database, this has the advantage of keeping the logged files the same as the database.

There are six sample stored procedure supplied to put data into the various sample tables:

capture_whole_put	This stored procedure simply saves three columns without any further processing.
capture_fixed_put1	This stored procedure is designed to process CDRs in the sample-bts7#2.txt file where call_time is passed as HH:MM to which the current date is added and call_len as HH:SS, both of which are stored in DATETIME columns.
capture_fixed_put2	This stored procedure is designed to process CDRs in the erikkson.txt file where call_time is either YYMMDD HHMM or MMDDHHMM both of which are converted into DATETIME with the years, and call_len which seconds and also converted to DATETIME.
capture_csv_put	This stored procedure simply saves six columns without any further processing.
capture_sonicwall_put	This stored procedure simply saves 15 columns without any further processing.
capture_sonicwall_put2	Similar to above, but parses IP address and port and source into separate columns, ie 192.168.1.109:3743:LAN is saved to three separate columns to ease selection.

The storedproc.sql file also has four xx_lst sample stored procedures to list rows in each of the four sample tables by date range.

To set-up ComCap to save captured data to a database, the SQL table and ideally stored procedure must be created first, to allow ComCap to read the column and parameter definitions. It is also recommended that some data is first captured, which will considerably ease setting the Data Format that defines how columns will be taken from captured data.

This option selects the technology that ComCap uses for database access, currently only ADO using Microsoft Data Access Components (MDAC) with OLE DB or ODBC drivers. Future ComCap releases may support more efficient database access technology.

The Specify Database Button displays the MDAC Data Link Properties dialog. From there, first select the OLE DB provider, usually SQL Server but perhaps ODBC or Jet. On the Connection tab, select or enter a SQL server name (it may be available in the drop down box, but may need a UNC name entered), then specify authentication to logon to the database, and finally the actual database to be used. Click Test Connection to make sure SQL is working, then OK. ComCap will then open the database and the details will be displayed to confirm it’s all working OK. More than one channel may capture data to the same database table, but each channel will use a separate link, to allow saving in parallel.

Note the database and optionally stored procedure to add records must already exist before ComCap can be configured.

Insert into Table	A Database Table selection field will appear, with a drop down box listing all the tables in the database, from which one should be selected. ComCap will use the SQL INSERT statement to add records directly into this table, using the columns specified in the Data Format.
Stored Procedure	A Stored Procedure selection field will appear, with a drop down box listing all the stored procedure in the database, from which one the one created for this channel should be selected. Note that not all databases support stored procedures.

As mentioned above, ComCap supports two special database columns, serial_nr and event_time. This option has two tick boxes to specify the database table has one or both of these columns as columns. In the case of stored procedure, these columns must be the first one or two parameters, in that order.

Ticking this option will cause an alert to be triggered for database problems, specifically if the database can not be opened.

Ticking this option will temporarily stop capture if the database becomes unavailable. ComCap will continually attempt to restart capture, using the delay specified on the General tab. Note that capture will not initially start until the database has been successfully opened. If this option is not used and the database becomes unavailable, Comcap will temporarily save rows that could not be written to the database and then update the database when it's successfully opened. Note this data is lost if ComCap is exited or the Background Service version stopped, it's intended for short database interruptions, such as a server or network restart.

This option specifies the database connection timeout in seconds, defaulting to 30 seconds, with an minimum of 10 seconds.

This option allows the database connection to be closed after an idle period in seconds with no new data captured, up to a maximum of 600 seconds. The database will be automatically re-opened when new data arrives. Setting zero seconds will leave the database connection open continually, which is normally perfectly OK.

ComCap has been successfully tested with one network channel capturing and writing 50 rows per second to a database table using SQL Server 2005 (running on a separate computer), while four other channels were capturing more slowly and writing to different tables.

Database problems are unfortunately inevitable. The database server might get rebooted by Windows Update (despite all settings saying don’t reboot), network issues could break the connection, and many other reasons may cause captured data to fail to be written to the database. ComCap therefore provides a recovery feature Database from Log, accessed from the right click menu in the main window, which allows previously captured data to be added to the database, from an old Capture Log.

When clicked, a File Open dialog allows the capture log to be selected. A dialog then warns ‘Confirm Read Capture Log and Add X Rows to Database for (channel)?’ If OK is clicked, all lines in the Capture Log will be added to the database, using the settings for that channel. If the extra serial_nr column is used this will have a normal increment Serial Number, but the event_time column will reflect the time the row was added, not when it was captured. This is one reason why it’s better to add these extra columns to the capture log, so they can be imported accurately later. To avoid duplicate rows, it may be necessary to manually edit a copy of the capture log to remove any lines successfully added to the database.

This recovery feature can also be used for testing database Data Format settings, without needing to wait for new data to be captured.