FortiAnalyzer Dataset

Hi everyone,

I've been working on a dataset to extract the number of FortiGates by model that experienced a conserve mode event. The query works, but it seems like the DISTINCT function isn’t actually returning only the unique entries.

For example, I only have one FortiGate-40F-3G4G, but the query returns more than one device. As shown in the attached image, during the period when I ran the report, this same device triggered four conserve mode events, and the query counted all of them instead of just once.

I've tried everything and still can't figure it out — lmao.

Here's what I'm trying to achieve:

• Get the list of unique devnames that triggered conserve mode
• Identify the model of each device
• Count how many unique devices per model

Any help would be much appreciated!

Here's my query:

SELECT
t1.platform AS tipo_dispositivo,
COUNT(DISTINCT t2.devname) AS total_dispositivos
FROM
$func-fgt-inventory AS t1
JOIN (
SELECT devname
FROM $log
WHERE $filter
AND LOWER(TRIM(msg)) LIKE '%kernel enters memory conserve mode%'
) AS t2
ON LOWER(TRIM(t1.devname)) = LOWER(TRIM(t2.devname))
GROUP BY
tipo_dispositivo
ORDER BY
total_dispositivos DESC