Thread Info
  • State Not Answered
  • Locked Locked
  • Replies 18 replies
  • Subscribers 1 subscriber
  • Views 77837 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

    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.

    :4052
Reply
  • 0

    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.

    :4052
Children
No Data