Thread Info
  • State Not Answered
  • Locked Locked
  • Replies 18 replies
  • Subscribers 1 subscriber
  • Views 77848 views
  • Users 0 members are here
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Nice Sophos DB query's

Placebo

I though it would be nice to share some of the query's I made to collect data from the Sophos 4.5 database.

Web content scanner
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 AS FirstDetectedAt
FROM ComputersAndDeletedComputers AS tb1, Threats AS tb2
WHERE tb2.FullFilePath LIKE 'h__%'
AND tb2.ComputerID = tb1.ID)
UNION
(SELECT tb1.name AS Computer, tb2.ThreatName, tb2.FullFilePath, tb2.FirstDetectedAt AS FirstDetectedAt
FROM ComputersAndDeletedComputers AS tb1, ThreatsArchive AS tb2
WHERE tb2.FullFilePath LIKE 'h__%'
AND tb2.ComputerID = tb1.ID)
ORDER BY FirstDetectedAt DESC
Example Result:
PC1000 - Mal/Badsrc-C - h__p://evilwebsite.com - 18-7-2010 23:29

If you have some query's please share.

If you are a database guru and have comment on the way I constucted my query please let me know :smileyhappy:

:4050


This thread was automatically locked due to age.
Parents
  • 0

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

    :4979
Reply
  • 0

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

    :4979
Children
No Data