Automating database rotation

Although you can do database rotation manually using Audit Manager, you might want to automate the process to perform it automatically on a regular schedule. You might also want to automate and schedule the detachment of old databases from the audit store. The API described in the reference enables you to write scripts to perform database rotation and attach or detach databases.

The software development kit (SDK) for auditing includes four sample scripts that you can modify to suit your purposes: two VBScript samples and two Power Shell samples. One pair of sample scripts (db_rotation) use default database settings. The second pair (db_rotation_sql_script) let you customize the database scripts to set up the database and the server.

The sample scripts perform the following steps:

  1. Create a new audit store database and attach it to an audit store.
  2. Grant permission to the management database and collectors to access the newly created audit store database.
  3. Make the newly created audit store database the active database.
  4. Detach any audit store databases older than two years.
  5. Publish the settings to Active Directory so that audited computers and collectors can look up the information.

Note that the sample scripts require the user to respond to informational messages at various points during execution. To make these scripts run without user interaction, remove or comment out all the wscript.echo commands in the script, or redirect the echo commands to STDOUT so that the scheduled task will not hang waiting for user input.

The following command adds the script db_rotation.vbs as a monthly scheduled task named rolldb to be run as user domain_name\administrator. By using cscript.exe to launch the script, it redirects output to STDOUT.

PS C:\Program Files\Centrify\Audit\SDK\Samples> schtasks.exe /Create /TN "rolldb" /TR "cscript.exe 
'C:\Program Files\Centrify\Audit\SDK\Samples\db_rotation.vbs' DefaultInstallation DefaultAuditStore 
sqlserver.domain_name.com subtest3" /RU domain_name\administrator /SC Monthly /MO 1

The components of this command are as follows:

Schtasks.exe /Create /TN <Task_name> /TR <Task_Command> /RU <Run_As_User> /SC <Reoccurance_rate> 
/MO <Reoccurance_increment>

where

  • Task_Name: rolldb
  • Task_Command: cscript.exe 'C:\Program Files\Centrify\Audit\SDK\Samples\db_rotation.vbs' DefaultInstallation DefaultAuditStore sqlserver.domain_name.com subtest3
  • Run_as_user: domain_name\Administrator
  • Reoccurance_rate: Monthly
  • Reoccurance_increment:1

The task command consists of the following elements:

<parser> '<install_path>\<VBS_script>' <Installation> <auditstore> <DB_Server> <DB_prefix>

where

  • parser: cscript.exe
  • install_path: C:\Program Files\Centrify\Audit\SDK\Samples
  • VBS_script: db_rotation.vbs
  • Installation: DefaultInstallation
  • auditstore: DefaultAuditStore
  • DB_Server: sqlserver.domain_name.com
  • DB_prefix: subtest3

The prefix is attached to a date stamp in the name of the newly created audit store database.