FortiAnalyzer dataset query questions

[kimeeeed]

Hi, I'm using fortianlyzer and have a question during dataset creation for report output.

I wrote the query below to create a forticlient uuid for the logged-in vpn user and it works fine.
Below is an example of a table made of query statements and queries.

- select user, fctuid from $log where $filter and ( ( ( lower(logid) = lower('0107045124'))))

Here, the user name is different, and I want to put a condition so that only the same line with the fctuid value is output, but it doesn't work well 

GROUP BY fctuid
HAVING COUNT (DISTINCT user) > 1 I tried adding this section but it doesn't work well.

[Anthony_E]

Hello,

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,

[Anthony_E]

Hello,

We are still looking for someone to help you.

We will come back to you ASAP.

Regards,

[Anthony_E]

Hi @vraev,

Could you please have a look?

[kimeeeed · Accepted solution]

hello, @Anthony_E 

I solved this problem with a sub query. Please refer to the query below

SELECT
from_itime(itime) AS itime,
`user`,
`fctuid`
FROM
$log
WHERE
$filter
AND (((lower(logid) = lower('0107045124'))))
AND `fctuid` IN (
SELECT `fctuid`
FROM $log
WHERE $filter
AND (((lower(logid) = lower('0107045124'))))
GROUP BY `fctuid`
HAVING COUNT(DISTINCT `user`) > 1
)
ORDER BY
`id`,
`itime`

[Anthony_E]

Hello,

Thanks a lot for sharing it with us!!

Regards,