How to export a threat history report directly from the Sophos core database

  • Article ID: 111117
  • Updated: 14 Jan 2014

This article explains how to create an example threat history report.

Warning:  Do not run this script on a production SQL server without fully testing first.

First seen in

Enterprise Console 4.0.0

Example script

Note:

  • The script linked above was accurate at the time of Enterprise Console version 4 and has been tested and still works with version 5.1.
  • Check the Sophos database names with article 17323.

Active Threats

select dbo.ComputersAndDeletedComputers.Name As 'Computer Name',
dbo.Threats.ThreatName As 'Threat Name',
dbo.Threats.FullFilePath As 'Location of Threat',
dbo.Threats.FirstDetectedAt As 'Detected Time',
dbo.Threats.ActionSubmittedAt As 'Actioned Time',
'Action Taken' = Case
when dbo.ThreatEvents.ActionTaken=100 then 'Unknown'
when dbo.ThreatEvents.ActionTaken=101 then 'None'
when dbo.ThreatEvents.ActionTaken=102 then 'Renamed'
when dbo.ThreatEvents.ActionTaken=103 then 'Deleted'
when dbo.ThreatEvents.ActionTaken=104 then 'Shred'
when dbo.ThreatEvents.ActionTaken=105 then 'Moved'
when dbo.ThreatEvents.ActionTaken=106 then 'Copied'
when dbo.ThreatEvents.ActionTaken=107 then 'Attachment Deleted'
when dbo.ThreatEvents.ActionTaken=108 then 'Attactment Quarantined'
when dbo.ThreatEvents.ActionTaken=109 then 'Disinfected'
when dbo.ThreatEvents.ActionTaken=110 then 'Message Deleted'
when dbo.ThreatEvents.ActionTaken=111 then 'Message Quarantined'
when dbo.ThreatEvents.ActionTaken=112 then 'Authorised'
when dbo.ThreatEvents.ActionTaken=113 then 'Removed'
when dbo.ThreatEvents.ActionTaken=114 then 'Partially Removed'
when dbo.ThreatEvents.ActionTaken=115 then 'Cleared by Administrator'
when dbo.ThreatEvents.ActionTaken=116 then 'Blocked'
when dbo.ThreatEvents.ActionTaken=117 then 'No longer present'
when dbo.ThreatEvents.ActionTaken=118 then 'Removed from Quarantine'
Else 'Action not known' End,
dbo.Threats.DealtWithAt As 'Dealt with Time',
dbo.ThreatEvents.UserName As 'Username', CAST(IPADDRESS / 256 / 256 / 256 % 256 AS VARCHAR) + '.' + CAST(IPADDRESS / 256 / 256 % 256 AS VARCHAR) + '.' + CAST(IPADDRESS / 256 % 256 AS VARCHAR) + '.' + CAST(IPADDRESS % 256 AS VARCHAR) AS IPAddress,
'Cleanup Status' = Case
when dbo.Threats.Status=0 then 'None'
when dbo.Threats.Status=50 then 'Resolved'
when dbo.Threats.Status=100 then 'Threat type not cleanable'
when dbo.Threats.Status=200 then 'Not Cleanable'
when dbo.Threats.Status=250 then 'Full Scan required'
when dbo.Threats.Status=300 then 'Cleanable'
when dbo.Threats.Status=400 then 'Cleanup in Progress'
when dbo.Threats.Status=500 then 'Restart required'
when dbo.Threats.Status=600 then 'Cleanup timed out'
when dbo.Threats.Status=700 then 'Cleanup failed'
end from dbo.Threats (nolock)
inner join dbo.ThreatEvents (nolock)
on dbo.Threats.ThreatInstanceID = dbo.ThreatEvents.ThreatInstanceID
inner join dbo.ComputersAndDeletedComputers (nolock)
on dbo.Threats.ComputerID= dbo.ComputersAndDeletedComputers.ID
Order by dbo.Threats.FirstDetectedAt

Archived Threats

select dbo.ComputersAndDeletedComputers.Name As 'Computer Name',
dbo.ThreatsArchive.ThreatName As 'Threat Name',
dbo.ThreatsArchive.FullFilePath As 'Location of Threat',
dbo.ThreatsArchive.FirstDetectedAt As 'Detected Time',
dbo.ThreatsArchive.ActionSubmittedAt As 'Actioned Time',
'Action Taken' = Case when dbo.ThreatEvents.ActionTaken=100 then 'Unknown'
when dbo.ThreatEvents.ActionTaken=101 then 'None'
when dbo.ThreatEvents.ActionTaken=102 then 'Renamed'
when dbo.ThreatEvents.ActionTaken=103 then 'Deleted'
when dbo.ThreatEvents.ActionTaken=104 then 'Shred'
when dbo.ThreatEvents.ActionTaken=105 then 'Moved'
when dbo.ThreatEvents.ActionTaken=106 then 'Copied'
when dbo.ThreatEvents.ActionTaken=107 then 'Attachment Deleted'
when dbo.ThreatEvents.ActionTaken=108 then 'Attactment Quarantined'
when dbo.ThreatEvents.ActionTaken=109 then 'Disinfected'
when dbo.ThreatEvents.ActionTaken=110 then 'Message Deleted'
when dbo.ThreatEvents.ActionTaken=111 then 'Message Quarantined'
when dbo.ThreatEvents.ActionTaken=112 then 'Authorised'
when dbo.ThreatEvents.ActionTaken=113 then 'Removed'
when dbo.ThreatEvents.ActionTaken=114 then 'Partially Removed'
when dbo.ThreatEvents.ActionTaken=115 then 'Cleared by Administrator'
when dbo.ThreatEvents.ActionTaken=116 then 'Blocked'
when dbo.ThreatEvents.ActionTaken=117 then 'No longer present'
when dbo.ThreatEvents.ActionTaken=118 then 'Removed from Quarantine'
Else 'Action not known' End,
dbo.ThreatsArchive.DealtWithAt As 'Dealt with Time',
dbo.ThreatEvents.UserName As 'Username', CAST(IPADDRESS / 256 / 256 / 256 % 256 AS VARCHAR) + '.' + CAST(IPADDRESS / 256 / 256 % 256 AS VARCHAR) + '.' + CAST(IPADDRESS / 256 % 256 AS VARCHAR) + '.' + CAST(IPADDRESS % 256 AS VARCHAR) AS IPAddress,
'Cleanup Status' = Case when dbo.ThreatsArchive.Status=0 then 'None'
when dbo.ThreatsArchive.Status=50 then 'Resolved'
when dbo.ThreatsArchive.Status=100 then 'Threat type not cleanable'
when dbo.ThreatsArchive.Status=200 then 'Not Cleanable'
when dbo.ThreatsArchive.Status=250 then 'Full Scan required'
when dbo.ThreatsArchive.Status=300 then 'Cleanable'
when dbo.ThreatsArchive.Status=400 then 'Cleanup in Progress'
when dbo.ThreatsArchive.Status=500 then 'Restart required'
when dbo.ThreatsArchive.Status=600 then 'Cleanup timed out'
when dbo.ThreatsArchive.Status=700 then 'Cleanup failed'
end from dbo.ThreatsArchive (nolock)
inner join dbo.ThreatEvents (nolock)
on dbo.ThreatsArchive.ThreatInstanceID = dbo.ThreatEvents.ThreatInstanceID
inner join dbo.ComputersAndDeletedComputers (nolock)
on dbo.ThreatsArchive.ComputerID= dbo.ComputersAndDeletedComputers.ID
Order by dbo.ThreatsArchive.FirstDetectedAt

Example output

 

 
If you need more information or guidance, then please contact technical support.

Rate this article

Very poor Excellent

Comments