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
1 About this guide........................................................................................................................................3
2 What do I install?......................................................................................................................................3
3 What are the key steps?.............................................................................................................................3
4 Check the requirements............................................................................................................................4
5 Check the version of Enterprise Console.................................................................................................4
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/.
2What 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.
3What 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
4Check 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.
5Check 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.
6Install 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
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:
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.
7Configure 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 MicrosoftOLE 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
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.1Recommendations
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.2Installation
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.3Default 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.
9Configure 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:
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.
<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
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.
10What information can be accessed?
Sophos Enterprise Console records logging information on:
Computers
■
Packages
■
Groups
■
Events
■
Threats
■
10.1Computers
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.2Groups
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.3Packages
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.4Events
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.5Threats
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.6Which 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.
11Reporting 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
12Log 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
13What 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).
14Uninstall 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:
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 clickRemove.
3. In the Confirm Uninstall message box, click Yes.
A progress bar is displayed. Wait for uninstallation to complete.
16Technical 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.
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...
+ hidden pages
You need points to download manuals.
1 point = 1 manual.
You can buy points or you can get point for every manual you upload.