The AMCC client and its query builder are not the only means of access to audit records. You also can run direct SQL queries against these records. To do so, you must first use the Query Data-Model Generator (QDMG) utility provided with AuditMaster. The utility generates a script to create a virtual database of views linked to audit records in the AuditMaster system.
Both current view and archived audit records can be queried directly using the query data-model method. Direct queries can support applications to create reports or otherwise display audit records, as well as serve development and debugging purposes.
Use cases are provided to demonstrate how to apply the direct query method to the Demodata database included in the Zen installation.
Note In AuditMaster 12, internal log and settings files are secured and encrypted with a Zen long owner name, so the SQL query method described here is not supported. However, you can still query audit records created in all other versions.
Query Data-Model Generator Utility
The Query Data-Model Generator (QDMG) utility generates a script, consisting of a set of SQL statements, to run against an empty database. The script populates this virtual database with views that link to audit records stored in the AuditMaster log file. Once the views are created, you can then run queries against them to return results from audit records within AuditMaster.
Data directory on remote server where the amserver file resides. Optional if amserver resides on the same system as the client.
-d
Path name of database schema (.ddf files) to import
-m
Master password if database is secure
-p
Name of audit configuration. For example, Zen Demo. For names with spaces, enclose in quotation marks.
-o
Path and file name of output (.sql) file for generated SQL. If no path name is given, the file is written to the current directory.
-l
Log file name for QDMG messages. Default is amlog.
-h
Help
The log file contains records for the current view file in AMCC. You can also access audit records in archive files, but queries on the current view file must be enabled first. Follow these short procedures in the order given:
This task 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 Zen.
1 Before setting up a virtual database, import the schema for your audited database into AM. 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 AM installation.
If you need instructions to import the schema from your own database, see Managing Schemas.
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 all of the following:
•Open Zen Control Center and expand the branch for the database being audited, Demodata.
•Open the Tables branch for Demodata, right-click on a table, and select Properties.
•Note the Dictionary Path where the DDFs are located.
3 For the virtual database to link to audit records, you must indicate which audit configuration in AM will be used. To check its name, do both of the following:
•Open AMCC and log in as an AuditMaster administrator.
•In the Audit Configurations list in the Tables tab, find the name of the audit configuration that was entered when you imported its schema. In this example, the name is “Zen Demo,” which was already imported in the AuditMaster installation.
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:
•Audited database DDF path name (default C:\ProgramData\Actian\Zen\Demodata)
•No password, since Demodata database security is disabled.
•Audit configuration product name “Zen Demo”
•Path and file name for output of the generated script.
Query Data-Model was generated into C:\ProgramData\Actian\Zen\DemodataV\script.sql
Next, create the database in which to run the script.
7 Open Zen 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:\ProgramData\Actian\Zen\DemodataV.
Note The virtual database must reside on the same volume as the AuditMaster installation directory. Also, if the original database uses long (V2) metadata, check the box shown for the new virtual database.
10 Click Finish to complete database creation.
11 In Zen Control Center, select File > Open.
12 In the Open dialog box, navigate to the file script.sql saved earlier in C:\ProgramData\Actian\Zen\DemodataV.
13 In the Select Database dialog box, 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. In ZenCC you can expand the Views node under the DemodataV database to see what was created.
The virtual database DemodataV now supports queries on audit record columns, as well as on data columns from Demodata.
The columns in 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:
•Audit columns in the result have the prefix AM$ and contain audit data.
•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.
•Many audit columns match query attributes seen in AMCC and in the Query Builder window tabs.
•All column names are queryable, but some contain internally used codes that are not particularly relevant to human auditing.
The following table compares the columns in an audit record with those displayed in the Audit Records tab in AMCC.
Table 2 Audit Record Columns in a Virtual Database versus AMCC
Virtual Database
AMCC
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 Zen 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 Zen status codes also appear here when first selected in the Errors to Audit area 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 AM) or Zen
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 in 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 in Did What tab.
AM$net_id
Machine Name
Same as Machine Name attribute in From Where tab. One of the following:
•MAC ID if event in audited file originated on same system as AM server
•IP address for local client applications using UNC address instead of simple path name
•IP address if event originated from remote client
Process that was source of audit event. Same as Process attribute in 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 the operating system where AM server is running
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
•Run qdmg to generate a script to populate a virtual database with views linked to audit records
•Create an empty database
•Execute the script in the database
If you have completed these tasks, you are ready to run direct queries for audit records as shown in the continuing example in this section.
►To run a simple query for DemodataV audit records
1 In AM, set the built-in Zen Demo audit configuration to monitor the Student table in Demodata, then in Zen Control Center under Services, restart the Zen database engine to activate the configuration.
2 In Zen 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 Zen 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:
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:\ProgramData\Actian\Zen\Audit\data\amlog. As explained in this section, you can reset the path to an archive 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 archive file that day, starting with two zeroes. File names end in a capital V. The default folder for archive files is C:\ProgramData\Actian\Zen\Audit\Arch.
When an archive file is compressed, it moves to a different folder, the default for which is C:\ProgramData\Actian\Zen\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. Queries can run only on uncompressed records.
The method described here uses two SQL scripts:
•The first script sets the virtual database to point to an archive file instead of the current view file.
•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 archive file query
1 To use these steps, you need an archive file. Open AMCC, right-click the current view file, and select Archive.
AuditMaster moves current audit records to an archive file.
2 Expand the Archived Files node, then right-click the node and select Refresh.
The newly created archive file appears in the list.
3 Note the name of the file, which in this example is 20160220.00V. If you wish to see that the V is in the file name suffix, look in the archive folder, (e.g., C:\ProgramData\Actian\Zen\Audit\Arch).
4 In Zen 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 archive file instead of 20191015.00V.
-- This script resets the virtual database to -- the uncompressed archive file 20191015.00V. ALTER TABLE AM$amlog IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Billing IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Class IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Course IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Department IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Enrolls IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Faculty IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Person IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Room IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Student IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.00V'; ALTER TABLE Tuition IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\Arch\20191015.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 20191015.00V.sql.
The delta query you ran under 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 archive 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 Zen 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.
ALTER TABLE AM$amlog IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\DATA\amlog'; ALTER TABLE Billing IN DICTIONARY USING '\<installation directory>\Audit\DATA\amlog'; ALTER TABLE Class IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\DATA\amlog'; ALTER TABLE Course IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\DATA\amlog'; ALTER TABLE Department IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\DATA\amlog'; ALTER TABLE Enrolls IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\DATA\amlog'; ALTER TABLE Faculty IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\DATA\amlog'; ALTER TABLE Person IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\DATA\amlog'; ALTER TABLE Room IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\DATA\amlog'; ALTER TABLE Student IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\Audit\DATA\amlog'; ALTER TABLE Tuition IN DICTIONARY USING '\C:\ProgramData\Actian\Zen\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.
This section summarizes the direct query method for audit records:
1 A virtual database can enable direct queries of audit records independently of AMCC.
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:\ProgramData\Actian\Zen\Audit).
4 Run the qdmg script in the database.
5 You may now run queries in the virtual database using the views that were created to return audit records from the current view file.
6 To enable queries of audit records in an archive 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 archive 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 archive files must be uncompressed for queries to succeed.
AuditMaster and Client-Side Caching
In a Zen database, when the client-side cache engine is turned on, the cache engine reads an entire database page after 8 consecutive reads in anticipation of more reads. The records in the database page read by the cache engine are not audited by the monitor on the server. If auditing requires that every read be captured, verify that the cache setting is off. However, lack of engine caching can reduce database performance. The behavior occurs only when the threshold of 8 consecutive reads is reached. If 7 reads and then an update occur, no caching occurs and all 7 reads are captured. To see the setting Use Cache Engine in Zen Control Center, expand Local Client, right-click MicroKernel Router, select Properties, and click Performance tuning. By default, the setting is off.
If you are not auditing Read operations, you do not need to restrict the use of client-side caching.