Sample Databases

ComCap includes various SQL script files, newdb-xx.sql and storedproc-xx.sql where xxx is a database type, containing SQL statements to create a COMCAP SQL database with four example tables, and stored procedures (for some databases only) to add records to those tables and provide limited reporting.

The help pages for the specific databases explain how to use the SQL script files.

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.
capture_email	Seven columns designed to capture emails, with the four main headers, originating IP address and a single 8000 long field for the email body. Because there is no obvious unique key, a SQL identity column is used the key. MS SQL Server only at present.
capture_gps	Seven columns designed to capture GPS location information using the ComCap GPS CSV format. There is an event_time column which is used as the key with the title or channel name. MS SQL Server only at present.

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-mssql.sql).

ComCap handles two columns specially, serial_nr and event_time, if used. Note these column names must not be used for other purposes if ComCap is told to use them. 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.
capture_email_put	This stored procedure simply saves seven columns without any further processing
capture_gps_put	This stored procedure simply saves seven columns without any further processing

The storedproc-mssql.sql file also has several xx_lst sample stored procedures to list rows in each of the four sample tables by date range, and some SQL statements that can be used to find out how many rows have been saved to each table, and to truncate the tables to remove all rows.

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.