Advanced Operations
Advanced operations are for those who need utilities and methods for accessing audit data from outside of AMCC.
Querying Audit Data Directly through SQL
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.
This topic covers the following items:
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.
Syntax
qdmg -d DDF_path [-m password] -p name ‑o file [‑l logfile] [-a folder]
Options
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:
Creating a Virtual Database
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.
The command looks like this:
qdmg -d C:\ProgramData\Actian\Zen\Demodata -p "Zen Demo" ‑o C:\ProgramData\Actian\Zen\DemodataV\script
6. Open a command prompt window and run the command.
The prompt returns the following message:
Query Data-Model Generator Utility for Actian AuditMaster
Copyright (C) Actian Corporation 2019
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.
You may now do any of the following:
The Structure of an Audit Record
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 Query Builder window tabs.
• 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 in an audit record with those displayed in the Audit Records tab in AMCC.
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:
• 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:
AM$rec_id AM$opdate AM$optext ID Cumulative_GPA
========== ========== ============= ========= ==============
637 10/2/2019 Modify Before 190907350 4.000
638 10/2/2019 Modify After 190907350 3.000
Running a Query on an Archived File
This topic 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:\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 '..\Audit\Arch\20191015.00V';
ALTER TABLE Billing IN DICTIONARY USING '..\Audit\Arch\20191015.00V';
ALTER TABLE Class IN DICTIONARY USING '..\Audit\Arch\20191015.00V';
ALTER TABLE Course IN DICTIONARY USING '..\Audit\Arch\20191015.00V';
ALTER TABLE Dept IN DICTIONARY USING '..\Audit\Arch\20191015.00V';
ALTER TABLE Enrolls IN DICTIONARY USING '..\Audit\Arch\20191015.00V';
ALTER TABLE Faculty IN DICTIONARY USING '..\Audit\Arch\20191015.00V';
ALTER TABLE Person IN DICTIONARY USING '..\Audit\Arch\20191015.00V';
ALTER TABLE Room IN DICTIONARY USING '..\Audit\Arch\20191015.00V';
ALTER TABLE Student IN DICTIONARY USING '..\Audit\Arch\20191015.00V';
ALTER TABLE Tuition IN DICTIONARY USING '..\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 201015.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 '..\Audit\DATA\amlog';
ALTER TABLE Billing IN DICTIONARY USING '..\Audit\DATA\amlog';
ALTER TABLE Class IN DICTIONARY USING '..\Audit\DATA\amlog';
ALTER TABLE Course IN DICTIONARY USING '..\Audit\DATA\amlog';
ALTER TABLE Dept IN DICTIONARY USING '..\Audit\DATA\amlog';
ALTER TABLE Enrolls IN DICTIONARY USING '..\Audit\DATA\amlog';
ALTER TABLE Faculty IN DICTIONARY USING '..\Audit\DATA\amlog';
ALTER TABLE Person IN DICTIONARY USING '..\Audit\DATA\amlog';
ALTER TABLE Room IN DICTIONARY USING '..\Audit\DATA\amlog';
ALTER TABLE Student IN DICTIONARY USING '..\Audit\DATA\amlog';
ALTER TABLE Tuition IN DICTIONARY USING '..\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.
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 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.