Add context to the Sophos Endpoint Health Status report with XDR

BIG thanks to RaviSoni for all the hard work on the detail in this query.

You can use the query below to get more context on the health status of Windows machines via Endpoint Live Discover. e.g. which area is causing a bad health (Service or Threat) and if service, which services are problematic.

--Sophos Endpoint Health Status Check EDR Query

SELECT
  (SELECT
    CASE
      WHEN data = 1 THEN 'GOOD ✅'
      WHEN data = 3 THEN 'BAD ❌ - Isolated'
    END Satus
  FROM
    registry
  WHERE key = "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sophos\Health\Status\" AND name = 'admin') AS IsolateStatus,

  (SELECT
    CASE
      WHEN data = 1 THEN 'GOOD ✅'
      WHEN data = 2 THEN 'SUSPICIOUS ⚠️️'
      WHEN data = 3 THEN 'BAD ❌'
    END Satus
  FROM
    registry
  WHERE key = "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sophos\Health\Status\" AND name = 'service') AS ServiceStatus,

  (SELECT
    CASE
      WHEN data = 1 THEN 'GOOD ✅'
      WHEN data = 2 THEN 'SUSPICIOUS ⚠️' || CHAR(10) || CAST((SELECT CAST(GROUP_CONCAT((json_extract(raw,'$.threatName') || ' --detected-- ' || json_extract(raw,'$.location') || rtrim(REPLACE(ltrim(json_extract(raw, '$.paths'),'["\\\\?\\'),'\\',"\"),'"]')),' -> ' ||CHAR(10)) AS TEXT) AS "Threat Details" FROM sophos_events_summary WHERE id IN
      (SELECT regex_match(line,'(\{.*?\})',0) AS "EventID" FROM grep WHERE pattern LIKE 'Processing event id:' AND path = "C:\ProgramData\Sophos\Health\Logs\Health.log"
      AND regex_match(line,'(\{.*?\})',0) NOT NULL ORDER BY regex_match(line,'(.*Z)',0) DESC LIMIT 5)) AS TEXT)
      WHEN data = 3 THEN 'BAD ❌' || CHAR(10) || CAST((SELECT CAST(GROUP_CONCAT((json_extract(raw,'$.threatName') || ' --detected-- ' || json_extract(raw,'$.location') || rtrim(REPLACE(ltrim(json_extract(raw, '$.paths'),'["\\\\?\\'),'\\',"\"),'"]')),' -> ' ||CHAR(10)) AS TEXT) AS "Threat Details" FROM sophos_events_summary WHERE id IN
      (SELECT regex_match(line,'(\{.*?\})',0) AS "EventID" FROM grep WHERE pattern LIKE 'Processing event id:' AND path = "C:\ProgramData\Sophos\Health\Logs\Health.log"
      AND regex_match(line,'(\{.*?\})',0) NOT NULL ORDER BY regex_match(line,'(.*Z)',0) DESC LIMIT 5)) AS TEXT)
    END Satus
  FROM
    registry
  WHERE key = "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sophos\Health\Status\" AND name = 'threat') AS ThreatStatus,

  (SELECT
    CASE
      WHEN (SELECT
              CAST(group_concat(name, CHAR(10)) AS TEXT) AS "bad services"
            FROM
              registry
            WHERE
              key = "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sophos\Health\Status\"
            AND name LIKE 'service.%' AND data IN (1,2)) IS NULL
      THEN 'NONE ✅'
      ELSE (SELECT
              CAST(group_concat(name, CHAR(10)) AS TEXT) AS "bad services"
            FROM
              registry
            WHERE
              key = "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sophos\Health\Status\"
            AND name LIKE 'service.%' AND data IN (1,2))
    END Status) AS BadServices,

  (SELECT
    CASE
      WHEN data = 1 THEN 'GOOD ✅'
      WHEN data = 2 THEN 'SUSPICIOUS ⚠️'
      WHEN data = 3 THEN 'BAD ❌'
    END Satus
  FROM
    registry
  WHERE key = "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sophos\Health\Status\" AND name = 'health') AS OverallHealthStatus