Running a Query on an Archived File

This section refers to the virtual database DemodataV you created under Creating a Virtual Database.

The qdmg script sets selected tables in the virtual database to point to audit records in the current view file. The default path for this file is C:\<installation directory>\Audit\data\amlog. As explained in this section, you can reset the path to an archived file if you know its name.

Archived file names are based on creation date, yyyymmdd.nn, where yyyy is year, mm is month, dd is day, and nn is number of archived file that day, starting with two zeroes. File names end in a capital V. The default folder for archived files is C:\<installation directory>\Audit\Arch.

When an archived file is compressed, it moves to a different folder, the default for which is C:\<installation directory>\Audit\Comp, and the V in the file name changes to Z. When the file is decompressed, it returns to the Arch folder and the Z changes back to V. As with queries within AuditMaster, direct queries run only on uncompressed records.

The method described in this section uses two SQL scripts:

n	The first script sets the virtual database to point to an archived file instead of the current view file.

n	The second script resets the virtual database to its original state so that queries again return results from the current view file.

The following steps demonstrate these scripts using the virtual database DemodataV created earlier. The examples are intended to illustrate how you can write your own versions of these scripts.

‰	To reset the virtual database for an archived file query

1	To use these steps, you need an archived file. Open AuditMaster, right-click the Current View File, and select Archive.

AuditMaster moves current audit records to an archived file.

2	Double-click the Archived Files node to open it, then right-click the node and select Refresh All.

The newly created archived file appears in the list.

3	Note the name of the file, which in this example is 20050602.00V. If you wish to see that the V is in the file name suffix, look in the archive folder, (e.g., C:\<installation directory>\Audit\Arch).

4	In Pervasive PSQL Control Center, select File  SQL Document.

5	When asked to select a database, click DemodataV.

6	In the new SQL document, run all of the following SQL statements. You may copy and paste them in SQL Editor. Use the name of your own archived file instead of 20050602.00V.

-- This script resets the virtual database to
-- the uncompressed archived file 20050602.00V.
ALTER TABLE AM$amlog IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Billing IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Class IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Course IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Department IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Enrolls IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Faculty IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Person IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Room IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Student IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';
ALTER TABLE Tuition IN DICTIONARY USING '\<installation directory>\Audit\Arch\20050602.00V';

Note The script alters the table location property for AM$amlog in the virtual database and also for all of its copies of the data tables found in the audited database. When you write your own version of this script, be sure you do not alter the table location property for the following virtual database tables: AM$Components, AM$OpList, AM$Products, AM$Tables.

7	After the script runs, you may want to select File  Save SQL Query As to keep it for reuse, perhaps under a name such as 20050602.00V.sql.

The delta query you ran under section Running a Query on the Current View File should now return the same result as when you ran it against the current view, since those audit records have been moved into the archived file to which the virtual database now points.

‰	To reset the virtual database for a current view query

These steps let you run direct queries on the current view file again.

1	In Pervasive PSQL Control Center, select File  SQL Document.

2	When asked to select a database, click DemodataV.

3	In the new SQL document, run all of the following SQL statements. You may copy and paste them in SQL Editor.

-- This script resets the virtual database to the current
-- view file. For AuditMaster 6.0 or 6.1, use amview
-- instead of amlog.

ALTER TABLE AM$amlog IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Billing IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Class IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Course IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Department IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Enrolls IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Faculty IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Person IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Room IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Student IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';
ALTER TABLE Tuition IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog';

Note The script alters the table location property for AM$amlog in the virtual database and also for all of its copies of the data tables found in the audited database. When you write your own version of this script, be sure you do not alter the table location property for the following virtual database tables: AM$Components, AM$OpList, AM$Products, AM$Tables.

4	After the script runs, you may want to select File  Save As to keep it for reuse, perhaps under a name such as currentview.sql.

The delta query you ran under section Running a Query on the Current View File will now return a result for the current view instead of for the archived file.

Summary of Direct Query Methods

This section summarizes the direct query method for audit records:

1	A virtual database can enable direct queries of audit records independently of AuditMaster Viewer.

2	A special script populates the database. Use the Query Data-Model Generator utility qdmg to automate the writing of this script.

3	Create a database on the same volume as the AuditMaster installation root (e.g., default C:\<installation directory>\Audit).

4	Run the qdmg script in the database.

5	You may now run queries in the virtual database to return audit records from the current view file.

6	To enable queries of audit records in an archived file, use an ALTER script to reset the virtual database to do so.

7	Use a second ALTER script to set the virtual database back to its original state to query the current view file again.

8	Create and save a reset script for the current view file and for each archived file against which you want to run direct queries. In the virtual database, run the script you need before running your direct queries.

9	Remember that archived files must be uncompressed for queries to succeed.

Using the Delta Alert Utility

The AuditMaster delta alerts feature provides a means of setting an email alert based on a change to a selected audit record column or columns as the monitored event. Delta alert configuration has the following steps:

1	In AuditMaster, configure one or more tables to be monitored for changes.

2	Use the qdmg utility to create a virtual database for the database of the tables you’re monitoring so that you can run delta queries against captured audit records to find the alert condition.

3	Create an AuditMaster alert and enter amda.exe as the RunProgram action and set its parameters.

4	Within the alert, build a query for the Modify After event for the table or tables where the alert condition will occur.

5	Select email recipients to be notified when the alert triggers.

The details of these steps are given under Delta Alert Example, which shows how to create a working sample of a delta alert on the Demodata database.