Overview

You have the ability to log the following user activity in Callback:

• Login attempts
• Logout attempts (via the links in Configuration, Launchpad, Dashboards, or System Management)
• Logout due to timeout
• New user added
• Existing user deleted
• Changes made to an existing user, including:
  • First name
  • Last name
  • Username
  • Password
  • Access level
  • System Management access level
  • "User must change password at next login" setting
  • "Account locked" setting
  • "Password never expires" setting

Configuring User Audit Logging

Two scripts are required to set up the user audit logging:

• VHT_Audit_Create_Tables.sql
• VHT_Audit_Create_Connection.sql

These scripts are automatically installed with Virtual Hold. They are written to the folder named SQL Server Database Installation Scripts, which is in the same location as the configuration file. These two scripts must be edited and then run manually to create the necessary tables and insert the appropriate records for logging.

Determine Database for Logging

You have the option to write the audit records to one of the existing Virtual Hold databases, or to a separate database. Make sure that the database you want to use has already been created. If the database is on a different server from the VHT configuration database (default is VHT_Config), this will require additional configuration steps, as described in the following.

Configure and Run Scripts

• From the server where you want to store the audit tables, open the VHT_Audit_Create_Tables.sql script. Within this file, find the text "ReplaceAuditDatabaseName" and replace that text with the name of the database that you want the audit logging information stored in.
• Within that same file, find all instances of the text "ReplaceVHTConfigDatabaseName" and replace that text with the name of the VHT configuration database (default is VHT_Config).

• If you are creating the audit tables on a different server other than the one containing the VHT configuration database:

  • Create a linked server so that the two servers can share data.

    • For information on creating a linked server in SQL Server 2012, please visit https://msdn.microsoft.com/en-us/library/ff772782.aspx.

  • In the VHT_Audit_Create_Tables.sql script, find all instances of the text "ReplaceVHTConfigLinkedServerName" and replace that text with the linked server name.

  • Find the section denoted by "-- If the audit tables exist on a different server than the VHT Config database, uncomment and run this section:" and uncomment that section.

  • Continue with Step 5, below.

• If you are creating the audit tables on the same server as the VHT configuration database:

  • Find the section denoted by "-- If the audit tables exist on the same server as the VHT Config database, uncomment and run this section:" and uncomment that section.

  • Continue with Step 5, below.

• Run the VHT_Audit_Create_Tables.sql script.
• Confirm that the AccessLevelDesc, AuditUser, AuditUserChangeTypeDesc, and SystemManagementAccessLevelDesc tables have been created in your specified database.
• On the SQL server where you have installed the VHT configuration database, open the VHT_Audit_Create_Connection.sql script.
• Within this file, find the text "ReplaceVHTConfigDatabaseName" and replace that text with the name of the VHT configuration database.
• Within this same file, find the following section:

SET @ServerName = N'<PleasePutAuditServerNameHere>';SET @UserId = N'<PleasePutAuditDatabaseUserIdHere>';SET @Password = N'<PleasePutAuditDatabaseUsersPasswordHere>';SET @DatabaseName = N'<PleasePutAuditDatabaseNameHere>';

• Replace each entry within the angle brackets (< >) with the appropriate value for the audit logging database. For example:

SET @ServerName = N'VHT_Audit_Logging_Server';SET @UserId = N'VHT_Audit_Database_Administrator';SET @Password = N'Password';SET @DatabaseName = N'VHT_Audit_Database';

• If you are using SQL authentication, find the section denoted by "-- For SQL Authentication, uncomment and run this section:" and uncomment that section.
• If you are using Windows integrated security, find the section denoted by "-- For Windows Integrated Security, uncomment and run this section:" and uncomment that section.
• Run the VHT_Audit_Create_Connection.sql script. This will add the AuditDBConnectionString into the ApplicationSettings table.

If an entry exists in the ApplicationSettings table for the AuditDBConnectionString, all login/logout attempts to the Callback system and changes to users will be written to the AuditLogin and AuditUser tables.

Upgrading User Audit Logging for Version 8.2

If you set up user audit logging on a pre-8.2 version of Virtual Hold, and you upgrade to version 8.2 or later, you must run the VHT_Audit_Create_Tables.sql script again after upgrading. This will update the AuditLoginResultStatusDesc table with two new fields added in version 8.2.

Sample SQL Query

A sample SQL query named "VHT_Audit_Report_Query_Example.sql" can be found in the Virtual Hold Technology\SQL Server Database Installation Scripts directory. This query will retrieve all user audit records. Edit the last two lines to optionally select by start date/end date or by username. This query will also replace the numeric values of the description fields with their actual text descriptions, to make the results easier to understand.

SELECT AuditUser.AuditUserId
      ,AuditUser.InitiatingUser
      ,AuditUser.IpAddress
      ,AuditUser.AuditDateTime
      ,AuditUserChangeTypeDesc.Description AS ChangeType
      ,AuditUser.FirstName
      ,AuditUser.LastName
      ,AuditUser.UserName
      ,AuditUser.PasswordChanged
      ,AccessLevelDesc.Description AS AccessLevel
      ,SystemManagementAccessLevelDesc.Description AS SystemManagementAccess
      ,AuditUser.Status
      ,AuditUser.PasswordNeverExpires
      ,AuditUser.ChangePasswordNextLogin
  FROM dbo.AuditUser AS AuditUser
  LEFT JOIN dbo.AuditUserChangeTypeDesc AS AuditUserChangeTypeDesc ON
  AuditUser.ChangeType = AuditUserChangeTypeDesc.Value
  LEFT JOIN dbo.AccessLevelDesc AS AccessLevelDesc ON
  AuditUser.AccessLevel = AccessLevelDesc.Value
  LEFT JOIN dbo.SystemManagementAccessLevelDesc AS SystemManagementAccessLevelDesc ON
  AuditUser.SystemManagementAccess = SystemManagementAccessLevelDesc.Value
  --WHERE AuditUser.AuditDateTime between 'StartDate' and 'EndDate'
  --WHERE AuditUser.UserName = 'UserName'

Database Tables Used for Logging

Six tables are used for user audit logging:

• AuditLogin: contains the audit log for login and logout attempts
• AuditLoginResultStatusDesc: contains the cross reference for the LoginResultStatus codes contained in the AuditLogin table
• AuditUser: contains the audit log for addition/deletion/modification of users
• AuditUserChangeTypeDesc: contains the cross reference for the ChangeType codes contained in the AuditUser table
• SystemManagementAccessLevelDesc: contains the cross reference for the SystemManagementAccess codes contained in the AuditUser table
• AccessLevelDesc: contains the cross reference for the AccessLevel codes contained in the AuditUser table

The AuditLogin table contains the following fields:

The AuditLoginResultStatusDesc table contains the following fields:

The AuditUser table contains the following fields:

The AuditUser table will be updated as follows:

• When the data is initially loaded, or when a new user is added, all of the fields that contain values will populate.
• When user-related data is changed, or when a user is deleted, only the fields that changed will populate. The remaining fields will be set to NULL.

The AuditUserChangeTypeDesc table contains the following fields:

The SystemManagementAccessLevelDesc table contains the following fields:

The AccessLevelDesc table contains the following fields: