Sophos Endpoint Report for a Specific Group only in Enterprise Console

Hi,

What information are you looking to gather as there are a few options.

Direct SQL queries to the database:

/search?q= 11901

Note: as you're on SEC 4.5, the database name is SOPHOS45, so the same as the query in that post.

If you upgrade to a later version of SEC, 5.1 is the latest, you can select a group, and copy and paste will copy all the information in the current tab, you can then paste this into Excel for example.

There is also the reporting interface as mentioned here:

/search?q= 8285

Hope these help.

Regards,

Jak

The items that I want to see in the report are computer name, AV version, OS, AV and HIPS policy, Up to Date, etc.

Hi,

How about connecting to SQL using Excel (/search?q= 25159) and run SQL such as:

SELECT 
c.ComputerName, 
CAST(c.IPAddress / 256 / 256 / 256 % 256 as varchar) + '.'  + 
		              CAST(c.IPAddress / 256 / 256 % 256 as varchar) + '.' +
		              CAST(c.IPAddress / 256 % 256 as varchar) + '.' +
                   	  CAST(c.IPAddress % 256 as varchar) AS [IP],
CASE c.OS
                WHEN 1 THEN 'Windows 95' 
                WHEN 2 THEN 'Windows 98' 
                WHEN 3 THEN 'Windows Me' 
                WHEN 4 THEN 'Windows NT' 
                WHEN 5 THEN 'Windows NT Server' 
                WHEN 6 THEN 'Windows 2000' 
			    WHEN 7 THEN 'Windows 2000 Server' 
                WHEN 8 THEN 'Windows XP' 
                WHEN 9 THEN 'Windows 2003' 
                WHEN 10 THEN 'Mac OS9' 
                WHEN 11 THEN 'Mac OSX' 
                WHEN 12 THEN 'Windows 95/98/Me' 
                WHEN 13 THEN 'Mac OSX or Unix' 
                WHEN 14 THEN 'Unknown' 
                WHEN 15 THEN 'Windows workstation' 
                WHEN 16 THEN 'Windows server'   
			    WHEN 17 THEN 'Vista' 
                WHEN 18 THEN 'Windows 2008' 
                WHEN 19 THEN 'Windows XP 64-Bit' 
                WHEN 20 THEN 'Linux' 	
                WHEN 21 THEN 'Unix'
                WHEN 33 THEN 'Windows 7'
                WHEN 34 THEN 'Windows 2008 R2'
                WHEN 35 THEN 'Windows 8'
                When 37 THEN 'Windows Server 2011'                 		   
			   ELSE 'Unknown' 
			 END as OS,                   	  
c.ServicePack,                   	 
c.LastMessageTime, 
c.SAVOnAccess, 
c.Connected, 
c.IdeNumber, 
gp.PathAndName,
p.SAVVersion,
p.EngineVersion,
p.VirusDataVersion,

case 
  when   p.expired = 0 
     and p.expirytime = '9999-01-01 00:00:00.000' 
     and p.rolloutnumber = 99999999
      then 'Yes'
  when     
   p.expired = 1
     and p.rolloutnumber = 99999999
     and p.ExpiryTime > dateadd(hh, -1, GETUTCDATE())
      then 'Yes'
    when   p.expired = 1 
     and p.rolloutnumber = 99999999 then 'Not since: ' + (CONVERT(VARCHAR, (dateadd(hh, 2, p.expirytime)), 120)) 
  else 'Unknown'
end as [UpToDate]
FROM dbo.computerlistdata2 c 
left JOIN Packages as p on p.id = c.packageid
left JOIN GroupPathAndNameTable() as gp on gp.GroupID = c.groupid
WHERE 
c.managed = 1
--and gp.PathAndName = 'Group1\Group2'
order by [UpToDate] DESC

You may wish to add other columns from the view ComputerListData2.

Note: In the query I have: 

dateadd(hh, 2, p.expirytime)

You may need to change the 2 to be the number of hours from UTC time + 1for your system.  If you compare in SEC with the results from the SQL you can work out the number to add/take away.

I have also commented out (note the -- comment) the where clause for the group, but you can add that in with a group you have:

--and gp.PathAndName = 'Group1\Group2'

EDIT: I've added to the up-to-date state the same 1hr grace period as SEC.

Hope it helps.  

Regards,

Jak