How to filter LAST(Event Receive Time) in Display Conditions?

[Akila2]

Hi,

I am working on a query to track when a device last sent events.

I want to list devices where the last log was received more than 5 hours ago.
I need a condition like this:
LAST(Event Receive Time) < Current Time - 5h

I’ve grouped results by:
LAST(Event Receive Time)
Reporting Device

FortiSIEM

[cdurkin_FTNT]

If you are using ClickHouse as the Event Database, this is easily performed via a SQL Query.

Out the box there is a query named "Existing Devices not reporting today", and the modified query below should be good for 5 hours , assuming that the device has reported data in the last 7 days.

WITH reporting_devices_last5 AS
(
    SELECT DISTINCT reptDevName
    FROM fsiem.events
    WHERE phRecvTime >= (now() - INTERVAL 5 HOUR)
      AND phEventCategory IN (0,4,6)
)
SELECT
    reptDevIpAddrV4,
    reptDevName,
    reptVendor,
    reptModel,
    max(phRecvTime) AS last_seen
FROM fsiem.events
WHERE phRecvTime >= (now() - INTERVAL 7 DAY)  -- still looking back 7 days for context
  AND phRecvTime < (now() - INTERVAL 5 HOUR)  -- but excluding last 5 hours
  AND phEventCategory IN (0,4,6)
  AND reptDevName GLOBAL NOT IN (
        SELECT reptDevName
        FROM reporting_devices_last5
  )
GROUP BY ALL
ORDER BY reptDevName ASC
SETTINGS transform_null_in = 1;

[Akila2]

Thanks a lot for the detailed query!

I should have clarified earlier, I am on FortiSIEM 7.2 and using EventDB, not ClickHouse.