Outstanding Alerts on the Dashboard

Hi,

When you say "Dashboard", I assume you are talking about the alert appearing in the computer list against the client?  The dashboard is really just the top summary section you can expand and collapse.

The alert will appear in the list view against a computer in response to the threat ID entry in the computersanddeletedcomputers table:

If you run the SQL command:

select lastthreatinstanceid

from ComputersAndDeletedComputers

where name = 'computername'

Note: Replace [computername] with the name of the machine in question .  This I assume will point to a threat id in the Threats table?  Note: There is a Threats table and a ThreatsArchive table the ones in Threats are typically outstanding, they get moved across into ThreatsArchive once they are no longer outstanding.

Running:
select * from threats as t with (nolock)

inner join computersanddeletedcomputers as c with (nolock)

on c.id = t.computerid

where name ='computername'

and

t.outstanding = 1

Will list you the outstading threats for the machine, again replace [computername] with the name of the machine in question

If you find the threat in the threats table you could as a last resort mark it as outstanding = 0 with a SQL command:

update threats set outstanding = 0 where threatinstanceid = NUMBER

where "NUMBER" is the threat id returned in the above query.

As the pointer in the computersanddeletedcomputers table is still there would will still get the alert graphic, to clear that you could then run:

update ComputersAndDeletedComputers set lastthreatinstanceid = null

To run the SQL commands above I would suggest downloading and installing SQL Server Management Studio and execute the commands using that against the SOPHOSX database.  I would also recommend takiing a backup of the database before modifying anything.  If you can't get the tool.  There is a tool called backupdb.bat in the DB directory under the Enterprise Console directory,  that will create you a backup.  You can run the above SQL queries on the command line also using SQLCMD.exe.  To do so:

sqlcmd -E -S .\sophos -d SOPHOS47 -Q "Enter the above query here"

Where:

.\sophos represents a local SOPHOS SQL instance.
SOPHOS47 is the name of the SOPHOS database for SEC 4.7.  If you have 4.5 it would be SOPHOS45, likewise SEC 4 would be SOPHOS4 .

I hope that helps.  If you don't feel confident updating the database please just post the results from the first 2 select queries here.

Regards,

Jak 

Hi,

When you say "Dashboard", I assume you are talking about the alert appearing in the computer list against the client?  The dashboard is really just the top summary section you can expand and collapse.

The alert will appear in the list view against a computer in response to the threat ID entry in the computersanddeletedcomputers table:

If you run the SQL command:

select lastthreatinstanceid

from ComputersAndDeletedComputers

where name = 'computername'

Note: Replace [computername] with the name of the machine in question .  This I assume will point to a threat id in the Threats table?  Note: There is a Threats table and a ThreatsArchive table the ones in Threats are typically outstanding, they get moved across into ThreatsArchive once they are no longer outstanding.

Running:
select * from threats as t with (nolock)

inner join computersanddeletedcomputers as c with (nolock)

on c.id = t.computerid

where name ='computername'

and

t.outstanding = 1

Will list you the outstading threats for the machine, again replace [computername] with the name of the machine in question

If you find the threat in the threats table you could as a last resort mark it as outstanding = 0 with a SQL command:

update threats set outstanding = 0 where threatinstanceid = NUMBER

where "NUMBER" is the threat id returned in the above query.

As the pointer in the computersanddeletedcomputers table is still there would will still get the alert graphic, to clear that you could then run:

update ComputersAndDeletedComputers set lastthreatinstanceid = null

To run the SQL commands above I would suggest downloading and installing SQL Server Management Studio and execute the commands using that against the SOPHOSX database.  I would also recommend takiing a backup of the database before modifying anything.  If you can't get the tool.  There is a tool called backupdb.bat in the DB directory under the Enterprise Console directory,  that will create you a backup.  You can run the above SQL queries on the command line also using SQLCMD.exe.  To do so:

sqlcmd -E -S .\sophos -d SOPHOS47 -Q "Enter the above query here"

Where:

.\sophos represents a local SOPHOS SQL instance.
SOPHOS47 is the name of the SOPHOS database for SEC 4.7.  If you have 4.5 it would be SOPHOS45, likewise SEC 4 would be SOPHOS4 .

I hope that helps.  If you don't feel confident updating the database please just post the results from the first 2 select queries here.

Regards,

Jak