Sophos REPORTING INTERFACE User manual

Sophos Reporting Interface
user guide
This guide provides information on how to install and use:
Sophos Reporting Interface
Sophos Reporting Log Writer
Product version: 1.0 Document date: January 2011

Contents

3 What are the key steps?.............................................................................................................................3
4 Check the requirements............................................................................................................................4
6 Install Sophos Reporting Interface ..........................................................................................................4
7 Configure Reporting Interface with Crystal Reports..............................................................................5
8 Install Sophos Reporting Log Writer.......................................................................................................6
9 Configure Log Writer................................................................................................................................7
10 What information can be accessed?.....................................................................................................10
12 Log Writer data sources........................................................................................................................16
13 What happens if I upgrade Enterprise Console?.................................................................................20
14 Uninstall Sophos Reporting Interface..................................................................................................20
15 Uninstall Sophos Reporting Log Writer...............................................................................................20
17 Legal notices..........................................................................................................................................21
2

1 About this guide

This guide describes Sophos tools that enable you to use third-party reporting and log-monitoring software to generate reports from threat and event data in Sophos Enterprise Console. It is intended for use by system administrators and database administrators.
It is assumed that you are familiar with and already using Sophos Enterprise Console (SEC) version
4.0 or later.
Sophos documentation is published at http://www.sophos.com/support/docs/.

2 What do I install?

You install Sophos Reporting Interface and (optionally) Sophos Reporting Log writer.
Sophos Reporting Interface enables direct access to the Enterprise Console database and allows the use of third-party applications such as Crystal Reports to communicate with the SQL server directly. The Sophos Reporting Interface must be installed on the same computer that has the Enterprise Console database installed.
user guide
Sophos Reporting Log Writer allows the use of third-party log-monitoring applications, for example Splunk, which retrieve data from plain text files rather than directly from a database. The Sophos Reporting Log Writer can be installed on a standalone computer, or on any computer that has access to the Enterprise Console database.
Note: You must install Sophos Reporting Interface before you install Sophos Reporting Log Writer.
Important: The Sophos Reporting Interface and the Sophos Reporting Log Writer make SEC data available to third-party applications. By installing either of these you assume the responsibility of the security of the data made available, which includes ensuring the data will only be made available to authorized users.

3 What are the key steps?

You carry out these key steps:
1. Check the requirements.
2. Check the version of Sophos Enterprise Console.
3. Install Sophos Reporting Interface.
4. (Optional) Configure Reporting Interface with Crystal Reports.
5. Install Sophos Reporting Log Writer, if you want to use third-party log-monitoring applications such as Splunk.
6. (Optional) Configure Log Writer.
3
Sophos Reporting Interface

4 Check the requirements

You should check that you have:
SEC 4.0, SEC 4.5, or SEC 4.7 installed.
a valid, complete backup of your database and Enterprise Console installation.
the necessary administrator privileges to makes changes to the Enterprise Console database
during the Reporting Interface installation.
.NET Framework 2.0 or later installed and sufficient privileges to install a new service on the
computer where Log Writer will be installed.

5 Check the version of Enterprise Console

To check the product version of Sophos Enterprise Console:
1. Open Sophos Enterprise Console.
2. Click on Help menu and click About Sophos Enterprise Console.
The About Sophos Enterprise Console window is displayed.
3. Make a note of the product version number.

6 Install Sophos Reporting Interface

Note:
The data retrieved by Reporting Interface may contain confidential information about your
computers. You should restrict access to this information. We recommend that you enable encryption in SQL Server when you are using remote databases. For information about encryption for Microsoft SQL Server, see
http://technet.microsoft.com/en-us/library/bb510663.aspx.
Additional queries made to the SEC database whilst accessing the Reporting Interface could
impact the performance of other database operations such as Sophos Enterprise Console. There may be a noticeable decrease in performance of Enterprise Console during large transfers of data from the Reporting Interface.
Sophos Reporting Interface must be installed on the computer that has the Enterprise Console database installed.
To install Reporting Interface:
1. Ensure you have a valid, complete backup of your database and Enterprise Console installation.
4
2. Find the DB folder that has been extracted.
3. Identify the batch file based on your product version.
If you are installing Reporting Interface on a server that uses the default SOPHOS instance selected during the SEC database installation, double-click the batch file. It requires no additional parameters.
If you are installing Reporting Interface with a custom database configuration, you must run the batch file with additional parameters as follows:
InstallReportingInterface.bat [SERVER\INSTANCE] [DOMAIN] [LOGFILE]
This will update the relevant database.
The installation script will generate a log file InstallSophosReportingInterface.log in its working folder. This log file will show if the installation was successful or detail any errors that have occurred during the installation.

7 Configure Reporting Interface with Crystal Reports

user guide
You can configure Reporting Interface with Crystal Reports. We recommend using Crystal Reports version 2008.
Note: The Crystal Reports Wizard will automatically link columns with identical names between views that have been included in a report. However, some of the connections must be removed as similarly named columns do not necessarily have identical values for a single log event.
For example, the InsertedAt column is present in every view which denotes when each entry was added to the database. However, a single event may have different InsertedAt times for its corresponding entries in each view. If the Crystal Reports Wizard automatically links these columns, the links must be removed to prevent missing data. For information on which data sources are linked, see Which datasources are linked? (page 11)
To create Reporting Interface connection with Crystal Reports:
1.
Open Crystal Reports and create a new connection using OLE DB (ADO) and choose Microsoft OLE DB Provider for SQL Server.
2. Enter the connection information and complete the wizard.
Sophos Reporting Interface will now be listed in the available data sources. For information on how to generate custom reports, see the Crystal Reports documentation.
For a list of data sources that are available for Log Writer, see Reporting Interface data sources (page
12).
For more information and examples on using Crystal Reports to access data provided by the Sophos Reporting Interface, see the Sophos knowledge base article 112873
http://www.sophos.com/support/knowledgebase/article/112873.html.
5
Sophos Reporting Interface

8 Install Sophos Reporting Log Writer

You can install the Sophos Reporting Log Writer, after installing Sophos Reporting Interface.
Note: The data retrieved by Log Writer may contain confidential information about the computers managed by SEC. You should restrict access to this information. We recommend that the access permissions of the installation folder, data formatting files and log files are all restricted to an appropriate administrator account. Also, since the data transferred from the Sophos Reporting Interface to the log files is unencrypted the log files should only be written to the local machine rather than transferring the data over an unencrypted network transport such as SAMBA/CIFS shares.

8.1 Recommendations

We recommend that the Log Writer is installed on the computer that has the management server installed. However, it can be installed on any server that has access to the Sophos Enterprise Console database
The Log Writer service will be installed under the LocalSystem account by default which has full access privileges to the local server. We strongly recommend that you reassign the service to an account with lower access privileges after installation. If the service is installed to a computer other than the management server it will need to be run under a user account with the appropriate permissions to access the SEC database remotely.
Note: Make sure the Log Writer computer and the database computer have their computer's location, time zone, and clock set correctly based on their location.

8.2 Installation

To install Log Writer:
1. Find the Log Writer installer (InstallLogWriter.msi) file that has been extracted.
If you want to generate a verbose log file during the installation of Log Writer use the following command: msiexec /l*v logfile.txt /i "SophosReportingLogWriter.msi"
The log file will be created in the folder in which the command was executed. If you do not want to generate a log file continue to next step.
2. Double-click on the InstallLogWriter.msi file.
3.
In the Sophos Reporting Log Writer Setup dialog box, click Next.
A wizard guides you through installation.
6
user guide
4. When installation is complete, click Finish. If you have the Show configuration file check box selected, a window appears with the default configuration file, SophosLogWriterConfig.xml, highlighted.
If you want to use the default configuration that is provided with Log Writer, continue to the next step and start the Log Writer service. For information on default configuration, see Default Log Writer configuration (page 7).
To edit the Log Writer configuration file, see Configure Log Writer (page 7).
5. To start the Log Writer service:
a)
Open Control Panel and double-click Administrative Tools.
b)
In Administrative Tools window, double-click on Services.
The list of available services is displayed.
c)
Select Sophos Reporting Log Writer and click Start to start the service.
Log Writer reads the configuration file when it is first started and requires a restart of the service for any configuration changes.

8.3 Default Log Writer configuration

The default configuration file contains two datafeeds. The first datafeed will write to a log file DefaultCommonEvents.log. It extracts common event data using the EventsCommonData data source. The second datafeed will write to a log file DefaultThreats.log. It extracts the threat event data using the ThreatEventData data source.
The default log file will be in the 'Log Files' folder using the default data formatting files in the 'Configuration Files' folder located in the Log Writer installation folder. Data for the last 7 days will be extracted when the service is started with the default configuration.

9 Configure Log Writer

The Configuration Files folder is located in the Log Writer's installation folder. The folder contains an example configuration file for each of the available data sources with every available column from the respective data source listed. You can customize them based on your requirements.
The configuration file is available at the following location by default: C:\Program Files\Sophos\Reporting Interface\SophosLogWriterConfig.xml.
For a list of data sources that are available for Log Writer, see Log Writer data sources (page 16).
To edit the Log Writer configuration file:
1. Modify the connection settings <connectionString> element which determines how Log Writer contacts the Enterprise Console database:
7
+ 14 hidden pages