Under Review

Sophos Firewall most recent heartbeat status for devices

This statement will query the Firewall Datalake and provice a list of devices and their last reported Heartbeat status

-- Devices seen by Firewall with Heartbeat status
-- Variable $$Heartbeat Status$$    String

WITH Device_List AS (
   SELECT 
      reported_host AS Endpoint_Name, 
      MAX(reported_at) as Last_Seen
   FROM xgfw_data
   WHERE log_type = 'Heartbeat'
      AND log_component = 'Endpoint'
   GROUP BY reported_host
),
Endpoint_List AS (
   SELECT Endpoint_Name,
      (
         SELECT DISTINCT hb_status
         FROM xgfw_data
         WHERE log_type = 'Heartbeat'
            AND log_component = 'Endpoint'
            AND reported_host = Endpoint_Name
            AND reported_at = Last_Seen
      ) AS Heartbeat_Status,
      Last_Seen,
      (
         SELECT DISTINCT device_serial_id
         FROM xgfw_data
         WHERE log_type = 'Heartbeat'
            AND log_component = 'Endpoint'
            AND reported_host = Endpoint_Name
            AND reported_at = Last_Seen
      ) AS Firewall_ID
   FROM Device_List
)
SELECT * 
FROM Endpoint_List
WHERE Heartbeat_Status LIKE '$$Heartbeat Status$$'
ORDER BY Last_Seen DESC