Live Discover Query - identify devices where services could be an issue

 REVIEWED by Sophos 

One possibility is to simply query the "services" table for service status, for example:

select s.display_name, s.status from services as s where (s.display_name like 'Sophos%' or s.display_name like 'HitmanPro%') and s.status <> 'RUNNING' ;

Of course, this only has the details for installed services.  Another option would be to gather the details from Sophos Health registry keys as this component (if running of course) knows what services should be installed as well and therefore missing. It also monitors for important Sophos processes which aren't necessarily services, for example:

select r.Name as 'Service',
case data
when 0 then 'Running'
when 1 then 'Stopped'
when 2 then 'Missing'
else 'unknown'
end as 'ServiceState From Health'
from registry as r
where r.key like 'HKEY_LOCAL_MACHINE\SOFTWARE\%\Sophos\Health\Status' 
and r.path like '%service.%'
and r.data <> 0

I'm sure there are no guarantees about the future of this registry key and the values under it but it shows how flexible the solution is for getting data in various ways.

Edit: Updated the "where" clause to be a "like" to cover 32 and 64-bit computers.