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

Sophos Reporting

Sys_Engineer

Hey,

We've got a big database of clients reporting back to our main Enterprise Console. I need to be able to generate a report which will give me a list of computers/servers which have any of the following conditions:

1. Failed to update for a certain amount of time

2. Have a virus/spyware threat

3. Not complying with policy

The top one is the most important. It would also be good if this can be run only on a certain range of PCs, ie. Running Server 2008 etc. Using Crystal Reports is fine but I'm just not sure which tables/fields I need to select.

Thank you,

Gregor

:17877


This thread was automatically locked due to age.
  • 0

    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

    :17883
Unfiltered HTML