Reply
Moderator
sandy
Posts: 1,033
Registered: ‎Mon 16-Nov-2009
0
Accepted Solution

Sophos Reporting Interface

[ Edited ]

The Sophos Reporting Interface and Sophos Reporting Log Writer provide the components that allow third-party applications to access Sophos Enterprise Console (SEC) threat and event data.

  • 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.
  • 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.
    To enable log-monitoring tools, the Sophos Reporting Log Writer service must be installed in addition to the Sophos Reporting Interface.

 

for more information and a link to the download page, go to  http://www.sophos.com/security/sophoslabs/reporting-interface.html

 

SophosTalk forum invites you to post on this forum examples of how you find it best to integrate your own reporting tools with the Sophos Reporting Interface, and samples of reports you have created.


Sandy.

Communities Moderator, Sophos
Knowledgebase  |  @SophosSupport  |  Video tutorials
If a post solves your question use the Accept as Solution button and award kudos.
Moderator
Moderator
EMK
Posts: 66
Registered: ‎Mon 23-Nov-2009

Re: Notification: Sophos Reporting Interface is now available

[ Edited ]

Here's a quick SQL statement to start people off using the interface.  It references the SOPHOS45 database so would need to be updated for those of you running SEC 4 to SOPHOS4.

This should return computer and SAV version information.  It only returns the TOP 1000 records in this example so please adjust as required and feel free to expand it with a WHERE clause in order to filter the results as required.

SELECT top 1000 c.Name AS [Computer Name], 
c.Domain AS [Domain Name],
ISNULL(c.IPAddress,N'') AS [IP],
ISNULL(c.LastMessageReceivedTime,N'') AS [LastRMSMessageTime],
g.PathAndName AS [SEC Group],
ISNULL(p.SAVVersion,N'') AS [SAV Version],
ISNULL(p.VirusDataVersion,N'') AS [SAV Virus Data Version],
ISNULL(p.EngineVersion,N'') AS [SAV Engine Version]
FROM [SOPHOS45].[Sophos Reporting Interface].[vComputerHostData] AS c 
LEFT OUTER JOIN [SOPHOS45].[Sophos Reporting Interface].[vComputerPackageMapping] AS cpm
ON cpm.ComputerID = c.ComputerID
LEFT OUTER JOIN [SOPHOS45].[Sophos Reporting Interface].[vPackageData] AS p
ON p.PackageID = cpm.PackageID
LEFT OUTER JOIN [SOPHOS45].[Sophos Reporting Interface].[vComputerGroupMapping] AS cgm
ON cgm.ComputerID = c.ComputerID 
LEFT OUTER JOIN [SOPHOS45].[Sophos Reporting Interface].vGroupPathAndNameData AS g
ON g.GroupID = cgm.GroupID
ORDER BY c.Name ASC

 Output would be something like:

Computer Name, Domain Name, IP, LastRMSMessageTime, SEC Group, SAV Version, SAV Virus Data Version, SAV Engine Version
XPClient1,Domain1,192.168.75.129,2011-01-24 14:45:26,AD,9.5.4 VDL4.61G,4.61G,3.15.0
XPCLient2,Domain1,192.168.75.10,2011-01-24 15:29:08,Unassigned,,,

 

Thanks,

E.

Moderator
Moderator
EMK
Posts: 66
Registered: ‎Mon 23-Nov-2009

Re: Notification: Sophos Reporting Interface is now available

The top 10 threats by count might be interesting:

SELECT TOP 10 [ThreatName], COUNT(*) AS [Number of Occurrences]
FROM [SOPHOS45].[Sophos Reporting Interface].[vThreatInstances]
GROUP BY ThreatName
ORDER BY COUNT(*) DESC

 

Example output:

 

EICAR-AV-Test	14294
HIPS/ProcMod-007	279
HIPS/RegMod-005	253
Mal/Packer	248
Mal/Generic-A	235
HIPS/RegMod-014	228
HIPS/RegMod-010	191
HIPS/IPConnect-001	134
LiveProtectTest	123
HIPS/ProcMod-004	119

 Regards,

E.

Moderator
Moderator
EMK
Posts: 66
Registered: ‎Mon 23-Nov-2009
0

Re: Notification: Sophos Reporting Interface is now available

Top 10 threat counts per machine might also be of use.

SELECT TOP 10 c.Name AS [Computer Name], 
c.Domain AS [Domain Name],
COUNT(*) AS [Threat Counts]
FROM [SOPHOS45].[Sophos Reporting Interface].[vComputerHostData] AS c 
INNER JOIN [SOPHOS45].[Sophos Reporting Interface].[vThreatInstances] AS ti
ON ti.ComputerID = c.ComputerID 
GROUP BY c.Name, c.Domain
ORDER BY COUNT(*) DESC

 

Example output:

Computer Name, Domain Name, Threat Counts
XPClient1,Domain1,40
XPCLient2,Domain1,33
XPClient3,Domain1,25
XPCLient4,Domain1,25
XPClient5,Domain1,23
XPCLient6,Domain1,6
XPClient7,Domain2,5
XPCLient8,Domain2,3
XPClient9,Domain2,2
XPCLient1,Domain1,1

 

Regards,

E.

Executive VIP
jak
Posts: 1,813
Registered: ‎Sat 19-Dec-2009

Re: Notification: Sophos Reporting Interface is now available

[ Edited ]

Hi,

 

Thanks for the queries.

 

How about this for a rather rudimentary and fallible attempt to help with source of infection.  It should, give a list of distinct ThreatNames and from that the computer and threat details of the machine that first reported the threat.  The idea being that the first "Managed" machine to suffer either "brought it in" or was "attacked" by an unmanaged machine.  If an unmanaged machine was the source, maybe the first managed machines to detect the threat would have something useful in its firewall log, event log or any other log around the detection time that could help pinpoint how it came to be on that machine.

 

Well I hope it helps someone but as I say, It's not an exact art this one I fear.

 

Regards,

Jak

 

 

WITH TMPT (ThreatName, OldDate) 
AS (
  SELECT ti.ThreatName, MIN(FirstDetectedAt) 
  FROM [Sophos Reporting Interface].vThreatInstances AS ti
  GROUP BY ti.ThreatName
  )

SELECT tif.ThreatName AS [Threat Name],
tif.FirstDetectedAt AS [First Detected At],
tif.InsertedAt AS [First Entered In Database],
tif.ThreatTypeName AS [Threat Type],
tif.ThreatSubTypeName AS [Threat Sub-Type],
c.Name AS [Computer Name],
c.Description AS [Computer Description],
c.Domain AS [Domain Name],
c.IPAddress AS [Computer IP],
tif.FullFilePath AS [Full File Path]
 FROM TMPT
 INNER JOIN [Sophos Reporting Interface].vThreatInstances AS tif ON tif.ThreatName = TMPT.ThreatName
   AND TMPT.OldDate = tif.FirstDetectedAt
   INNER JOIN [Sophos Reporting Interface].vComputerHostData AS c ON c.ComputerID = tif.ComputerID
   
-- WHERE tif.ThreatName = 'Mal/Conficker-A'
-- WHERE tif.ThreatTypeID = 1 --Viruses/spyware
-- WHERE tif.ThreatTypeID = 2 --Adware or PUA
-- WHERE tif.ThreatTypeID = 4 --Suspicious behavior
-- WHERE tif.ThreatTypeID = 5 --Suspicious file

ORDER BY tif.ThreatName ASC

 

I've added a few WHERE clauses to it that might be of interest.  Note: They are currently commented out.

 

 

Executive VIP
jak
Posts: 1,813
Registered: ‎Sat 19-Dec-2009

Re: Notification: Sophos Reporting Interface is now available

[ Edited ]

 

Hi,
Here is a pretty basic HTA for interfacing with the reporting interface if you don't have SQL Management Studio handy or any other means of running queries against the SOPHOS database.  It combines a few custom reports with just basic selects from the reporting interface views.
To use:
1. Download and Install the Reporting Interface into the SOPHOS database version you are using.
2. Paste the below code (I've had to paste it into 2 separate posts) into a text file and save with a HTA extension.
3. Run the HTA as a user with sufficient access to make queries on the Reporting interface views.  
Note: It uses Windows Authentication (logged on user).
I hope it is of use to someone.  If nothing else it provides a simple interface to see what's on offer via the reporting interface.
Regards,
Jak

 

 

<html>
<head>
<title>SEC Reporting Interface and Custom Reports</title>
<hta:application id="SOPHOSRI" windowstate="normal" scroll="yes"/>
<script language="VBScript">

Sub Window_onLoad
    window.resizeTo 1200,700
End Sub

' constants
const adOpenDynamic  = 2
const adLockReadOnly = 1

' language variables, changes as required, for all other text change HTML at bottom of script.
CHOOSE_A_REPORT 		= "Please choose a report."
ERROR_MSG_TYPE  		= "Error"
CUSTOM_QUERY_RESULTS 	= "Custom Query Results."
CHECK_DB_CONNECTION 	= "Please check database connection values. "
ERROR_CODE_MSG 			= "Error code: "
ERROR_DESC_MSG 			= "Error Description: "
SQL_ERROR_SYN_MSG 		= "Please check syntax of SQL command. "
SAVE_TO_FILE			= "Save to file"
COPY_TO_CLIPBOARD 		= "Copy to clipboard"
BACK_TO_TOP 			= "[Back to top]"
COULD_NOT_CREATE_FILE 	= "Could not create file. "

' global variables
dim strTitle, strHeaders, strSQL, objConn, objRsData

Function GetData(strReport)
    if strReport = "1" or strReport="2" then
        msgbox CHOOSE_A_REPORT, 0, ERROR_MSG_TYPE
        exit function
    end if

    SELECT case strReport
        case "CR1"
            strTitle    = "Source of infection"
            strHeaders  = "<th>Threat Name</th><th>First Detected At</th><th>First Entered In Database</th>" &_
                            "<th>Threat Type</th><th>Threat Sub-Type</th><th>Computer Name</th>" &_
                            "<th>Computer Description</th><th>Domain Name</th><th>Computer IP</th><th>Full File Path</th>"
            strSQL      = "WITH TMPT (ThreatName, OldDate) AS ( " &_
                            "  SELECT " & document.form1.topcount.value & " ti.ThreatName, MIN(FirstDetectedAt) " &_
                            "  FROM [Sophos Reporting Interface].vThreatInstances AS ti " &_
                            "  GROUP BY ti.ThreatName ) " &_
                            "SELECT tif.ThreatName AS [Threat Name], " &_
                            "tif.FirstDetectedAt AS [First Detected At]," &_
                            "tif.InsertedAt AS [First Entered In Database]," &_
                            "tif.ThreatTypeName AS [Threat Type]," &_
                            "tif.ThreatSubTypeName AS [Threat Sub-Type]," &_
                            "c.Name AS [Computer Name], " &_
                            "c.Description AS [Computer Description]," &_
                            "c.Domain AS [Domain Name], " &_
                            "c.IPAddress AS [Computer IP]," &_
                            "tif.FullFilePath AS [Full File Path] " &_
                            " FROM TMPT " &_
                            " INNER JOIN [Sophos Reporting Interface].vThreatInstances AS tif ON tif.ThreatName = TMPT.ThreatName" &_
                            "   AND TMPT.OldDate = tif.FirstDetectedAt " &_
                            "   INNER JOIN [Sophos Reporting Interface].vComputerHostData AS c ON c.ComputerID = tif.ComputerID " &_
                            "-- WHERE tif.ThreatName = 'Mal/Conficker-A' " &_
                            "-- WHERE tif.ThreatTypeID = 1 --Viruses/spyware " &_
                            "-- WHERE tif.ThreatTypeID = 2 --Adware or PUA " &_
                            "-- WHERE tif.ThreatTypeID = 4 --Suspicious behavior " &_
                            "-- WHERE tif.ThreatTypeID = 5 --Suspicious file " &_
                            "ORDER BY tif.ThreatName ASC" 
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 10  
            GetData2 document.form1.colcount.value 

        case "CR2"
            strTitle   = "Computer information"
            strHeaders = "<th>Computer Name</th><th>Domain Name</th><th>IP</th><th>LastRMSMessageTime</th><th>SEC Group</th><th>SAV Version</th>" &_
                            "<th>SAV Virus Data Version</th><th>SAV Engine Version</th>"
            strSQL = "SELECT " & document.form1.topcount.value & " c.Name AS [Computer Name], "&_
                        "c.Domain AS [Domain Name], "&_
                        "ISNULL(c.IPAddress,N'') AS [IP], "&_
                        "ISNULL(c.LastMessageReceivedTime,N'') AS [LastRMSMessageTime], "&_
                        "g.PathAndName AS [SEC Group], "&_
                        "ISNULL(p.SAVVersion,N'') AS [SAV Version], "&_
                        "ISNULL(p.VirusDataVersion,N'') AS [SAV Virus Data Version], "&_
                        "ISNULL(p.EngineVersion,N'') AS [SAV Engine Version] "&_
                        "FROM [Sophos Reporting Interface].[vComputerHostData] AS c  "&_
                        "LEFT OUTER JOIN [Sophos Reporting Interface].[vComputerPackageMapping] AS cpm "&_
                        "ON cpm.ComputerID = c.ComputerID "&_
                        "LEFT OUTER JOIN [Sophos Reporting Interface].[vPackageData] AS p "&_
                        "ON p.PackageID = cpm.PackageID "&_
                        "LEFT OUTER JOIN [Sophos Reporting Interface].[vComputerGroupMapping] AS cgm "&_
                        "ON cgm.ComputerID = c.ComputerID "&_
                        "LEFT OUTER JOIN [Sophos Reporting Interface].vGroupPathAndNameData AS g "&_
                        "ON g.GroupID = cgm.GroupID "&_
                        "ORDER BY c.Name ASC "
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 8  
            GetData2 document.form1.colcount.value 
 
        case "CR3"
            strTitle   = "Theats by count"
            strHeaders = "<th>Threat Name</th><th>Number</th>"
            strSQL = "SELECT " & document.form1.topcount.value & " [ThreatName], COUNT(*) AS [Number of Occurrences] " &_
                        "FROM [Sophos Reporting Interface].[vThreatInstances] " &_
                        "GROUP BY ThreatName " &_
                        "ORDER BY COUNT(*) DESC"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 2     
            GetData2 document.form1.colcount.value 

        case "CR4"
            strTitle   = "Theats count by computer"
            strHeaders = "<th>Computer Name</th><th>Domain Name</th><th>Number</th>" 
            strSQL = "SELECT " & document.form1.topcount.value & " c.Name AS [Computer Name], " &_
                        "c.Domain AS [Domain Name], " &_
                        "COUNT(*) AS [Threat Counts] " &_
                        "FROM [Sophos Reporting Interface].[vComputerHostData] AS c " &_
                        "INNER JOIN [Sophos Reporting Interface].[vThreatInstances] AS ti " &_
                        "ON ti.ComputerID = c.ComputerID " &_
                        "GROUP BY c.Name, c.Domain " &_
                        "ORDER BY COUNT(*) DESC"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 3     
            GetData2 document.form1.colcount.value 

        case "CR5"
            strTitle   = "Theats in the last 7 days"
            strHeaders = "<th>Computer Name</th><th>Domain Name</th><th>IP</th><th>Threat Type</th>" &_
                            "<th>Threat Sub Type</th><th>Threat Name</th>" &_
                            "<th>Full File Path</th><th>First Detect At</th><th>Last Message Received Time</th><th>SEC Group</th>" &_
                            "<th>SAV Version</th><th>SAV Virus Data Version</th><th>SAV Engine Version</th>" 
            strSQL = "SELECT " & document.form1.topcount.value & " " &_
                        "c.Name AS [Computer Name], " &_
                        "c.Domain AS [Domain Name], " &_
                        "ISNULL(c.IPAddress,N'') AS [IP], " &_ 
                        "ISNULL(t.ThreatTypeName,N'') AS [Threat Type], " &_
                        "ISNULL(t.ThreatSubTypeName,N'') AS [Threat Sub Type], " &_
                        "ISNULL(t.ThreatName,N'') AS [Threat Name], " &_
                        "ISNULL(t.FullFilePath,N'') AS [Full File Path], " &_
                        "ISNULL(t.FirstDetectedAt,N'') AS [First Detected At], " &_
                        "ISNULL(c.LastMessageReceivedTime,N'') AS [LastRMSMessageTime], " &_
                        "g.PathAndName AS [SEC Group], " &_
                        "ISNULL(p.SAVVersion,N'') AS [SAV Version], " &_
                        "ISNULL(p.VirusDataVersion,N'') AS [SAV Virus Data Version], " &_
                        "ISNULL(p.EngineVersion,N'') AS [SAV Engine Version] " &_
                        "FROM [Sophos Reporting Interface].[vComputerHostData] AS c  " &_
                        "LEFT OUTER JOIN [Sophos Reporting Interface].[vComputerPackageMapping] AS cpm " &_ 
                        "ON cpm.ComputerID = c.ComputerID " &_
                        "LEFT OUTER JOIN [Sophos Reporting Interface].[vPackageData] AS p " &_
                        "ON p.PackageID = cpm.PackageID " &_
                        "LEFT OUTER JOIN [Sophos Reporting Interface].[vComputerGroupMapping] AS cgm " &_
                        "ON cgm.ComputerID = c.ComputerID " &_
                        "LEFT OUTER JOIN [Sophos Reporting Interface].vGroupPathAndNameData AS g " &_
                        "ON g.GroupID = cgm.GroupID " &_
                        "LEFT OUTER JOIN [Sophos Reporting Interface].vThreatInstances as t " &_
                        "ON t.ComputerID = c.ComputerID " &_
                        "WHERE DATEDIFF( d, FirstDetectedAt, GETUTCDATE() ) < 7 " &_
                        "ORDER BY c.Name ASC"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 13     
            GetData2 document.form1.colcount.value 

        case "vComputerHostData"
            strTitle   = "vComputerHostData"
            strHeaders = "<th>ComputerID</th><th>Name</th><th>Domain</th><th>IPAddress</th><th>Description</th><th>LastMessageReceived</th>"
            strSQL     = "SELECT " & document.form1.topcount.value & " * FROM [Sophos Reporting Interface].[vComputerHostData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 6     
            GetData2 document.form1.colcount.value 

        case "vComputerPackageMapping"
            strTitle   = "vComputerPackageMapping"
            strHeaders = "<th>ComputerID</th><th>PackageID</th>"
            strSQL     = "SELECT " & document.form1.topcount.value & " * FROM [Sophos Reporting Interface].[vComputerPackageMapping]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 2  
            GetData2 document.form1.colcount.value 

        case "vComputerGroupMapping"
            strTitle   = "vComputerGroupMapping"
            strHeaders = "<th>GroupID</th><th>ComputerID</th>"
            strSQL     = "SELECT " & document.form1.topcount.value & " * FROM [Sophos Reporting Interface].[vComputerGroupMapping]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 2  
            GetData2 document.form1.colcount.value 

        case "vEventsApplicationControlData"
            strTitle   = "vEventsApplicationControlData"
            strHeaders = "<th>EventID</th><th>ComputerID</th><th>EventTime</th><th>EventTypeID</th>" &_
                            "<th>EventTypeName</th><th>Name</th><th>ReportingName</th><th>UserName</th>" &_
                            "<th>ActionID</th><th>ActionName</th><th>ScanTypeID</th><th>ScanTypeName</th>" &_
                            "<th>SubTypeID</th><th>SubTypeName</th><th>InsertedAt</th>"
            strSQL     = "SELECT " & document.form1.topcount.value & "  * FROM [Sophos Reporting Interface].[vEventsApplicationControlData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value = 15  
            GetData2 document.form1.colcount.value 

        case "vEventsCommonData"
            strTitle   = "vEventsCommonData"
            strHeaders = "<th>EventID</th><th>ComputerID</th><th>EventTime</th><th>EventTypeID</th>" &_
                            "<th>EventTypeName</th><th>Name</th><th>ReportingName</th><th>UserName</th>" &_
                            "<th>ActionID</th><th>ActionName</th><th>ScanTypeID</th><th>ScanTypeName</th>" &_
                            "<th>SubTypeID</th><th>SubTypeName</th><th>InsertedAt</th>"
            strSQL     = "SELECT " & document.form1.topcount.value & "  * FROM [Sophos Reporting Interface].[vEventsCommonData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 15  
            GetData2 document.form1.colcount.value 

        case "vEventsDataControlData"
            strTitle   = "vEventsDataControlData"
            strHeaders = "<th>EventID</th><th>ComputerID</th><th>EventTime</th><th>EventTypeID</th>" &_
                            "<th>EventTypeName</th><th>Name</th><th>ReportingName</th><th>UserName</th>" &_
                            "<th>ActionID</th><th>ActionName</th><th>InsertedAt</th><th>RuleName</th>" &_
                            "<th>TrueFileType</th><th>DestinationPath</th><th>DestinationTypeID</th>" &_
                            "<th>DestinationTypeName</th><th>SourcePath</th><th>FileName</th><th>DestinationValue</th>"
            strSQL     = "SELECT " & document.form1.topcount.value & " * FROM [Sophos Reporting Interface].[vEventsDataControlData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 19  
            GetData2 document.form1.colcount.value 

        case "vEventsDeviceControlData"
            strTitle   = "vEventsDeviceControlData"
            strHeaders = "<th>EventID</th><th>ComputerID</th><th>EventTime</th><th>EventTypeID</th>" &_
                            "<th>EventTypeName</th><th>Name</th><th>ReportingName</th><th>UserName</th>" &_
                            "<th>ActionID</th><th>ActionName</th><th>SubTypeID</th><th>SubTypeName</th>" &_
                            "<th>InsertedAt</th><th>DeviceTypeID</th><th>DestinationTypeName</th>" &_
                            "<th>Model</th><th>DeviceID</th>"
            strSQL     = "SELECT " & document.form1.topcount.value & "  * FROM [Sophos Reporting Interface].[vEventsDeviceControlData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 17  
        GetData2 document.form1.colcount.value 

 

 

Executive VIP
jak
Posts: 1,813
Registered: ‎Sat 19-Dec-2009
0

Re: Notification: Sophos Reporting Interface is now available

[ Edited ]

 

        case "vEventsFirewallData"
            strTitle   = "vEventsFirewallData"
            strHeaders = "<th>EventID</th><th>ComputerID</th><th>EventTime</th><th>EventTypeID</th>" &_
                            "<th>EventTypeName</th><th>Name</th><th>ReportingName</th><th>UserName</th>" &_
                            "<th>ActionID</th><th>ActionName</th><th>SubTypeID</th><th>SubTypeName</th>" &_
                            "<th>InsertedAt</th><th>Role</th><th>FileName</th><th>FilePath</th>" &_
                            "<th>FileVersion</th><th>FileChecksum</th><th>CommandLine</th><th>Session</th>" &_
                            "<th>Desktop</th><th>Location</th><th>ProtocolID</th><th>ProtocolText</th>" &_
                            "<th>DirectionID</th><th>DirectionText</th><th>LocalAddress</th><th>RemoteAddress</th>" &_
                            "<th>LocalPort</th><th>RemotePort</th>"
            strSQL     = "SELECT " & document.form1.topcount.value & "  * FROM [Sophos Reporting Interface].[vEventsFirewallData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 30  
            GetData2 document.form1.colcount.value 

        case "vEventsTamperProtectionData"
            strTitle   = "vEventsTamperProtectionData"
            strHeaders = "<th>EventID</th><th>ComputerID</th><th>EventTime</th><th>EventTypeID</th>" &_
                            "<th>EventTypeName</th><th>Name</th><th>ReportingName</th><th>UserName</th>" &_
                            "<th>ActionID</th><th>ActionName</th><th>SubTypeID</th><th>SubTypeName</th>" &_
                            "<th>InsertedAt</th><th>TargetTypeID</th><th>TargetTypeText</th><th>Target</th>" 

            strSQL     = "SELECT " & document.form1.topcount.value & "  * FROM [Sophos Reporting Interface].[vEventsTamperProtectionData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 16  
            GetData2 document.form1.colcount.value 
  
        case "vEventsWebData"
            strTitle   = "vEventsWebData"
            strHeaders = "<th>EventID</th><th>ComputerID</th><th>EventTime</th><th>EventTypeID</th>" &_
                            "<th>EventTypeName</th><th>Name</th><th>ReportingName</th><th>UserName</th>" &_
                            "<th>ActionID</th><th>ActionName</th><th>SubTypeID</th><th>SubTypeName</th>" &_
                            "<th>InsertedAt</th><th>RuleID</th><th>BlockedSite</th><th>ReferringURL</th>" 
            strSQL     = "SELECT " & document.form1.topcount.value & "  * FROM [Sophos Reporting Interface].[vEventsWebData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 16  
            GetData2 document.form1.colcount.value 

        case "vGroupPathAndNameData"
            strTitle   = "vGroupPathAndNameData"
            strHeaders = "<th>GroupID</th><th>PathAndName</th><th>Depth</th>" 
            strSQL     = "SELECT " & document.form1.topcount.value & " * FROM [Sophos Reporting Interface].[vGroupPathAndNameData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 3  
            GetData2 document.form1.colcount.value 
  
        case "vPackageData"
            strTitle   = "vPackageData"
            strHeaders = "<th>PackageID</th><th>Product</th><th>SAVVersion</th>"  &_
                            "<th>EngineVersion</th><th>VirusDataVersion</th><th>ExpiryTime</th>"  &_
                            "<th>NotificationTime</th><th>Expired</th>" 
            strSQL     = "SELECT " & document.form1.topcount.value & " * FROM [Sophos Reporting Interface].[vPackageData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 8  
            GetData2 document.form1.colcount.value 

        case "vPolicyComplianceData"
            strTitle   = "vPolicyComplianceData"
            strHeaders = "<th>ComputerID</th><th>PolicyTypeID</th><th>PolicyTypeName</th>"  &_
                            "<th>ComplianceID</th><th>ComplianceName</th>"
            strSQL     = "SELECT " & document.form1.topcount.value & " * FROM [Sophos Reporting Interface].[vPolicyComplianceData]"

            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 5  
            GetData2 document.form1.colcount.value 
  
        case "vThreatEventData"
            strTitle   = "vThreatEventData"
            strHeaders = "<th>EventID</th><th>ThreatID</th><th>EventTime</th>"  &_
                            "<th>ActionTakeID</th><th>ActionTakenName</th>" &_
                            "<th>UserName</th><th>ScannerTypeID</th>" &_
                            "<th>ScannerTypeName</th><th>StatusID</th>" &_
                            "<th>StatusName</th><th>InsertedAt</th>" 
            strSQL     = "SELECT " & document.form1.topcount.value & " * FROM [Sophos Reporting Interface].[vThreatEventData]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 11  
            GetData2 document.form1.colcount.value 

        case "vThreatInstances"
            strTitle   = "vThreatInstances"
            strHeaders = "<th>ThreatID</th><th>ComputerID</th><th>ThreatTypeID</th>"  &_
                            "<th>ThreatTypeName</th><th>ThreatSubTypeID</th>" &_
                            "<th>ThreatSubTypeName</th><th>Priority</th>" &_
                            "<th>ThreatName</th><th>FullFilePath</th>" &_
                            "<th>FileVersion</th><th>Checksum</th>" &_
                            "<th>FirstDetectedAt</th><th>InsertedAt</th>" 
            strSQL     = 	"SELECT " & document.form1.topcount.value & " * FROM [Sophos Reporting Interface].[vThreatInstances]"
            document.form1.sqltext.innertext  = strSQL 
            document.form1.headers.innertext  = strHeaders
            document.form1.colcount.value     = 13  
            GetData2 document.form1.colcount.value 
        case else
     end select
End Function

Function CustomQ(strCSql, strHeadersC, strCols)
    strTitle   = CUSTOM_QUERY_RESULTS 
    strSQL     = strCSql
    strHeaders = strHeadersC
    GetData2 strCols
End Function

Function SetupConnection()
    on error resume next
    dim strConnectionString 
    SetupConnection = false
    strConnectionString = "Driver={SQL Server};Server=" & document.form1.instancename.value &_
                            ";Database=" & document.form1.databasename.value &_
                            ";Trusted_Connection=yes;"
    Set objConn = CreateObject("ADODB.Connection")
    err.clear
    objConn.Open strConnectionString, adOpenDynamic, adLockReadOnly
    if err.number <> 0 then
        msgbox CHECK_DB_CONNECTION & vbcrlf & ERROR_CODE_MSG & err.number & vbcrlf & ERROR_DESC_MSG  & err.description , 0, ERROR_MSG_TYPE
        Set objConn = nothing
        SetupConnection = false
        exit function
    end if
    SetupConnection = true
End Function

Function GetData2(intColumnsf)
    on error resume next
    dim intRecordCount, str1, j
    intRecordCount = 0
    if SetupConnection() = false then
        exit function
    end if
    Set objRsData = objConn.Execute(strSQL)
    if err.number <> 0 then
        msgbox SQL_ERROR_SYN_MSG & vbcrlf & ERROR_CODE_MSG & err.number & vbcrlf & ERROR_DESC_MSG & err.description ,0, ERROR_MSG_TYPE
        exit function
    end if
    str1 =  "<p><span class=""repTitle"">" & strTitle & "</span><span align=""right"" id=""myContentCount""></span></p>" &_
                "<table border=""1"" class=""tresults"">" & strHeaders
    Do Until objRsData.EOF
        str1 = str1 &  "<tr>"
        for j = 0 to intColumnsf-1
            str1 = str1 & "<td>" & objRsData(j) & vbtab & "</td>"
        next
        str1 = str1 &  "</tr>"
        objRsData.moveNext
        intRecordCount = intRecordCount + 1
    loop
    str1 = str1 & "</ul></table>"
    myContent.innerHTML         = str1
    myFileSave.innerhtml        = "<br><input name=""strFileName"" type=""text"" size=""30"" value=""SECToolReportOutput.txt"">" &_
                                    "<input type=""button"" onclick=""OutputToFile(1)"" value="""&SAVE_TO_FILE&"""> " &_
                                    "<Input type=""button"" onclick=""ClipBoard()"" value="""&COPY_TO_CLIPBOARD&""">"
    myBackToTop.innerhtml       = "<p align=""right""><a href=""#TOP"">"&BACK_TO_TOP&"</a></p>" 
    myContentCount.innerhtml    = " (" & intRecordCount &" records)"
    set objRsData = nothing
    set objConn   = nothing
End Function

Function OutputToFile(strArea)
    on error resume next
    set objFso  = CreateObject("scripting.FileSystemObject")
    err.clear
    set objFile = objFso.createtextfile(document.form1.strFileName.value, TRUE, TRUE)
    if err.number <> 0 then
        msgbox COULD_NOT_CREATE_FILE & vbcrlf & ERROR_CODE_MSG & err.number & vbcrlf & ERROR_DESC_MSG & err.description , 0, ERROR_MSG_TYPE
        exit function
    end if
    err.clear
    select case strArea
        case 1
            objFile.WriteLine myContent.innerText
        case 2
            objFile.WriteLine sgr.innerText
    end select	
    objFile.close
    set objFso =  nothing
    set objFile = nothing
End Function
</script>
<style type="text/css">
    .repTitle{ font-family: Verdana; font-size: 14px; color:#fffffff; font-weight: bold; margin-top: 0px; }
    h3{ font-family: Verdana; font-size: 16px; color:#fffffff; margin-bottom: 2px; }
    fieldset{ padding: 8px; }
    p{ font-family: Verdana; font-size: 12px; color:#fffffff; }
    hr{ color: #000000; height:1px; } 
    table.tresults { font-family: Verdana; background-color: #6666666; border: 0px; border-spacing: 0px; }
    table.tresults td { background-color: #eeeeee; color: #000000; padding: 2px; border: 1px #ffffff solid; font-size: 10px; }
    table.tresults th { background-color: #000000; color: #ffffff; padding: 2px; text-align: left; border-bottom: 2px #ffffff solid; font-size: 10px; font-weight: bold; }
</style>
<script language="javascript" type="text/javascript"> 
    function ClipBoard() {
        document.form1.holdtext.innerText = myContent.innerText;
        Copied = document.form1.holdtext.createTextRange();
        Copied.execCommand("Copy");
    }
    function showMe (it, box) {
        var vis = (box.checked) ? "block" : "none";
        document.getElementById(it).style.display = vis;
    }
</script> 
</head>
<body bgcolor="#ffffff" text="#000000">
    <a name="TOP"></a>
    <h3>SEC Reporting Interface and Custom Reports</h3>
    <table width="100%" border="0">
        <tr>
            <td valign="top" width="50%">
                <form name="form1">
                    <fieldset>
                        <legend>Database Connection</legend>
                        <label for="si">Server and database instance name: E.g. SQLServerName\SOPHOS</label><br>
                        <input type="text" name="instancename" value=".\SOPHOS" size="30" id="si"><br>
                        <label for="db">Database:</label><br><select id="db" size="1" name="databasename">
                        <option value="SOPHOS4">SOPHOS4 (SEC 4)</option>
                        <option selected="selected" value="SOPHOS45">SOPHOS45 (SEC 4.5)</option>
                        <option value="SOPHOS47">SOPHOS47 (SEC 4.7)</option>
                        </select>
                    </fieldset>
            </td>
            <td valign="top">
                <fieldset>
                    <legend>Report Operations</legend>
                    <div id="myFileSave">The ability to save or copy a report will appear here after running a report.</div>
                </fieldset>
            </td>
        </tr>
    </table>
    <fieldset>
        <legend>Reports</legend>
        <select size="1" name="ReportChooser" id="rep">
            <option value="1">Choose Custom Report</option>
            <option value="CR1">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Source of infection</option> 
            <option value="CR2">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Computer information</option>  
            <option value="CR3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Threats by count</option>  
            <option value="CR4">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Threats count by computer</option>  
            <option value="CR5">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Threats in the last 7 days</option>  
            <option value="2">Choose Reporting Interface View</option>
            <option value="vComputerHostData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vComputerHostData</option>
            <option value="vComputerPackageMapping">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vComputerPackageMapping</option>
            <option value="vComputerGroupMapping">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vComputerGroupMapping</option>
            <option value="vEventsApplicationControlData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vEventsApplicationControlData</option>
            <option value="vEventsCommonData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vEventsCommonData</option>
            <option value="vEventsDataControlData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vEventsDataControlData</option>
            <option value="vEventsDeviceControlData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vEventsDeviceControlData</option>
            <option value="vEventsFirewallData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vEventsFirewallData</option>
            <option value="vEventsTamperProtectionData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vEventsTamperProtectionData</option>
            <option value="vEventsWebData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vEventsWebData</option>
            <option value="vGroupPathAndNameData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vGroupPathAndNameData</option>
            <option value="vPackageData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vPackageData</option>
            <option value="vPolicyComplianceData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vPolicyComplianceData</option>
            <option value="vThreatEventData">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vThreatEventData</option>
            <option value="vThreatInstances">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vThreatInstances</option>
        </select> 
        Records
        <select name="topcount" id="top">
            <option value="TOP 1">1</option>
            <option value="TOP 10">10</option>
            <option value="TOP 50">50</option>
            <option selected="selected" value="TOP 100">100</option>
            <option value="TOP 150">150</option>
            <option value="TOP 200">200</option>
            <option value="TOP 400">400</option>
            <option value="TOP 1000">1000</option>
            <option value="TOP ALL">All</option>
        </select>
        <input type="button" value="Run Report" onclick="GetData(document.form1.ReportChooser.Value)">
        <label for="re">SQL Editor</label>
        <input type="checkbox" name="c1" unchecked onclick="javascript&colon;showMe('div1', this)" id="re">
    </fieldset><br>
    <div id="div1" style="display:block; display:none;">
        <fieldset>
            <legend>SQL Editor</legend>SQL:<br>
            <textarea name="sqltext" cols="100" rows="5"></textarea><br>Table Headers:<br>
            <textarea name="headers" cols="100" rows="5"></textarea><br>Number of columns:<br>
            <input type="text" value="" name="colcount" size="2">
            <input type="button" value="Query!" onclick="CustomQ document.form1.sqltext.innertext, document.form1.headers.innertext, document.form1.colcount.value">
        </fieldset>
    </div>
    <fieldset>
        <legend>Output</legend>
        <div id="myContent"></div>
    </fieldset>
    <div id="myBackToTop"></div>
    <textarea id="holdtext" STYLE="display:none;"></textarea>
    </form>
</body>
</html>

 

 

Executive VIP
jak
Posts: 1,813
Registered: ‎Sat 19-Dec-2009

Re: Notification: Sophos Reporting Interface is now available

I've just tried copying and pasting the above into a new file and found it errors due to the line:

 

 <input type="checkbox" name="c1" unchecked onclick="javascript......

 

The colon after the javascript was escaped, this should be edited once saved to be a :

 

Jak

 

 

Moderator
sandy
Posts: 1,033
Registered: ‎Mon 16-Nov-2009
0

Re: Notification: Sophos Reporting Interface is now available

Keep 'em coming :-)


Sandy.

Communities Moderator, Sophos
Knowledgebase  |  @SophosSupport  |  Video tutorials
If a post solves your question use the Accept as Solution button and award kudos.
Occasional Visitor
Roel
Posts: 1
Registered: ‎Mon 06-Jun-2011
0

Re: Sophos Reporting Interface

Hello, >for more information and a link to the download page, go to http://www.sophos.com/security/sophoslabs/reporting-interface.html The page you refer to is very informational. Alas, I cannot seem to find the download link as mentioned in the post Can someone please post a download link? Thanks and regards Roel