TRITON reporting database FAQs

Administering Websense Databases | Web, Data, and Email Security Solutions | v7.6.x - 7.8.x

Applies to:

In this topic

Web Filter, Web Security, and Web Security Gateway (Anywhere), v7.6.x - v7.8.x

Data Security, v7.6.x - v7.8.x

Email Security Gateway (Anywhere), v7.6.x - v7.8.x

Which database tools are required or used?

Which permissions are required?

Which database jobs are run?

How does the installer set up each database?

How big should the database partitions be?

How many partitions can be accessed at the same time?

How do I configure partition rollover?

What if I need more partitions to run reports?

Do Websense databases use named instances?

Can Websense reporting databases be hosted in a SQL Server cluster?

Which database tools are required or used?

Websense reporting components connect to the SQL Server database engine as clients and perform standard Transact-SQL commands and stored procedures.

Web Security and Email Security Gateway may use 2 database utilities:

bcp to use bulk insertion for adding logs to the database.

osql to run SQL scripts during Log Database installation.

Which permissions are required?

During Data Security installation, the account used for database creation and access needs sysadmin server role membership. Also, Backup database permission on the master database is required for installation only. After Data Security installation, the account privileges can be reduced to the db_owner of the newly created databases, and no access to any other user database except system databases such as master, tempdb, and model.

If you're using SQL Server 2012 Standard or Enterprise, 2008, 2008 R2 or 2005 SP4 (Web Security only) to install the Web Security Log Server and Email Security Log Server, the user account that owns the Websense database must:

Be a member of the dbcreator server role

In the msdb database:

Have membership in the db_datareader role

Have membership in one of the following roles:

SQLAgentUser Role

SQLAgentReader Role

SQLAgentOperator Role

For SQL Server 2008 R2 Express, the user account requires the sysadmin server role.

Which database jobs are run?

The following database jobs are installed with the Web Security Log Database and Email Security Log Database:

The Extract, Transform, and Load (ETL) job runs continually, receiving data from Log Server, processing it, and then inserting it into the partition database. The ETL job must be running to process log records into the Log Database.

The database maintenance job performs database maintenance tasks. This job runs nightly, by default.

ETL jobs are run, then re-run 10 seconds after they finish for SQL Server Standard and Enterprise. For SQL Server Express, 60 seconds elapse between completion of one job and start of the next.

Maintenance jobs are run once every night by default. The jobs are run automatically.

The Web Security Log Database also installs the following jobs:

The Internet browse time (IBT) job analyzes data and calculates browse time for each user. The IBT database job is resource intensive, requiring significant server resources. This job runs nightly.

(v7.7.x) When trend data retention is enabled, the trend job uses daily trend data created by the ETL job to update weekly, monthly, and yearly trend records for use in presentation reports. This job runs nightly.

Even when trend data retention is disabled, the trend job processes data from the threats (AMT) partition to provide trend data on the Threats dashboard.

(v7.7.x) The Advanced Malware Threat (AMT) ETL job receives, processes, and inserts data into the threats partition database. Only log records that include a severity ranking are recorded in the threats partition. Data from this partition is used to populate the Threats dashboard.

When configuring the start time for the (Web and Email Security) maintenance job and the (Web Security) Internet browse time job, consider system resources, and IT maintenance tasks and their duration. These jobs can be resource intensive and time consuming, so they can have a negative impact on logging and reporting performance.

Both Log Databases require either the SQL Server Agent service (SQL Server Standard or Enterprise) or Service Broker (SQL Server Express) to run database jobs.

How does the installer set up each database?

The TRITON reporting databases should allow TCP and trusted-mode connections from the TRITON management server, Email Security Log Server, and Web Security Log Server, as well as from the any email-capable V-Series appliance (Email Security or Web and Email Security mode).

Web Security Log Database

By default, the Web Security Log Database includes one catalog database, one standard logging partition database, and (v7.7 and later only) one threats (AMT) partition database. Typically, multiple standard logging partition databases are created as Internet activity is recorded.

The catalog database provides a single connection point for the various components that need to access the Log Database: Log Server, presentation reports, and investigative reports configuration. It also contains definitions for the following:

Category names

Risk classes

Users

User-to-group mapping

Database job information

The catalog database also maintains a list of all the database partitions.

Standard logging partitions store the individual log records of Internet activity. New partitions are created based on size (5 GB, by default) or date interval.

(v7.7.x) The threats (AMT) partition stores information about requests that have been assigned a severity level, and is used to populate the Threats dashboard.

Email Security Log Database

The Email Security Log Database includes one catalog database and (initially) a standard logging partition.

The catalog database provides a single connection point for the various components that need to access the Log Database: Log Server, the Email Security Gateway quarantine daemon, and Email Security manager (presentation reports, dashboard, log database configuration page). It also includes definitions for the following:

Email Security Gateway actions

Mail direction

Message type

DLP severity level

Email Security Gateway appliance mapping

Email Security Gateway policies

The catalog database also maintains a list of all the database partitions.

Database partitions store the individual log records, including connection log, message log, policy log, delivery log, status log, hybrid service status log. New partitions are created based on size (5 GB, by default) or date interval.

How big should the database partitions be?

For Web Security, see Partitioning.

For Email Security, see Partitioning.

For Data Security, see Rate of network and endpoint incidents.

How many partitions can be accessed at the same time?

Data Security maintains incident partitions independently of the database engine, based on quarters (3-month periods). By default, SQL Server Express maintains 8 partitions are online simultaneously, and other SQL Server editions maintain 12 partitions online. You can choose to move any number of partitions online simultaneously as long as your disk space and SQL Server database permit it.

With Web and Email Security solutions, you can access all enabled partitions.

How do I configure partition rollover?

With Web and Email Security solutions, partition rollover can occur automatically when partitions reach a specified size or (SQL Server Standard or Enterprise) date.

When partition rollover is based on size, all log records are inserted into the most recent active partition that satisfies the size rule. When the partition reaches the designated maximum size, a new partition is created.

When partition rollover is based on date, new partitions are created according to the established cycle. For example, if the rollover option is monthly, a new partition is created as soon as any records are received for the new month. Log records are inserted into the appropriate partition based on date.

Partition rollover can also be initiated manually.

For information about configuring automatic or manual rollover, see:

"Configuring database partition options" in the v7.6, v7.7, or v7.8 TRITON - Web Security Help.

"Configuring Log Database options" in the v7.6, v7.7, or v7.8 TRITON - Email Security Help.

For Data Security solutions, partition rollover is configured on the Settings > Archive page in Data Security manager. Here, you configure when to create an archive partition and when to restore it. For instructions, refer to "Archiving incidents" in the v7.6, v7.7, or v7.8 Data Security Help.

What if I need more partitions to run reports?

For Web and Email Security, the available Log Database partitions, both enabled and disabled, are listed on the Settings > Reporting > Log Database page in the respective Web and Email Security modules of the TRITON console. To include data from a disabled partition, first enable it, then run the report. You can use this page to disable the partition again once you have retrieved the desired data.

For Data Security, when you want to run a report and some or all of the data you want is stored in an offline partition, you must bring that partition online, or the generated report will not contain all the data you need.

Do Websense databases use named instances?

If you are using SQL Server Standard or Enterprise to host your Websense reporting databases:

The Web Security Log Database can be hosted by one instance, and the Email Security Log Database and Data Security Incident and Configuration Database can be hosted elsewhere (on another server or instance).

All 3 reporting databases can be hosted by the same SQL Server instance.

Can Websense reporting databases be hosted in a SQL Server cluster?

If your organization uses a SQL Server cluster to provide failover for your database servers, the Websense reporting databases can be hosted by the cluster if:

A virtual IP address is assigned to the cluster.

The data managed by SQL Server is housed on a reliable shared disk array.

When you install reporting components in a network that uses a SQL Server cluster, it is imperative that the cluster's virtual IP address is used to configure the reporting database connection. When this is done, reporting data is sent to SQL Server via the virtual IP address.

If you configure Websense reporting components (like Web Security and Email Security Log Server) to use the IP address of an individual node in the cluster, they cannot take advantage of the failover protection of the cluster.

If the configured node becomes unavailable, reporting components cannot process data into the reporting database.

For Web and Email Security, log cache files continue to be saved on the Log Server machine. These files can build up quickly and fill the disk, causing additional problems.

When failover occurs, reporting components must wait briefly while the secondary SQL Server is made primary. When SQL Server begins accepting data over the virtual IP address again, reporting data is once again sent successfully.

This pause in recording data occurs both when failover occurs in a SQL Server cluster and when a standalone SQL Server installation fails and is later brought back online. Any records that were actively being processed into the reporting database when the primary SQL Server fails is be lost.

For Web and Email Security, if you are using BCP log insertion, the loss is generally a full batch (log cache file) of filtering records.

With ODBC log insertion, fewer records may be lost.