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
Sophos Reporting Interface
In the default configuration file the <connectionString> element is commented out (surrounded by "<!--" and "-->" tags). If the connection string is commented out or not present in the configuration file then the service will attempt to find the appropriate settings by scanning the registry for a SEC management service connection string. However, if the Log Writer is installed on a different machine to the management service then a connection string must be specified.
For typical installations, only the version of Enterprise Console, database server name and instance must be modified. If you have a non- standard database setup, a description of how to edit connection parameters is available from the Microsoft website at the following location:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
Note:
If the <connectionString> element is present but specifies an incorrect or empty connection
string (such as DataSource="") the service will fail to start and will not look for the registry value.
If a connection to the database has been specified, a <noOfDays> element must be defined
which determines how many days of historical data to retrieve.
The <commandTimeout> element specifies the time SQL server must wait before a
command times out. It is optional and if it is not specified the server will wait indefinitely.
<?xml version=1.0 encoding=utf-8 ?> <SophosDatafeed xmlns= http://www.sophos.com/msys/LogWriterConfig.xsd> <connection> <connectionString> Integrated Security = SSPI; Persist Security Info = False; Initial Catalog = Sophos[SECVersion]; Data Source = [SERVER]\[INSTANCE] </connectionString> <commandTimeout>[TIMEOUT IN SECONDS] </commandTimeout> </connection> <noOfDays> [AGE OF HISTORICAL DATA]</noOfDays>
2. Define custom datafeeds to extract information from the database. We recommend adding only one feed at a time as this helps in troubleshooting and reduces the load on the database. The datafeed definition is as follows:
Note:
Each datafeed must specify a single <tick> and <logFile> element. They specify the frequency
to check the database for new data and the location to save data.
The <applyLogFormat> element takes a value of either true or false and specifies whether
to prefix each line with the date and time the line was written to the log file. This can be useful if a third-party tool such as Splunk is used which automatically picks up the first date on each line of the log file. If it is not set then the log file date is not prefixed.
8
user guide
The size of the log file is bounded by the <file_size> element and log rotation is performed
using the <no_of_files> element. It specifies the number of times the log will be rotated before being deleted. The log file without suffix is the latest and the log file with highest number suffix is the oldest, the oldest file will be overwritten.
Each datafeed contains one or more <call> elements which are labelled with a unique callID
attribute. The Log Writer keeps track of each call made by storing a timestamp for each call in a "[CallID].last" file. The callID must be unique.
<datafeeds> <datafeed> <tick> [POLL TIME IN SECONDS] </tick> <applyLogFormat> [LOG FORMAT] </applyLogFormat> <logFile> <noOfFiles> [NUMBER OF BACKUP FILES] </noOfFiles> <fileSize> [MAX FILE SIZE KB/MB/GB] </fileSize> <outputLocataion> [LOG FILE LOCATION] </outputLocation> <outputFilename> [LOG FILE NAME] </outputFilename> </logFile>
<call callID = [UNIQUE CALL NAME]> <dataSource> [DATA SOURCE TO USE] </dataSource> <dataConfigurationLocation>[CALL DATA CONFIGFILE LOCATION]</dataConfigurationLocation> <dataConfigurationFile>[CALL DATA CONFIG FILENAME]</dataConfigurationFile> </call> ... </datafeed> ... </datafeeds> </SophosDatafeed>
3. If you want to edit the data sources, you can edit the <call> element. It specifies the data source to extract data and associates it with a data formatting file that determines the columns of the available data which should be saved. The data formatting file can be constructed as an ordered list of required fields as follows:
Note:
The field name attribute can use any name.
The link attribute must use a valid Reporting Interface field for the data source.
For enabled attribute, 0 indicates data will not be extracted and 1 indicates data will be
extracted.
<?xml version=1.0 encoding=utf-8 ?> <LogFile> <Events>
9
Sophos Reporting Interface
<field name=[FIELDNAME] link=[FIELDNAME] enabled=1 />
... </Events> </LogFile>
4.
Start the Sophos Reporting Log Writer service.
Note:
You must restart the Log Writer service for any configuration changes.
Before you start the Log Writer service with a new configuration, we recommend you stop
the Sophos Management Service whilst the Log Writer initializes new datafeeds and downloads historical data from the database.

10 What information can be accessed?

Sophos Enterprise Console records logging information on:
Computers
Packages
Groups
Events
Threats

10.1 Computers

Computers are the individual endpoints currently being monitored by Enterprise Console and are uniquely identified by their ComputerID.You can access computer's logging information using the following database views:
vComputerHostData provides information on each computer monitored by SEC.
vPolicyComplianceData lists which policies have been applied to each computer.

10.2 Groups

Groups are a logical grouping of computers made from within Enterprise Console and are uniquely identified by their GroupID. You can access groups logging information using the following database views:
10
vGroupPathAndNameData provides a list of group labels.
vComputerGroupMapping lists which computers belong in which groups.

10.3 Packages

Packages are particular versions of Sophos Anti-Virus that may be present on the network and are uniquely identified by their PackageID. You can access package's logging information using the following database views:
vPackageData lists the versions of Sophos Anti-Virus currently running on the network.
vComputerPackageMapping lists which package each computer currently has installed.

10.4 Events

Events are notifications of events that have occurred on endpoints and are uniquely identified by their EventID.
Events are classified by their type into different categories. vEventsCommonData provides basic information on all events that have occurred and includes an EventTypeName to denote which of the following views will contain additional category-specific information on the event:
user guide
Application Control using vEventsApplicationControlData
Data Control using vEventsDataControlData
Device Control using vEventsDeviceControlData
Firewall using vEventsFirewallData
Tamper Protection using vEventsTamperProtectionData
Web using vEventsWebData
Threat Response using vThreatEventData

10.5 Threats

Threats are instances of malware that have been detected on endpoints and are uniquely identified by their ThreatID.
vThreatInstances lists the threats that have been detected on each computer.
vThreatEventData provides a list of actions that have been performed in response to threats
detected on the network.

10.6 Which datasources are linked?

When merging data from multiple views, rows from each view that reference the same entity will need to be combined. This can be achieved by combining rows that reference the same entity ID
11
Sophos Reporting Interface
numbers. The following diagram shows which fields are responsible for joining each of the available views.

11 Reporting Interface data sources

The following data sources are available for Reporting Interface.
Note: Letter of the alphabet listed besides the data source is used in the table below to represent its availability for the data field.
A. vComputerHostData
B. vThreatInstances
C. vEventsCommonData
D. vEventsApplicationControlData
E. vEventsDataControlData
F. vEventsDeviceControlData
G. vEventsFirewallData
H. vEventsTamperProtectionData (SEC 4.5 and above)
I. vEventsWebData (SEC 4.5 and above)
12
user guide
J. vThreatEventData
K. vComputerGroupMapping
L. vGroupPathAndNameData
M. vComputerPackageMapping
N. vPackageData
O. vPolicyComplianceData
The data fields available for each of these data sources are listed in the table below. All date-time columns are returned in UTC in the format "yyyy-mm-dd hh:mi:ss" (24 hours).
Data sourceData typeData field
ONMLKJIHGFEDCBA
intComputerID
nvarcharName
datetimeInsertedAt
intEventID
datetimeEventTime
nvarcharUserName
intEventTypeID
nvarcharEventTypeName
nvarcharReportingName
intActionID
nvarcharActionName
intSubTypeID
nvarcharSubTypeName
intScanTypeID
nvarcharScanTypeName
nvarcharDomain
nvarcharDescription
nvarcharIPAddress
13
Sophos Reporting Interface
Time
Data sourceData typeData field
ONMLKJIHGFEDCBA
datetimeLastMessageReceived
intThreatID
intThreatTypeID
nvarcharThreatTypeName
intThreatSubTypeID
nvarcharThreatSubTypeName
intPriority
nvarcharThreatName
nvarcharFullFilePath
nvarcharFileVersion
nvarcharCheckSum
datetimeFirstDetectedAt
nvarcharRuleName
nvarcharTrueFileType
nvarcharDestinationPath
intDestinationTypeID
nvarcharDestinationTypeName
nvarcharSourcePath
nvarcharFileName
nvarcharDestinationValue
intDeviceTypeID
nvarcharDeviceTypeName
nvarcharModel
14
intDeviceID
nvarcharRole
user guide
Data sourceData typeData field
ONMLKJIHGFEDCBA
nvarcharFileName
nvarcharFilePath
nvarcharFileVersion
nvarcharFileChecksum
nvarcharCommandLine
nvarcharSession
nvarcharDesktop
nvarcharLocation
intProtocolID
nvarcharProtocolText
intDirectionID
nvarcharDirectionText
nvarcharLocalAddress
nvarcharRemoteAddress
intLocalPort
intRemotePort
intTargetTypeID
nvarcharTargetTypeText
nvarcharTarget
intRuleID
nvarcharBlockedSite
nvarcharReferringURL
intActionTakenID
nvarcharActionTakenName
intScannerTypeID
nvarcharScannerTypeName
15
Sophos Reporting Interface
Data sourceData typeData field
ONMLKJIHGFEDCBA
intStatusID
nvarcharStatusName
intGroupID
nvarcharPathAndName
intDepth
intPackageID
nvarcharProduct
nvarcharSAVVersion
nvarcharEngineVersion

12 Log Writer data sources

The following data sources are available for Log Writer.
Note: Letter of the alphabet listed beside each data source is used in the table below to represent its availability for the data field.
A. EventsApplicationControlData
nvarcharVirusDataVersion
datetimeExpiryTime
datetimeNotificationTime
bitExpired
intPolicyTypeID
nvarcharPolicyTypeName
intComplianceID
nvarcharComplianceName
16
B. EventsCommonData
C. EventsDataControlData
D. EventsDeviceControlData
user guide
E. EventsFirewallData
F. EventsTamperProtectionData (SEC 4.5 and above)
G. EventsWebData (SEC 4.5 and above)
H. ThreatEventData
I. ThreatInstances
The data fields available for each of these data sources are listed in the table below. All date-time columns are returned in UTC in the format "yyyy-mm-dd hh:mi:ss" (24 hours).
Data sourceData typeData field
IHGFEDCBA
integerEventID
datetimeEventTime
integerEventTypeID
nvarcharEventTypeName
integerSubTypeID
nvarcharSubTypeName
datetimeInsertedAt
nvarcharUserName
nvarcharComputerName
nvarcharComputerDomain
nvarcharComputerIPAddress
nvarcharName
nvarcharReportingName
integerActionID
nvarcharActionName
integerScanTypeID
nvarcharScanTypeName
nvarcharRuleName
nvarcharTrueFileType
17
Sophos Reporting Interface
Data sourceData typeData field
IHGFEDCBA
nvarcharDestinationPath
integerDestinationTypeID
nvarcharDestinationTypeName
nvarcharSourcePath
nvarcharFileName
nvarcharDestinationValue
integerDeviceTypeID
nvarcharDeviceTypeName
nvarcharModel
nvarcharDeviceID
nvarcharRole
nvarcharFilePath
nvarcharFileVersion
nvarcharFileChecksum
nvarcharCommandLine
nvarcharSession
nvarcharDesktop
nvarcharLocation
integerProtocolID
nvarcharProtocolText
integerDirectionID
nvarcharDirectionText
nvarcharLocalAddress
18
nvarcharRemoteAddress
integerLocalPort
integerRemotePort
user guide
Data sourceData typeData field
IHGFEDCBA
nvarcharTarget
integerTargetTypeID
nvarcharTargetTypeText
nvarcharRuleID
nvarcharBlockedSite
nvarcharReferringURL
integerActionTakenID
nvarcharActionTakenName
integerScannerTypeID
nvarcharScannerTypeName
integerStatusID
nvarcharStatusName
integerThreatID
nvarcharThreatName
integerThreatTypeID
nvarcharThreatTypeName
integerThreatSubTypeID
nvarcharThreatSubTypeName
nvarcharFullFilePath
nvarcharCheckSum
datetimeFirstDetectedAt
integerPriority
19
Sophos Reporting Interface

13 What happens if I upgrade Enterprise Console?

If you upgrade Enterprise Console after you have installed Sophos Reporting Interface, you must:
1. Re-install Sophos Reporting Interface based on the version of Enterprise Console you have upgraded to.
For information on how to install Reporting Interface, see Install Sophos Reporting Interface (page 4).
2. If you have Sophos Reporting Log Writer installed, edit its configuration file to update the version of SEC.
For information on how to edit the Log Writer configuration file, see Configure Log Writer (page 7).

14 Uninstall Sophos Reporting Interface

Note: We recommended you to take a full backup of the SEC database before and after the uninstallation process. Also, the SQL scripts must be verified by the database administrator to ensure the default values are appropriate for your setup.
To uninstall Reporting Interface:
1. Find the DB folder that has been extracted.
2. Identify the batch file based on your product version.
If you are uninstalling Reporting Interface from a server that has the default SOPHOS instance selected during the SEC database installation, double-click the batch file. It requires no additional parameters.
If you are uninstalling Reporting Interface that has a custom database configuration, you must run the batch file with additional parameters as follows:
UninstallReportingInterface.bat [SERVER\INSTANCE] [DOMAIN] [LOGFILE]

15 Uninstall Sophos Reporting Log Writer

Note: During the uninstallation of Log Writer, the configuration file will also be deleted. We recommend you to take a backup the configuration file if you plan to install Log Writer again.
20
To uninstall Log Writer:
1.
Open Control Panel > Add/Remove Programs.
2. In the Add/Remove Programs dialog box, select Sophos Reporting Log Writer and click Remove.
3. In the Confirm Uninstall message box, click Yes.
A progress bar is displayed. Wait for uninstallation to complete.

16 Technical support

You can find technical support for Sophos products in any of these ways:
Visit the SophosTalk community at http://community.sophos.com/ and search for other users who are experiencing the same problem.
Visit the Sophos support knowledgebase at http://www.sophos.com/support/.
Download the product documentation at http://www.sophos.com/support/docs/.
Send an email to support@sophos.com, including your Sophos software version number(s), operating system(s) and patch level(s), and the text of any error messages.
user guide

17 Legal notices

Copyright © 2011 Sophos Limited. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or otherwise unless you are either a valid licensee where the documentation can be reproduced in accordance with the licence terms or you otherwise have the prior permission in writing of the copyright owner.
Sophos and Sophos Anti-Virus are registered trademarks of Sophos Limited. All other product and company names mentioned are trademarks or registered trademarks of their respective owners.
21
Loading...