Hi there AppWorks fans,

Welcome to a new installment of AppWorks tips.

Last week was BPM-time, this week it will be Audit-time. The end-goal? Cleaning up tables in the database! Audit information is also one of those big-data-sets to manage correctly as it can increase in size quickly. This post will show how to start auditing, how to view the audit information, and how to clean it when expired. It’s your decision how long the audit information needs to be accessible as this is a compliance, security, and awareness question out of scope for this post! 🤠

Let get right into it…

First two links where we “sniffed” already on the audit trail:

• Release 21.2
• Release 21.3

Now, spin up your machine (if not already up and running), and make sure to enable auto-start and run the ‘Auditing’ service container in the ‘System’ space.

Make sure to also look in the ‘Auditing’ tab for this service container. It contains information about the database location. Here you have the possibility to save records of the audit information into a separate database configuration. Out of scope for this post, but also interesting to know…From your own organization, you can also create your own service container of type ‘Auditing’!

Once ready, we can enable a variety of types to audit on in the ‘Audit Configuration’ artifact (also in the ‘System’ space). Just enable some interesting types and save it (this post will play with entity data!).

I run AWP 22.1 with the latest audit features available!

The next step is to prototype a simple entity acclaim (with some basic properties a_name and a_summary), publish it, and CRUD some instances in runtime. You can all do this in your own organization space. In the end, you can use the ‘Audit Viewer’ artifact of your organization to recap the actions with a view like this:

Right-click the row to check the action input for further information like this property update:

Nice…Audit is in place!

Database view

Time to #RTFM (Chapter 10 of the AWP administration guide) where we learn about these tables:

• cordys_artifacts_audit: Which exposes the settings of the ‘Audit Configuration’ artifact.
• cordys_artifacts_revisions: Which exposes the audit trail itself.

This last table is the most interesting with the same view (from HeidiSQL) like before:

The query behind it (you need to tweak it for your own organization):

1 2 3 4 5

SELECT performed_at, state, operation, revision_remark FROM cordys_artifacts_revisions WHERE performed_by = 'cn=awdev@awp,cn=organizational users,o=appworks_tips,cn=cordys,cn=defaultInst,o=mydomain.com' AND OPERATION NOT IN ('Invoke', 'LogOn', 'Read') AND revision_remark NOT LIKE '%acclaim%' ORDER BY performed_at DESC;

Great…We learned about the database audit table! Next step…

Purging the audit

Continue #RTFM…This teaches me about the database archiving scripts which first need to be installed. This is a quick and easy thing to do on my VM:

1
2
3
4

sudo -u postgres psql
\c appworks_db
\i /opt/opentext/AppWorksPlatform/defaultInst/components/audit/dbschema/archivescripts/audit_archive_postgres.sql
\q

Other databases (‘mssql’, ‘mysql’, and ‘oracle’) are supported with these scripts!

It’s a good habit to inform yourself with a view on these kinds of scripts: view /opt/opentext/AppWorksPlatform/defaultInst/components/audit/dbschema/archivescripts/audit_archive_postgres.sql. This view teaches me about a set of created database functions:

1
2
3
4
5
6
7
8
9
10
11

SP_ARCHIVE_AUDIT(...);
SP_PROCESS_ALL(...);
SP_PROCESS_ORG(...);
SP_PROCESS_TYPE(...);
SP_PROCESS_ORG_TYPE(...);
SP_ARTIFACTS_REVISIONS(...);
SP_ARTIFACTS_AUDIT(...);
SP_CREATE_ARCHIVE_TABLES(...);
SF_TABLE_EXISTS(...);
SP_CREATE_TEMP_DEBUG(...);
SP_DEBUG(...);

This list also exposes a (un-documented) ‘Debug’ feature which we can enable with a function call like this: SELECT sp_create_temp_debug();. This call creates a new table temp_debug in the database. You can verify it yourself with function call: SELECT sf_table_exists('temp_debug');. A result of true concludes debug enablement for our audit purge activities!

Time for a first purge action with a function call like this: SELECT sp_archive_audit(current_date + 1 , 3, NULL, NULL);

Notes on the passed parameters for the function call:

• Param 1 - IN_ARCHIVE_DATE: Puts everything older than the given date into archive (For my example this includes today because of the +1!). These are also valid input values for PostgreSQL: '2022-02-15' or current_date - interval '30' day
• Param 2 - IN_BATCH_SIZE: Number of records in one database transaction. The documentation uses a recommended value between 10.000 - 100.000
• Param 3 - IN_ORGANIZATION: The LDAP-DN of the organization; For me this would be o=appworks_tips,cn=cordys,cn=defaultInst,o=mydomain.com
• Param 4 - IN_ARTIFACT_TYPE: A valid artifact type like Entity; Query the database for other valid values: SELECT distinct artifact_type FROM cordys_artifacts_audit;

What does it all trigger?

1. Our parameterized function call triggers the database function SP_PROCESS_ALL
2. This function creates the corresponding archive tables ARCHIVE_ARTIFACTS_AUDIT and ARCHIVE_ARTIFACTS_REVISIONS
3. It inserts rows in those tables based on select statements of the original ‘Cordys’ tables CORDYS_ARTIFACTS_AUDIT and CORDYS_ARTIFACTS_REVISIONS
4. Deletes the entries from the original ‘Cordys’ tables
5. Fills the debug table during this process!

So, to monitor our action: SELECT * FROM temp_debug ORDER BY ts desc;

When done, you should see a data shift from the original audit table to the archive audit table! Query along to have a look…You know the tables now!

That’s a ‘Purging’ DONE for this week. Again, interesting insights on database cleaning. A nicely documented feature from the administration guide…great! With this post we even exposed a fascinating “debug” feature (which helps in the near future!). The provided samples will for sure help someone else and exposes the importance of correct audit management for the long run…It’s all about compliance rules…Have fun! Cheers, and CU next week.

Don’t forget to subscribe to get updates on the activities happening on this site. Have you noticed the quiz where you find out if you are also “The AppWorks guy”?