Summary

Starting from OneSpan Authentication Server (OAS) 3.15, the default (or embedded) database used is MariaDB.

KB0013986 explains how to clean up the Audit database for older versions, using PostgreSQL.
KB001442 explains how to clean up the Audit database for versions 3.15 and 3.16, using MariaDB.
This KB explains how to do it for OAS versions 3.17 and newer, using MariaDB.

 

Problem Details.

Using the maintenance wizard, you can export audit data (and delete it in the database) for archiving, and as of 3.16 you can also delete audit data from the webadmin.
But you can only use a time-span as selection criterium.

When using the LDAP Sync Tool, the audit database may grow fast, it is creating a lot of less relevant audit entries (when reading and searching in the Authentication Server)
In this case, it may be useful to clean-up the audit Database selectively (delete only the read records and not the write or update records).

Problem Solution.

Before changing anything in the database it is recommended to back-up the database first.
See the Authentication Server administrator guide on how to back-up the database.
(If you use the database with SSL encyption you need to add –SSL to the commands in the Documentation).

You can also backup only the audit data with the following command:
mysqldump -v -u digipass -p "Identikey Server" --ssl vdsauditmsg vdsauditmsgfield > test2.sql

You can also backup the database without the audit data with the following command:
mysqldump -v -u digipass -p "Identikey Server" --ssl --ignore-table="Identikey Server".vdsauditmsg --ignore-table="Identikey Server".vdsauditmsgfield  > test3.sql

The audit data is stored in two tables in the Database.
There is CONSTRAINT between the two tables that prevents deleting records in the main table when there are still records in the second table that are related.

 

1. Steps for removing older data

You can now use following SQL query to remove older data:

Delete from vdsauditmsg where vdstimestamp < ‘date';

Where date is YYYY-MM-DD. For example, the following query will delete all entries before September 1st, 2017.

Delete from vdsauditmsg where vdstimestamp < '2017-09-01';

If you first want to see which records will be deleted, you can run:

Select count(*) from vdsauditmsg where vdstimestamp < '2017-09-01';

(you can use the same queries from the command line after opening the mysql prompt as above)

3. Steps for removing less important LDAP sync audit data

You can use following SQL query to delete all audit records recording the query of a user attribute:

Delete from vdsauditmsg where vdsdesc = ‘A query for [User Attributes] records was successful.’;

If you first want to have an idea on the number of records you will delete, you can run the following SQL query:

Select count(*) from vdsauditmsg where vdsdesc = ‘A query for [User Attributes] records was successful.’;

 

4. Steps for scheduling

In the latest versions of OAS (as of OAS3.16) you can delete audit data from the webadmin and schedule the delete to old audit data on regular time intervals.

This function can only be used with time as criteria. If you wish to schedule more specific delete commands, you can create a bat file to do so:

To run the query from a bat file we use the mysql command with some options.

Eg:

C:\Program Files\VASCO\MariaDB\bin\mysql -u digipass -p digipassword "identikey server" --ssl=TRUE -e "Delete from vdsauditmsg where vdsdesc = ‘A query for [User Attributes] records was successful.’;"

5. Reduce disk space used by the audit tables after clean-up

Open a dos windows as administator.

change directory (cd) to the bin directory of mariadb: Program Files\VASCO\MariaDB\bin

Dump the audit data to a file: mysqldump -v -u digipass -p "Identikey Server" --ssl vdsauditmsg vdsauditmsgfield > test2.sql

PS: if you have little space left on the disk, you may want to dump on a file in another partition or a network drive. change test2.sql by "x:\test2.sql" where x:\ is an other partition or disk where you have space (and access rights) to dump the audit data.

Next we will drop the tables to free the space:

login to MySql:

mysql "Identikey Server" --ssl=TRUE -u digipass -p
(enter password)

delete the audit tables:

DROP TABLE IF EXISTS `vdsauditmsgfield`;
DROP TABLE IF EXISTS `vdsauditmsg`;
commit;
quit

restore the previously taken backup: (This will also recreate the tables. If you replaced test2.sql by "x:\test2.sql" in the back-up, you also need to restore from "x:\test2.sql" )
mysql "Identikey Server" --ssl=TRUE -u digipass -p < test2.sql
(enter password)

_________________________________________________________________________________________________________________

Security Status: External

Document type: How To

Applies to: Onespan Authentication Server 3.17 and higher

Support Case Reference: CS0089989