Creating a Virtual Database

This section gives the steps for using the qdmg utility to create a virtual database for direct queries of audit data. The example uses the Demodata database installed with Pervasive PSQL.

1	Before setting up a virtual database, import the schema for your audited database into AuditMaster. If this already has been done, go to the next step.

In this example, importing has already been done for Demodata as part of the AuditMaster installation.

If you need instructions to import the schema from your own database, see the section “Managing Schemas” in Chapter 4: “Working with Audit Configurations” of Pervasive AuditMaster User’s Guide.

2	Creation of the virtual database will require access to the DDFs of the database for which you want to query audit records. To find this path, do the following:

a.	Open Pervasive PSQL Control Center and expand the branch for the database being audited, Demodata.

b.	Open the Tables branch for Demodata, right-click on a table, and select Properties.

c.	Note the Dictionary Path where the DDFs are located. In this example, it’s C:\<installation directory>\Demodata.

3	For the virtual database to link to audit records, you must indicate which audit configuration in AuditMaster will be used. To check its name, do the following:

a.	Open AuditMaster Viewer and log in as an AuditMaster administrator.

b.

Select Admin  Audit Configuration and in the window that appears, find the product name in the list of configurations on the left. The product name is the name you entered when you imported the schema into AuditMaster. In this example, the product name is “Pervasive PSQL Demo,” which was already imported when AuditMaster was installed.

4	In Windows Explorer, create a new folder at the same level as the existing Demodata folder.

In this example, we name the folder DemodataV, adding the V for “virtual,” but you can choose your own name. The script to populate the virtual database will be saved here, as well as the database itself.

5	Now use qdmg to generate the script based on the following:

w	Audited database DDF path name (default installation location C:\<installation directory>\Demodata)

w	No password, since Demodata database security is disabled.

w	Audit configuration product name “Pervasive PSQL Demo”

w	Path and file name for output of the generated script.

The command looks like this:

qdmg -d C:\<installation directory>\Demodata -p "Pervasive PSQL Demo" ‑o C:\<installation directory>\DemodataV\script

6	Open a command prompt window and run the command.

The prompt returns the following message:

Query Data-Model Generator Utility for Pervasive AuditMaster
Copyright (C) Pervasive Software Inc. 2004

Query Data-Model was generated into C:\<installation directory>\DemodataV\script.sql

Next, create the database in which to run the script.

7	Open Pervasive PSQL Control Center.

8	Under the name of your server, right-click the Databases (Engine) node and select New Database.

The Create Database Wizard appears.

9	This example uses the database name DemodataV and the directory you created, C:\<installation directory>\DemodataV, as shown here.

Note You must place the virtual database on the same volume as the AuditMaster installation directory.

10	Click Finish to complete database creation.

11	In Pervasive PSQL Control Center, select File  Open.

12	In the Open dialog box, navigate to the file script.sql saved earlier in C:\<installation directory>\DemodataV.

The Select Database dialog box appears.

13	Expand the Databases tree, select DemodataV, and click OK.

SQL Editor displays the SQL statements in script.sql.

14	Select SQL  Execute All SQL Statements.

The statements in script.sql populate DemodataV with views to audit records.

The virtual database DemodataV now supports queries on audit record columns, as well as on data columns from Demodata.

You may now do any of the following:

n	Find out what you can query. See The Structure of an Audit Record.

n	Query current audit records. See Running a Query on the Current View File.

n	Query archived audit records. See Running a Query on an Archived File.

n	Create a delta alert. See Using the Delta Alert Utility.

The Structure of an Audit Record

The columns of an audit record are described in this section. Its structure is representative of the result returned by a query such as SELECT * FROM vstudent.

The following facts should be noted in the example:

n	Audit columns in the result have the prefix AM$ and contain audit data.

n	After the AM$ audit data columns, the rest of the row consists of data fields from the audited table and contain values captured from that table at the time of the audit event.

n	Many audit columns match query attributes seen in AuditMaster Viewer and in the Query Builder window tabs.

n	All column names are queryable, but some contain internally used codes that are not particularly relevant to human auditing.

Once you have reviewed the audit record structure, see Running a Query on the Current View File for steps to run a query on the DemodataV example.

The following table compares the columns of an audit record with those displayed in the AuditMaster Viewer grid.

 

Table B-1

Audit Record Columns in a Virtual Database versus AuditMaster Viewer

Virtual Database	AuditMaster Viewer	Description
AM$rec_id	Record No.	Incremental number for audit record
AM$opdate	Date	Capture date for audit record (e.g., 2005-06-07)
AM$optime	Time	Capture time for audit record (e.g., 17:04:30)
AM$dbms_id	—	Internal use
AM$dbmsverkey	—	Version of Pervasive PSQL system
AM$opcontextkey	Operation Context	Normal operation (e.g., BTRIEVE) or error
AM$opcode	—	Internal use
AM$optext	Operation	Database event. Events can include any item in Operations list of the Did What tab in Query Builder. SQL logins display in this column. Selected Pervasive PSQL status codes also appear here when first selected in the Errors to Audit section of the Server Settings window.
AM$dep_rec_id	Dependent Record	Record number for an earlier related record: • Modify-before record for modify-after record • Begin-transaction record for end/abort transaction record
AM$prod_id	—	Internal use
AM$prodverkey	Product Version	As listed in audit configuration for monitored files
AM$product_name	Product	As listed in audit configuration for monitored files
AM$comp_id	Database Engine	Either AM Message API (internal use within AuditMaster) or Pervasive PSQL
AM$compverkey	Component Version	Component version, as listed in audit configuration for monitored files
AM$comp_name	Component	As listed in audit configuration for monitored files
AM$tab_id	—	Internal use
AM$tabverkey	—	Same as AM$compverkey
AM$table_name	Table Name	File in which event occurred. Same as Tables attribute under Did What tab. The file must be selected for monitoring in an audit configuration. All configured files appear in the Tables list of the Did What tab in Query Builder.
AM$tabdef_id	—	Internal use
AM$group_name	Group Name	Group for monitored files in audit configuration. Same as Groups attribute under Did What tab.
AM$net_id	Network Address	Same as Network Address attribute under From Where tab. One of the following: • MAC ID if event in audited file originated on same system as AuditMaster server • IP address for local client applications using UNC address instead of simple path name • IP address if event originated from remote client
AM$net_user_id	User Name	Login ID under which event occurred. Same as user name under Who tab. See “Displaying Audit Records under Pervasive PSQL Security” in Pervasive AuditMaster User’s Guide.
AM$process_name	Process Name	Process that was source of audit event. Same as Process attribute under How tab.
AM$sess_num	—	Internal use
AM$lic_num	—	Internal use
AM$mapstate	—	Internal use
AM$database_name	Database Name	Database in which audit event occurred. Depending on the implementation of the database concept at the level of the event, this value may be “n/a,” not available.
AM$osverkey	OS Version	Name and version of operating system of machine where AuditMaster server is running (e.g., W2K 5.1.2600 SP3.0)
AM$retcode	—	Internal use
AM$reserved	—	Internal use
AM$databufsize	—	Internal use
AM$len	—	Internal use
<Data Column 1>	—	First data column from table where audit event occurred
<Data Column 2>	—	Second data column from table where audit event occurred
<Data Column n...>	—	Additional data columns...