Nice Sophos DB query's

> If you feel the current reports in Enterprise Console are insufficient for your needs we encourage you to log them on

> the forum or notify Sophos technical support so we can integrate them into the product in the future.

Ok here we go:

1. I like the full path to everything (Viruses, Suspicious, HIPS, Controlled Applications) to be visible in the computer details screen.

2. I like to have a notification by email of a Web content scanner detection. Sophos arguments that this is not needed because the virus has never made it to the system. I want to know if something was blocked because maybe not everything was blocked. I have plenty of cases where Sophos blocked the PDF but the SWF used in the same attack was not detected and made it to the system.

3. I like to have a notification by email of a Web Protection (Live URL) detection. Same argumentation. If Sophos lists a C&C URL but does not detect my specific sample of the bot. Communication to the C&C is blocked but I also like the computer to be cleaned.

4. I like to know which user is logged on to the computer for Web content scanner, viruses, Suspicious and HIPS detections. This data is currently not available in the database.

5. I like to have a report like the  'Data control events' query.

6. A md5 checksum for ever item detected would also be very nice!. (see my remarks in the HIPS detections with checksum
and Suspicious file detections with checksum query's why.)

7. If a computer misses the Full scan (because it's off) I like the Full scan to run when the computer has been started.

With other word I want details. I want to use the reports for investigation. It's nice that I can present a generic report to my manager but I want more!

Device control detections
Description:
Shows all device control detections.
Query:
SELECT tb2.EventTime, tb1.Name AS Computer, tb2.UserName, tb3.DeviceDescription, tb3.DeviceModel
FROM ComputersAndDeletedComputers AS tb1, Events As tb2, Events_DeviceControlData AS tb3
WHERE tb2.EventType = 2
AND tb2.ComputerID = tb1.ID
AND tb3.EventID = tb2.ID
ORDER BY tb2.EventTime DESC
Example Result:
23-7-2010 13:53 - PC1000 - domain\user - SigmaTel USB-IrDA Dongle - USB\Vid_066f&Pid_4200&Rev_0008

Tamper protection events
Description:
Shows all tamper protection events.
Query:
SELECT tb2.EventTime, tb1.Name AS Computer, tb2.UserName, tb3.Target
FROM ComputersAndDeletedComputers AS tb1, Events AS tb2, Events_TamperProtectionData AS tb3
WHERE tb2.EventType = 5
AND tb2.ComputerID = tb1.ID
AND tb3.EventID = tb2.ID
ORDER BY tb2.EventTime DESC
Example Result:
29-10-2010 14:23 - PC1000 - domain\user - Sophos Anti-Virus

Looking for the following queries and the Sophos Reporting tool doesn't offer much. 

I can do a lot of the date and time calculations in Excel since I'm pivoting off the MSAccess Query.  Wondering if you could help with the tables/views.

1. Policies: Out of Date more than 7 days old – include the following items/fields:

1. Computer Name
2. User
3. Group
4. Tamper Protection Status

• Protection: Out of Date more than 7 days old – include the following items/fields:
  1. Computer Name
  2. User
  3. Policy Compliance
  4. Up To Date
• Errors: Out of date more than 7 days old – include the following items/fields:
  1. Computer Name
  2. User
  3. Alerts and Errors
  4. Scanning Errors
  5. Install Errors
  6. Update Errors
  7. Group

The database structure changed during the update from SEC 4.7 to 5.0.
Here is a SEC v5.0 query for Live URL Filtering (Web protection).

Live URL Filtering (Web protection)

Description:
Shows all the Web protection detections in one list sorted by date of detection.

Query:
SELECT tb2.EventTime, tb1.name AS Computer, tb2.UserName, tb2.Name As Reason, tb2.BlockedSite, tb2.ReferringURL
FROM ComputersAndDeletedComputers AS tb1, Events_Web AS tb2
WHERE tb2.ComputerID = tb1.ID
ORDER BY tb2.EventTime DESC

Example Result:
8-2-2012 11:40 - PC1000 - Domain\username - Mal/HTMLGen-A - www.sophostest.com - ReferringURL

And here is a new SEC v5.0 Data control events Query.
I have added the new filesize field!

Data control events

Description:
Shows all the Data Control events for the last 7 days sorted by date.

Query:
SELECT tb1.name AS Computer, tb2.EventTime, tb2.UserName, tb3.TrueFileType, tb3.SourcePath, tb3.DestinationPath, tb3.DestinationValue, tb3.FileSize
FROM ComputersAndDeletedComputers AS tb1, Events As tb2, Events_DataControlData AS tb3
WHERE tb2.EventType = 1
AND tb2.ComputerID = tb1.ID
AND tb3.EventID = tb2.ID
AND tb2.EventTime <= GETDATE() - 7
ORDER BY tb2.EventTime DESC

Example Result:
PC1000 - 15-7-2010 14:45 - Domain\username - Spreadsheet - c:\test.xls - e:\test.xls - removableStorage - 39936

Remarks:
If you want the data for more or less then 7 days edit the 7 (after the GETDATE part) into the amount of days you want.

Hi,

An idee to generate list of computer runing a defined controlled application ? (Example : Ultrasurf ...)

Thanks

---

RoulienDeLaVega wrote:

An idee to generate list of computer runing a defined controlled application ? (Example : Ultrasurf ...)

---

SELECT tb2.EventTime, tb1.Name AS Computer, tb2.UserName, tb2.Name As Application

FROM ComputersAndDeletedComputers AS tb1, Events As tb2

WHERE tb2.EventType = 3

AND tb2.ComputerID = tb1.ID

AND tb2.EventTime >= GetUTCDate() - 31

AND tb2.Name = 'PUT YOUR APP NAME HERE'

ORDER BY tb2.EventTime DESC