FortiAnalyzer Dataset

[jptavaresss]

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

[Jean-Philippe_P]

Hello jptavaresss, 

Thank you for using the Community Forum. I will seek to get you an answer or help. We will reply to this thread with an update as soon as possible. 

Thanks, 

[zolepco1]

Syslog is straight text. There is no structure to it except what the endpoint creates.

Datasets are only for devices that send the right information for FortiAnalyzer, which is basically just FortiNet stuff.

If you want reports you have to export the syslog files and do everything yourself.