Sophos Reporting

Hi,

I guess you should ideally use the reporting interface for such things.  This post: /search?q= 8285  is a good start for that.  

Here is an example query for out of date machines.  It's constructed from the Stored Procedure (dbo.ComputerListOutOfDateGet ). This is called to get the Computer IDs that are out of date, maybe you can use that and adjust it for what you need.  I've added one join to it to get the group path.  Please check the view ComputerListData2 to see what other columns you might like to return.

SELECT c.ComputerName, 
	c.DomainName, 
	c.SAVVersion, 
	c.IDENumber, 
	c.PackageExpiryTime,
	c.PrimaryCIDLoc, 
	dbo.IPAddressText(c.IPAddress) as IP, 
	c.LastMessageTime, 
	gp.PathAndName
	    FROM dbo.ComputerListData2 AS c WITH (NOLOCK)
		    LEFT OUTER JOIN dbo.ComputerPolicyStates sav WITH (NOLOCK) ON sav.Type = 2 AND c.ComputerID = sav.ComputerID
		    LEFT OUTER JOIN dbo.Packages p WITH (NOLOCK) ON c.PackageID = p.ID
		    LEFT OUTER JOIN dbo.GroupPathAndNameTable() AS gp ON gp.GroupID = c.GroupID
		WHERE sav.ComputerID IS NOT NULL 
		      AND c.Managed = 1
		      AND ISNULL(p.ExpiryTime,0) < dateadd( hh, -1, GETUTCDATE() )
		      AND c.Connected = 1

 Please note: the Connected = 1 clause, this wil only consider machines that are, well, connected.

For the threat reports.  I would suggest possibly using a join between computerlistdata2 (note: that's quite an "expensive" view if you only want a a few of the computer information columns so maybe just the table ComputersAndDeletedComputers would do) and  the view ThreatInstancesAll.  That is another view that unions threats from the Threats and ThreatsArchive tables.  You can filter on Outstanding=1 to find only "live" threats.

For policy violation, have a look at the functions that start:

ComputerListWithPolicyViolation....

To filter on OS for example, you need to use a where clause on the OS column if using ComputerListData2.  The numbers represent the different OS I have a list somewhere.

Placebo put some queries here:

http://community.sophos.com/t5/Sophos-Endpoint-Security-and/Nice-Sophos-DB-query-s/td-p/4050

that might help you also.

Essentially all the data is there to get what you want. :)

Regards,

Jak

Hi,

I guess you should ideally use the reporting interface for such things.  This post: /search?q= 8285  is a good start for that.  

Here is an example query for out of date machines.  It's constructed from the Stored Procedure (dbo.ComputerListOutOfDateGet ). This is called to get the Computer IDs that are out of date, maybe you can use that and adjust it for what you need.  I've added one join to it to get the group path.  Please check the view ComputerListData2 to see what other columns you might like to return.

SELECT c.ComputerName, 
	c.DomainName, 
	c.SAVVersion, 
	c.IDENumber, 
	c.PackageExpiryTime,
	c.PrimaryCIDLoc, 
	dbo.IPAddressText(c.IPAddress) as IP, 
	c.LastMessageTime, 
	gp.PathAndName
	    FROM dbo.ComputerListData2 AS c WITH (NOLOCK)
		    LEFT OUTER JOIN dbo.ComputerPolicyStates sav WITH (NOLOCK) ON sav.Type = 2 AND c.ComputerID = sav.ComputerID
		    LEFT OUTER JOIN dbo.Packages p WITH (NOLOCK) ON c.PackageID = p.ID
		    LEFT OUTER JOIN dbo.GroupPathAndNameTable() AS gp ON gp.GroupID = c.GroupID
		WHERE sav.ComputerID IS NOT NULL 
		      AND c.Managed = 1
		      AND ISNULL(p.ExpiryTime,0) < dateadd( hh, -1, GETUTCDATE() )
		      AND c.Connected = 1

 Please note: the Connected = 1 clause, this wil only consider machines that are, well, connected.

For the threat reports.  I would suggest possibly using a join between computerlistdata2 (note: that's quite an "expensive" view if you only want a a few of the computer information columns so maybe just the table ComputersAndDeletedComputers would do) and  the view ThreatInstancesAll.  That is another view that unions threats from the Threats and ThreatsArchive tables.  You can filter on Outstanding=1 to find only "live" threats.

For policy violation, have a look at the functions that start:

ComputerListWithPolicyViolation....

To filter on OS for example, you need to use a where clause on the OS column if using ComputerListData2.  The numbers represent the different OS I have a list somewhere.

Placebo put some queries here:

http://community.sophos.com/t5/Sophos-Endpoint-Security-and/Nice-Sophos-DB-query-s/td-p/4050

that might help you also.

Essentially all the data is there to get what you want. :)

Regards,

Jak