QRadar: Configuring the Sophos database on a dedicated SQL server

Answer

Before you begin: This is a non-standard configuration where an SQL server is being used to support the custom view instead of Sophos Enterprise Console.

In this case, to configure the Sophos Enterprise Console, the SQL command from the QRadar DSM guide needs to be run on the dedicated SQL server instead of Sophos Enterprise Console CLI.

1. Log in to the SQL database server.
   
2. At command-line, type the following command to create a custom view for your Sophos database to support QRadar:
   CREATE VIEW threats_view AS SELECT t.ThreatInstanceID, t.ThreatType, t.FirstDetectedAt, c.Name, c.LastLoggedOnUser, c.IPAddress, c.DomainName, c.OperatingSystem, c.ServicePack, t.ThreatSubType, t.Priority, t.ThreatLocalID, t.ThreatLocalIDSource, t.ThreatName, t.FullFilePathCheckSum, t.FullFilePath, t.FileNameOffset, t.FileVersion, t.CheckSum, t.ActionSubmittedAt, t.DealtWithAt, t.CleanUpable, t.IsFragment, t.IsRebootRequired, t.Outstanding, t.Status, InsertedAt
FROM <Database Name>.dbo.ThreatInstancesAll t, <Database Name>.dbo.Computers c
WHERE t.ComputerID = c.ID;
   Note:
• Where <Database Name> is the name of the Sophos database.
• The database name must not contain any spaces.