Nice Sophos DB query's

Live URL Filtering (Web protection)

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

Query:
SELECT tb1.name AS Computer, tb2.EventTime, tb2.UserName, tb3.BlockedSite, tb3.ReferringURL
FROM ComputersAndDeletedComputers AS tb1, Events As tb2, Events_WebFilteringData AS tb3
WHERE tb2.EventType = 6
AND tb2.ComputerID = tb1.ID
AND tb3.EventID = tb2.ID
ORDER BY tb2.EventTime DESC

Example Result:

PC1000 - 15-7-2010 14:45 - Domain\username - www.sophostest.com - ReferringURL

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
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

Remarks:

The table Events has a collum Action. My guess is it's related to the fact if something was allowed, blocked, logged, enz. But I'm not sure. So I have not included it.

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,

Great idea for a thread!  I notice a couple of things.  

You could use the View ThreatInstancesAll which is a union of Threats and ThreatsArchive which will simplify your query.  You can still use the Outstanding flag in a where clause to filter accordingly.

You might want to use the function GetUTCDate() as all the timestamps are in UTC.

I would also suggest making queries with no lock hints to reduce the impact on any lengthy queries.  E.g.

select * from computersanddeletedcomputers with (nolock)

As soon as I think of any useful queries I'll post them here. 

Thanks,

Jak

Last full scan way back

Description:

Show all the computers that where connected to the management server in the last 2.5 hours and that had there last full scan more than 31 days ago.

Query:

SELECT name, LastScanDateTime, LastScanName, LastMessageTime
FROM ComputersAndDeletedComputers
WHERE LastScanDateTime <= GetUTCDate() - 31
AND LastMessageTime >= GetUTCDate() - 0.1
ORDER BY NAME ASC

Example Result:

PC1000 - 24-5-2010 1:50 - Scheduled scan - 22-7-2010 11:12

Remarks:

Usefull for initiating full scans from the management console for computers that missed all the scheduled scans because they are always of at the planned time for the scheduled scan.

Inproved version of the web content scanner query.

Web content scanner (v2)
Description:
Shows all the Internet Explorer BHO web content scanner detections in one list sorted by date of detection.
Query:
SELECT tb1.name As Computer, tb2.ThreatName, tb2.FullFilePath, tb2.FirstDetectedAt
FROM ComputersAndDeletedComputers AS tb1, ThreatInstancesAll AS tb2
WHERE tb2.FullFilePath LIKE 'h__%'
AND tb2.ComputerID = tb1.ID
ORDER BY tb2.FirstDetectedAt DESC
Example Result:
PC1000 - EICAR-AV-Test - h__p://www.eicar.org/download/eicar.com - 18-7-2010 23:29

Hi Jak,

Thanks for all your comments.
As you can see I have incorporated them in to the new full scan way back query and I made a new version of the Web content scanner query.

Placebo

HIPS detections with checksum

Description:

Shows all HIPS detections. Includes files version and checksum.

Query:

SELECT tb1.name As Computer, tb2.FirstDetectedAt, tb2.ThreatName, tb2.FullFilePath, tb2.FileVersion, tb2.CheckSum
FROM ComputersAndDeletedComputers AS tb1, ThreatInstancesAll AS tb2
WHERE tb2.ThreatType = 4
AND tb2.ComputerID = tb1.ID
ORDER BY tb2.FirstDetectedAt DESC

Example Result:

PC1000 - 21-7-2010 14:52 - HIPS/RegMod-013 - c:\WINDOWS\system32\drivers\xtouch.sys - 4.03.07.3203 built by: WinDDK - a607af8012bbd8ba1800d8f759f9b354

Remarks:

I find the checksum (md5 hash) useful for a quick check on Virustotal.

http://www.virustotal.com/buscaHash.html

Great thread Placebo keep them coming.

Suspicious file detections with checksum
Description:
Shows all suspicious file detections. Includes file version and checksum.
Query:
SELECT tb1.name As Computer, tb2.FirstDetectedAt, tb2.ThreatName, tb2.FullFilePath, tb2.FileVersion, tb2.CheckSum
FROM ComputersAndDeletedComputers AS tb1, ThreatInstancesAll AS tb2
WHERE tb2.ThreatType = 5
AND tb2.ComputerID = tb1.ID
ORDER BY tb2.FirstDetectedAt DESC
Example Result:
PC1000 - 21-7-2010 14:52 - Sus/AutoInf-A - E:\AutoRun.INF -  - 47c14b295ba92adac527a1fa7bd77cfc
Remarks:
I find the checksum (md5 hash) useful for a quick check on Virustotal.
http://www.virustotal.com/buscaHash.html

Thank you PlaceBo!

Very nice to share your DB queries. That is what I am missing among predefined reports in Sophos Console.

I suggest Sophos developers to add such queries into next version console. In present version there is no field e.g. FullFilePath.

Thanks again