Sophos – Datenbank exportieren und in MySQL einlesen

Feb 27, 2020

—

von

Irgendwann kommt die Zeit in der man erkennt, dass man der Internetcommunity erworbenes Wissen beisteuern sollte. Deshalb möchte ich euch meinen Weg zeigen wie man die Sophos Datenbank exportieren und zum Beispiel in Mysql einlesen kann. Generell ist es nicht notwendig die Daten in eine MySQL Datenbank zu exportieren, wenn man sich mit dem MS SQL-Server auskennt. Doch wenn die eigene Anwendung mit den Daten aus dem SOPHOS System gefüttert werden will, wäre es über MySQL eine Möglichkeit.

Inhalt:

Inhaltsübersicht Verbergen

1. USB-Geräte-Ausnahmen aus der SOPHOS-Datenbank auslesen und in CSV exportieren

2. Viren- und Spyware Meldungen aus der SOPHOS-Datenbank auslesen und in CSV exportieren

3. SOPHOS Audit-Daten exportieren

4. SOPHOS Client/Computer/Agent Daten in CSV exportieren

5. das komplette Powershell-Script zum Auslesen der SOPHOS Datenbank

6. Datenbank-Dump für MySQL und Maria DB

7. weitere Beiträge zum Thema SOPHOS Endpoint Security

Folgend findet ihr ein paar Powershellzeilen um die SOPHOS Datenbank in eine CSV zu exportieren. Die CSV könnt ihr dann recht simpel weiterverarbeiten. Ich persönlich importiere die täglich in meine MySQL Datenbank um die Daten dort aktuell zu halten und meine benötigten Reports tagaktuell zu generieren.

Zum Ablauf, das Script generiert den SQL-Befehl und speichert den in einer Datei ab, die im darauffolgenden Schritt an die sqlcmd.exe übergeben wird.

Die erstellten CSV-Dateien werden dann per 7Zip komprimiert und anschließend an einen Server per SMB kopiert.

start-process -FilePath ‘C:\Program Files\7-Zip\7z.exe’ -ArgumentList a -mx=9 -mmt=off c:\temp\SOPHOS_DB_export.7z c:\temp\SOPHOS_*.csv -NoNewWindow -Wait

USB-Geräte-Ausnahmen aus der SOPHOS-Datenbank auslesen und in CSV exportieren

Um die eingetragene USB-Geräte-ausnahmen auszulesen, muss die Microsoft SQL Datenbank der „dbo.ExemptedDevices“ der SOPHOS Instanz angezapft werden. In meinem Fall hieß die Datenbank „SOPHOS550“. Der Instanzname kann aber variieren, je nach dem mit welcher Versionsnummer ihr den Sophos Server damals installiert habt bzw. er nun geupdated wurde!

$sql_commands="use SOPHOS550
SELECT ID,DeviceTypeID,Comment,DeviceDescription,DeviceModel,DeviceInstance,DefaultExemptionLevel FROM dbo.ExemptedDevices ORDER BY ID DESC 
go"

out-file -filepath $sql_commandfile -inputobject $sql_commands

#fetch sql data, create csv_file
start-process -FilePath sqlcmd.exe -ArgumentList "-E -S .\sophos -i temp_sql_cmd.sql -s`";`" -w 700 -W -o $resultfile_csv1" -NoNewWindow -Wait

Viren- und Spyware Meldungen aus der SOPHOS-Datenbank auslesen und in CSV exportieren

Auch die Meldungen zu aktuellen Viren und Spywaremeldungen können direkt aus der Datenbank ausgelesen werden. Dazu einfach die Tabelle „dbo.Threats“ anzapfen und über einen JOIN mit dbo.ComputersandDeletedComputers verknüpfen um die dazugehörigen Computernamen zu erhalten.

$sql_commands="use SOPHOS550 
Select ComputerID, Name, DomainName, Description, IPAddressText, ThreatType, ThreatName, FullFilePath, FirstDetectedAt, ActionSubmittedAt, CleanUpable, Outstanding,Status,Managed,Connected, SAVonAccess, LastScanDateTime, LastLoggedOnUser,LastScanName 
FROM dbo.Threats inner join dbo.ComputersandDeletedComputers on Threats.ComputerID = ComputersandDeletedComputers.ID 
go"

out-file -filepath $sql_commandfile -inputobject $sql_commands

#fetch sql data, create csv_file
start-process -FilePath sqlcmd.exe -ArgumentList "-E -S .\sophos -i temp_sql_cmd.sql -s`";`" -w 700 -W -o $resultfile_csv2" -NoNewWindow -Wait

SOPHOS Audit-Daten exportieren

Wer in seinem SOPHOS Server „AUDIT“ aktiviert hat um die Aktivitäten der SOPHOS-Administratoren und deren Konfiguration in der Sophos Enterprise Console zu überwachen, kann auch hier die Datenbank bequem exportieren.

$sql_commands="use SOPHOS550 
SELECT [EventId],[Timestamp],[Action],[TargetType]
      ,[TargetSubType],[TargetName]
      ,[ParameterType],[ParameterValue],[Result],[UserName]
      ,[HostName],[HostIPAddress],[ActionId],[TargetTypeId]
      ,[TargetSubTypeId],[ParameterTypeId],[SubEstateId],[ResultId],[UserSid]
  FROM [SophosSecurity].[Reports].[vAuditEventsAll]
go"

out-file -filepath $sql_commandfile -inputobject $sql_commands

#fetch sql data, create csv_file
start-process -FilePath sqlcmd.exe -ArgumentList "-E -S .\sophos -i temp_sql_cmd.sql -s`"!`" -w 700 -W -o $resultfile_csv3" -NoNewWindow -Wait

SOPHOS Client/Computer/Agent Daten in CSV exportieren

Noch viel nützlicher finde ich den Export der Sophos Agentdaten. Dazu muss die MS SQL-Tabelle „[dbo].[ComputersAndDeletedComputers]“ der Sophos Instanz abgefragt werden.

$sql_commands="use SOPHOS550 
SELECT  [ID],[Name],[DomainName],[Description]
,[Managed],[Connected],[SAVOnAccess],[PackageID]
,[LastScanDateTime],[InstallPending],[InstallInProgress]
,[IPAddress],[IPAddressText],[ServicePack],[QuarantineCount],[PrimaryCIDLoc]
,[LastLoggedOnUser],[InstallFailureReason],[LastScanName],[InstallTime]
,[LastMessageTime],[Deleted],[DNSName],[AppControlOnAccess],[HipsRuleVersion],[HipsConfigVersion],[PolicyManagementType]
,[DataControlIsActive],[DeviceControlIsActive],[TamperProtectionIsActive],[InsertedAt],[IdentityTag]
FROM [dbo].[ComputersAndDeletedComputers]
go"

out-file -filepath $sql_commandfile -inputobject $sql_commands

#fetch sql data, create csv_file
start-process -FilePath sqlcmd.exe -ArgumentList "-E -S .\sophos -i temp_sql_cmd.sql -s`";`" -w 700 -W -o $resultfile_csv4" -NoNewWindow -Wait

Wer die Daten auch in eine MySQL oder MARIA Datenbank imoprtieren möchte, kann sich gerne dem folgenden SQL-Zeilen zum Anlegen der benötigten Tabellen bedienen.

das komplette Powershell-Script zum Auslesen der SOPHOS Datenbank

# PS-Script to export SOPHOS exempted usb-devices
#
# Requirements:
# - powershell 2.0
# - installed 7-Zip / $7zip_proc="C:\Program Files\7-Zip\7z.exe"

####
#### Configuration
####

$location_name=$env:USERDOMAIN
$scriptname="SOPHOS_exempted_USB-Devices"
$transfer_path="\\my_fileserver.local\sophos"
$debug=0

$sql_SophosDB="SOPHOS550"
$sql_commandfile="temp_sql_cmd.sql"
$sql_commands="empty"


$7zip_proc="C:\Program Files\7-Zip\7z.exe"
$curDate = Get-Date -format "yyyy-dd-MM_HHmm"
$7z_transferfile="c:\temp\"+$scriptname+"_"+$env:COMPUTERNAME+"_"+$curDate+".7z"

$resultfile_csv1="c:\temp\SOPHOS_devc_exemptionlist-"+$location_name+".csv"
$resultfile_csv2="c:\temp\SOPHOS_alerts-"+$location_name+".csv"
$resultfile_csv3="c:\temp\SOPHOS_audit-"+$location_name+".csv"
$resultfile_csv4="c:\temp\SOPHOS_clients-"+$location_name+".csv"

####
#### export exempted devices
####
$sql_commands="use "+$sql_SophosDB+"
SELECT ID,DeviceTypeID,Comment,DeviceDescription,DeviceModel,DeviceInstance,DefaultExemptionLevel FROM dbo.ExemptedDevices ORDER BY ID DESC
go"
out-file -filepath $sql_commandfile -inputobject $sql_commands

#fetch sql data, create csv_file
start-process -FilePath sqlcmd.exe -ArgumentList "-E -S .\sophos -i temp_sql_cmd.sql -s`";`" -w 700 -W -o $resultfile_csv1" -NoNewWindow -Wait



####
#### export Virus and Spyware alerts
####
$sql_commands="use "+$sql_SophosDB+"
Select ComputerID, Name, DomainName, Description, IPAddressText, ThreatType, ThreatName, FullFilePath, FirstDetectedAt, ActionSubmittedAt, CleanUpable, Outstanding,Status,Managed,Connected, SAVonAccess, LastScanDateTime, LastLoggedOnUser,LastScanName 
FROM dbo.Threats inner join dbo.ComputersandDeletedComputers on Threats.ComputerID = ComputersandDeletedComputers.ID 
go"
out-file -filepath $sql_commandfile -inputobject $sql_commands

#fetch sql data, create csv_file
start-process -FilePath sqlcmd.exe -ArgumentList "-E -S .\sophos -i temp_sql_cmd.sql -s`";`" -w 700 -W -o $resultfile_csv2" -NoNewWindow -Wait



####
#### export audit data
####
$sql_commands="use "+$sql_SophosDB+"
SELECT [EventId],[Timestamp],[Action],[TargetType]
      ,[TargetSubType],[TargetName]
      ,[ParameterType],[ParameterValue],[Result],[UserName]
      ,[HostName],[HostIPAddress],[ActionId],[TargetTypeId]
      ,[TargetSubTypeId],[ParameterTypeId],[SubEstateId],[ResultId],[UserSid]
  FROM [SophosSecurity].[Reports].[vAuditEventsAll]
go"
out-file -filepath $sql_commandfile -inputobject $sql_commands

#fetch sql data, create csv_file
start-process -FilePath sqlcmd.exe -ArgumentList "-E -S .\sophos -i temp_sql_cmd.sql -s`"!`" -w 700 -W -o $resultfile_csv3" -NoNewWindow -Wait

####
#### export clients data
####
$sql_commands="use "+$sql_SophosDB+"
SELECT  [ID],[Name],[DomainName],[Description]
,[Managed],[Connected],[SAVOnAccess],[PackageID]
,[LastScanDateTime],[InstallPending],[InstallInProgress]
,[IPAddress],[IPAddressText],[ServicePack],[QuarantineCount],[PrimaryCIDLoc]
,[LastLoggedOnUser],[InstallFailureReason],[LastScanName],[InstallTime]
,[LastMessageTime],[Deleted],[DNSName],[AppControlOnAccess],[HipsRuleVersion],[HipsConfigVersion],[PolicyManagementType]
,[DataControlIsActive],[DeviceControlIsActive],[TamperProtectionIsActive],[InsertedAt],[IdentityTag]
FROM [dbo].[ComputersAndDeletedComputers]
go"
out-file -filepath $sql_commandfile -inputobject $sql_commands

#fetch sql data, create csv_file
start-process -FilePath sqlcmd.exe -ArgumentList "-E -S .\sophos -i temp_sql_cmd.sql -s`";`" -w 700 -W -o $resultfile_csv4" -NoNewWindow -Wait

### configure 7Zip
    if(Test-Path $7zip_proc){ 
        

        $7zip_arg="a -mx=9 -mmt=off "+$7z_transferfile+" c:\temp\SOPHOS_*.csv"
        $7zip_error_file=$auditfile_path+"\7zip_error.txt"
        $7zip_output_file=$auditfile_path+"\7zip_output.txt"

        write-host "- 7zip: create archive "$7z_transferfile"..."
        write-host "- 7zip: "$7zip_proc" "$7zip_arg
        start-process -FilePath $7zip_proc -ArgumentList $7zip_arg -RedirectStandardError $7zip_error_file -RedirectStandardOutput $7zip_output_file -NoNewWindow -Wait

    }else{
        write-host "- ERROR: 7zip was not found"
    }


    ### transfer data to reporting-server
    if(Test-Path $7z_transferfile){
        write-host "- copy "$7z_transferfile" to "$transfer_path
        copy-Item -Path $7z_transferfile -Destination $transfer_path

    }else{
        write-host "- ERROR: "$7z_transferfile" not copied!"

    }

#cleanup
if($debug -eq 0){
    if ((Test-Path $resultfile_csv1) ) {Remove-Item($resultfile_csv1)}
    if ((Test-Path $resultfile_csv2) ) {Remove-Item($resultfile_csv2)}
    if ((Test-Path $resultfile_csv3) ) {Remove-Item($resultfile_csv3)}
    if ((Test-Path $resultfile_csv4) ) {Remove-Item($resultfile_csv4)}
    if ((Test-Path $7z_transferfile) ) {Remove-Item($7z_transferfile)}    
    if ((Test-Path $sql_commandfile) ) {Remove-Item($sql_commandfile)}
}

Datenbank-Dump für MySQL und Maria DB

Folgend eine Dumpfile falls ihr die Sophos-Daten in eure eigene Datenbank einlesen und auswerten wollt, was durchaus Sinn macht.

-- phpMyAdmin SQL Dump
-- version 4.6.5.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------

--
-- Table structure for table `sophos_alerts`
--

CREATE TABLE `sophos_alerts` (
  `ID` int(10) NOT NULL,
  `fs_location_id` int(5) NOT NULL,
  `ComputerID` int(10) NOT NULL,
  `ComputerName` varchar(50) NOT NULL,
  `DomainName` varchar(30) NOT NULL,
  `ComputerDescription` text NOT NULL,
  `IPAddress` varchar(16) NOT NULL,
  `ThreatType` int(2) NOT NULL,
  `ThreatName` varchar(50) NOT NULL,
  `FullFilePath` text NOT NULL,
  `FirstDetectedAt` datetime DEFAULT '0000-00-00 00:00:00',
  `ActionSubmittedAt` datetime DEFAULT '0000-00-00 00:00:00',
  `CleanUpable` int(1) NOT NULL,
  `Outstanding` int(1) NOT NULL,
  `Status` int(3) NOT NULL,
  `Managed` int(1) NOT NULL,
  `Connected` int(1) NOT NULL,
  `SAVonAccess` int(11) NOT NULL,
  `LastScanDateTime` datetime DEFAULT '0000-00-00 00:00:00',
  `LastLoggedOnUser` varchar(50) NOT NULL,
  `LastScanName` varchar(50) NOT NULL,
  `CreateDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ModifiedDate` datetime DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `sophos_alerts`
--

INSERT INTO `sophos_alerts` (`ID`, `fs_location_id`, `ComputerID`, `ComputerName`, `DomainName`, `ComputerDescription`, `IPAddress`, `ThreatType`, `ThreatName`, `FullFilePath`, `FirstDetectedAt`, `ActionSubmittedAt`, `CleanUpable`, `Outstanding`, `Status`, `Managed`, `Connected`, `SAVonAccess`, `LastScanDateTime`, `LastLoggedOnUser`, `LastScanName`, `CreateDate`, `ModifiedDate`) VALUES
(1, 1, 4105, 'fileserver', 'Standort1', '', '192.168.16.80', 1, 'Mal/Generic-S', '//./GLOBALROOT/Device/HarddiskVolumeShadowCopy261/shares/Hausmeister/09 SOFTWARE f?r PC/Installations-CD Akkumaster C5/interface/examples/logger/logger.exe', '2016-10-25 00:43:23', '1900-01-01 00:00:00', 0, 1, 200, 1, 1, 1, '2016-09-07 16:37:31', 'administrator', 'Scan my computer\r', '2017-01-24 11:04:46', '0000-00-00 00:00:00');


ALTER TABLE `sophos_alerts` ADD PRIMARY KEY (`ID`);

--
-- AUTO_INCREMENT for table `sophos_alerts`
--
ALTER TABLE `sophos_alerts`
  MODIFY `ID` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1874;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


--
-- Table structure for table `sophos_exemptions`
--

CREATE TABLE `sophos_exemptions` (
  `ID` int(50) NOT NULL,
  `s_ID` int(10) DEFAULT NULL,
  `s_Comment` text,
  `s_DeviceDescription` text,
  `s_DeviceModel` varchar(200) DEFAULT NULL,
  `s_DeviceInstance` varchar(200) DEFAULT NULL,
  `s_DefaultExemptionLevel` int(2) DEFAULT NULL,
  `fs_location_id` int(5) DEFAULT NULL,
  `CreateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `LastFoundDate` datetime DEFAULT '0000-00-00 00:00:00',
  `approvalstate` int(1) DEFAULT '0' COMMENT '0=unkown, 1=appr, 2=not_appr.',
  `approvalstate_userid` int(10) DEFAULT NULL,
  `approvalstate_date` datetime DEFAULT NULL,
  `approvalstate_comment` text,
  `approval_document` varchar(200) DEFAULT NULL,
  `request_userid` int(5) NOT NULL DEFAULT '0',
  `request_date` datetime DEFAULT NULL,
  `request_comment` text,
  `request_id` int(50) NOT NULL DEFAULT '0',
  `request_assigned` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `sophos_exemptions`
--

INSERT INTO `sophos_exemptions` (`ID`, `s_ID`, `s_Comment`, `s_DeviceDescription`, `s_DeviceModel`, `s_DeviceInstance`, `s_DefaultExemptionLevel`, `fs_location_id`, `CreateDate`, `LastFoundDate`, `approvalstate`, `approvalstate_userid`, `approvalstate_date`, `approvalstate_comment`, `approval_document`, `request_userid`, `request_date`, `request_comment`, `request_id`, `request_assigned`) VALUES
(1, 1, '', 'WD My Passport 0821 USB Device', 'USBSTORDiskWD______My_Passport_08201012', '', NULL, 4, '2017-01-23 10:06:08', '2019-03-05 09:56:02', 0, NULL, NULL, NULL, NULL, 0, '0000-00-00 00:00:00', '', 0, 0);



ALTER TABLE `sophos_exemptions`  ADD PRIMARY KEY (`ID`);
ALTER TABLE `sophos_exemptions` ADD FULLTEXT KEY `FULLTEXT` (`s_Comment`,`s_DeviceDescription`,`approvalstate_comment`,`request_comment`);
ALTER TABLE `sophos_exemptions` ADD FULLTEXT KEY `FULLTEXT2` (`request_comment`,`approvalstate_comment`);

--
-- AUTO_INCREMENT for table `sophos_exemptions`
--
ALTER TABLE `sophos_exemptions` MODIFY `ID` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Kommentare

Schreibe einen Kommentar Antworten abbrechen

Du musst angemeldet sein, um einen Kommentar abzugeben.