SQL Query Request

There are many views and tables in the Sophos database that can be used for getting such information, but remember that some of them provide different subsets of information, and some may have permissions that prevent access by systems such as SQL Reporting Services.

For example, "ComputerListData2" will provide a list of current computers in the system - both managed and unmanaged. If you only want managed machines, you will need to check the managed bit. I cannot recall the reason, but I typically use the "Computers" view for most reporting requirements, which has never let me down yet.

I personally do not use the Sophos Reporting Interface views for computers (I actually use internal tables for most things), as it is missing far too much important information that for my company, is absolutely critical and mandatory for contractual requirements. In addition, the view "vComputerHostData" returns deleted, managed and unmanaged systems - but does not allow any way to filter deleted or unmanaged systems out. Due to the size of my environment, this results in a discrepancy of more than 8,000 systems between the tables!

Furthermore, watch out for some of the Reporting Interface views that are far too heavy in some cases - as multiple calls to various enumeration and conditional statements exist in some of them. "vEventsFirewallData" is a very, very heavy table if you have a large environment. We have over 140,000,000 rows of data in this table (growing by about 20,000 rows each day), and as the underlying table is not normalised, and the query is extremely heavy - it results in over 80% CPU usage on SQL server (a server with 24GB RAM and 16 CPUs) through a simple query for a particular application, which usually times out the query (5 minutes on our system) and sometimes cause a stack trace. During this time, all components of Sophos will see massive performance hit as a result. Just a warning to be aware of ;)

There are many views and tables in the Sophos database that can be used for getting such information, but remember that some of them provide different subsets of information, and some may have permissions that prevent access by systems such as SQL Reporting Services.

For example, "ComputerListData2" will provide a list of current computers in the system - both managed and unmanaged. If you only want managed machines, you will need to check the managed bit. I cannot recall the reason, but I typically use the "Computers" view for most reporting requirements, which has never let me down yet.

I personally do not use the Sophos Reporting Interface views for computers (I actually use internal tables for most things), as it is missing far too much important information that for my company, is absolutely critical and mandatory for contractual requirements. In addition, the view "vComputerHostData" returns deleted, managed and unmanaged systems - but does not allow any way to filter deleted or unmanaged systems out. Due to the size of my environment, this results in a discrepancy of more than 8,000 systems between the tables!

Furthermore, watch out for some of the Reporting Interface views that are far too heavy in some cases - as multiple calls to various enumeration and conditional statements exist in some of them. "vEventsFirewallData" is a very, very heavy table if you have a large environment. We have over 140,000,000 rows of data in this table (growing by about 20,000 rows each day), and as the underlying table is not normalised, and the query is extremely heavy - it results in over 80% CPU usage on SQL server (a server with 24GB RAM and 16 CPUs) through a simple query for a particular application, which usually times out the query (5 minutes on our system) and sometimes cause a stack trace. During this time, all components of Sophos will see massive performance hit as a result. Just a warning to be aware of ;)