Audit Log Archiving

Beginning with CAPSYS Capture, rules can be set to determine when AuditLog rows will be deleted. You can also choose to write the rows to another database or to CSV files before deletion. This document details the code used to archive and then delete the rows.

The archive rules are set on the batch profile maps. Since every document must first exist as part of a batch, the documents inherit the rules from their batch. When a batch is created (either by a user scanning a batch in the Web Client or by an input module running in the CAPSYS Capture Server), a row is created in the QM_BATCHES table. When the batch reaches the Assembly event, document cases may be created. Each document created causes the creation of a related row in the QM_DOCUMENTS table. Each of these documents is related back to the QM_BATCHES table by the BatchCaseID column. The QM_BATCHES table also keeps a counter of the number of documents created.

When a case reaches a Stop event, the usp_delete_case stored procedure is executed. If the case is a document, the ChildCaseCompleteCt column in QM_BATCHES is incremented. If the case is a batch, the CompleteDateTime column in QM_BATCHES is set to the current date and time. Usp_delete_case then checks to see if CompleteDateTime is set and if ChildCaseCompleteCt = ChildCaseCt. If both conditions are true, then the batch has reached the Stop event and all the document cases have also reached Stop events. At this point, we can calculate the amount of time to keep the AuditLog records. Usp_delete_case sets the ReadyForDelete column in QM_BATCHES to 1 and sets the DeleteDateTime to the current date/time + the number of days to keep the records. Please note that the number of days to keep the records starts when the last case from the batch reaches a Stop event. As an example, assume that the ‘Number of Days to Keep Log’ is set to 45. A batch is created on 7/15/10 and 4 documents are created from it on 7/16/10. The batch reaches the Stop event on the Profile map on 7/16/10. The documents work through their Document Type maps and the last one reaches the Stop event on 9/1/10. The ‘ReadyForDelete’ and DeleteDateTime columns are set on 9/1/10. DeleteDateTime is set to 10/16/10 à 45 days after the last document case reached a Stop event.

Archiving and deletion of the records is handled by the Archive Thread in the CAPSYS Capture Server. This thread looks for rows in the QM_BATCHES table with ReadyForDelete = 1 and DeleteDateTime < the current datetime. It will retrieve a maximum of 20 cases and use an OrderBy to get the oldest cases first. (The CAPSYS Capture Server immediately terminates this query and processes these 20 cases. It will repeat this up to 100 times before allowing the Archive thread to sleep.) For each case returned, the Server does the following (in the text below, ‘BatchID’ is the CaseID returned from the QM_BATCHES table):

1. Check the Audit Log Retention Rules for this BatchID. If entries are archived to files, go to step a. If entries are archived to a database, go to step b. If entries are deleted, go to step 2.

1. The Profile specifies a root folder for the files. The Server creates a subfolder here by using the current date in the format ‘yyyymmdd’. Rows for the current batch are read from the QM_EVENT_TIMING, QM_FILINGS, Q_AUDITLOG, QM_DOCUMENTS and QM_BATCHES tables. The rows for each table are written in CSV format to a file of the same name. In other words, rows from the QM_EVENT_TIMING table are written to a file named QM_EVENT_TIMING.txt in the date folder created earlier in this step.
2. The Profile specifies a database connection. Rows for the current batch are read from the QM_EVENT_TIMING, QM_FILINGS, Q_AUDITLOG, QM_DOCUMENTS and QM_BATCHES tables. The rows for each table are written to a table named yyyy_ARCHIVE. In other words, rows from the QM_EVENT_TIMING table are written to the QM_EVENT_TIMING_ARCHIVE table.

2. To delete the entries, the CAPSYS Capture Server issues the following commands:

1. Delete from QM_EVENT_TIMING where BATCHCASEID = BatchID
2. Delete from QM_FILINGS where BATCHCASEID = BatchID
3. Delete from Q_AUDITLOG where BATCHCASEID = BatchID
4. Delete from QM_DOCUMENTS where BATCHCASEID = BatchID
5. Delete from QM_BATCHES where CASEID = BatchID
6. If one of the delete statements fails, the failure will be written to the TraceFile, but not to the AuditLog or to the Windows Event Viewer. The error message in the TraceFile will look like: “Failed to delete metrics rows for case [BatchID]. Command was: xxxx. Error was: yyyy.” The command should be one of the commands listed above.
7.  Since each delete statement is operating only on the rows for a single batch, the number of rows affected should be fairly small. The largest table will probably be Q_AUDITLOG and the number of rows for a particular batch will probably be in the hundreds. If you are having trouble with deletion of records, make sure that you have indexes on these tables. You can use the ‘planonly’ command in SQL Server to make sure that the delete statements will use the index. You can also use ‘select count(*)’ statements for each table to find out how many rows exist.