How To: Sending all TPP events to a separate SQL channel

Versions:

Venafi Trust Protection Platform 14.2 to 14.4

Subject:

Occasionally organizations need to keep a copy of all events to comply with regulations.

This can become impractical if the only logging channel used is the default SQL channel, as a high number of events could cause performance degradations.

The Default SQL channel is commonly used for day-to-day administration and troubleshooting and should be trimmed down to display only the last 90 days.

An additional SQL channel should be created so logs are kept for a longer period to comply with regulations, it is recommended this database is located on another server.

Creating and preparing the new database

Setting Up a Database on a Microsoft SQL Server 2008 or 2012

Connect to the database engine
Create a new database
Select the database in the drop-down list
Run the MSSQL CreateLOGDB database script
Click Execute
Add the same user account, used to connect the Venafi Trust Protection Platform database, to the new database
Grant db_datareader and db_datawriter permissions to the database user account

The new MSSQL database is now configured and ready to use.

Setting up a Database on Oracle 11g R2 database server

NOTE: If the archive database resides on the same database server as the Venafi Trust Protection Platform database, make sure to change the Tablespace name

Tablespace.sql is provided as a sample for the creation of the Tablespace and user. If preferred, you can create the Tablespace and database user account yourself.

The default username referenced in the Tablespace.sql script is DIRECTOR.

The following table identifies the default Tablespace settings in the Tablespace.sql script:

Script Parameters Tablespace Name Password Data file Initial Tablespace Tablespace Increase Increments Maximum Database Size

Default Settings VED password \ved.ora 1024 MB 1024 MB n/a

Connect to the database engine.
Select the new database.
Run the Tablespace.sql script.
Grant Connect and Quota Unlimited permissions to the database user account.

The database user account is created when you run the Tablespace.sql script. It is the account Trust Protection Platform uses to connect to the Oracle database. The default username for the Trust Protection Platform database is “DIRECTOR”. If you want to define a different database username, replace all DIRECTOR references
After the Tablespace.sql script runs, open CreateLOGDB.sql. Modify the database username to match the username if required
Run the CreateLOGDB.sql script

The new Oracle database is now configured and ready to use.

Creating the new SQL Channel

Go to the logging tree
Right Click on Channels
Select Add > MSSQL if you are using MSSQL or Oracle if you are using Oracle
Enter a name for the new SQL channel. In this example, it will be called SQL Archive Channel
Enter the database connection settings
Do not fill the Log View Database Access and Expiration sections
Click Save

The new SQL Channel is now created

Creating the notification rule

Now the database and channel have been configured, a new notification rule sending all events to the SQL Archive Channel must be created.

Go to the Logging tree
Right click on Notification Rules
Add > Notification
Enter the name of the new notification rule. In this example, it will be called Send All Events to Archive Channel
The rule will be:

If Event ID between 0 and 4294967295 (equivalent to the hexidecimal 0xFFFFFFFF)
Set the Target Channel to SQL Archive Channel
Click Save
For the new notification to apply immediately, you should restart the Venafi Log Server service

NOTE: During upgrade, Venafi may make changes to the log table schema. Make sure those changes are applied to the SQL Archive Channel database as well. Contact Venafi Support for more information.

Info: Set Up Log Rotation
Sending all VED Logs to a Syslog Server
Info: Set Up Log Rotation

ArchiveChannelDBScript.zip

md5: 3d4fc0e1056fc53c12d5860136e71294
sha1: cf2ec45734fdfed5f4912887444813b4fb49e180