Running a Query on the Current View File

Before querying for audit records described under The Structure of an Audit Record, be sure to have done the following:

n	Run qdmg to generate a script to populate a virtual database with views linked to audit records

n	Create an empty database

n	Execute the script in the database

If you’ve completed these tasks, you’re ready to run direct queries for audit records as shown in the continuing example in this section. Steps for a simple query are given, as well as those for a more complex delta query to compare Before and After field values.

‰	To run a simple query for DemodataV audit records

1	In AuditMaster, set the built-in Pervasive PSQL Demo audit configuration to monitor the Student table in Demodata, then restart the event handler to activate the configuration.

2	Open Pervasive PSQL Control Center, open the Demodata database, then open the Student table.

In SQL Editor, the default query SELECT * FROM "Student" returns all rows.

3	The first row should contain the student ID 190907350. Click the GPA field for this student, change 4.000 to 3.000, and press Enter.

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

5	When asked to select a database, click DemodataV.

6	In the new SQL document, run the following query. You may copy this statement and paste it in SQL Editor.

SELECT AM$rec_id, AM$opdate, AM$optext, ID, Cumulative_GPA FROM VStudent

The query should return a result like the following:

AM$rec_id AM$opdate AM$optext ID Cumulative_GPA

========== ========== ============= ========= ==============

637 6/2/2005 Modify Before 190907350 4.000

638 6/2/2005 Modify After 190907350 3.000

‰	To run a delta query to compare Before and After fields

For a more complex example, you may run a delta query to compare Before and After values. The general syntax for a delta query is as follows:

SELECT field1, field2,...
FROM view after, view before
WHERE after.AM$rec_id = before.AM$dep_rec_id AND after.AM$field3 <> before.AM$field3

This type of query will display audit records for every instance of an update to field 3. For a large set of audit records, however, such a query may return too large a result. In this example, we will limit the delta query to a specific student ID.

1	Return to SQL Editor for the DemodataV database used in the last example.

2	Run the following delta query against the Before and After fields in the audit records for the ID of the student whose GPA you changed. You may copy this statement and paste it into SQL Editor.

SELECT after.AM$rec_id, after.AM$opdate, after.AM$optime, after.AM$net_user_id, before.Cumulative_GPA AS "GPA Before", after.Cumulative_GPA AS "GPA After"
FROM "VStudent" after, "VStudent" before
WHERE after.ID = '190907350' AND after.AM$rec_id = before.AM$dep_rec_id
AND after.Cumulative_GPA <> before.Cumulative_GPA;

The query should return a result like the following:

AM$rec_id AM$opdate AM$optime AM$net_user_id GPA Before GPA After
========= ========== ========== ============== ========== ==========
637 6/2/2005 6:23:49 PM db_user 4.000 3.000

Note	Once you’re able to run a delta query against a virtual database, you can configure a delta alert. The AuditMaster delta alert feature can use changes in selected columns of an audit record as the alert condition.