- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
FortiAnalyzer dataset query questions
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.
Solved! Go to Solution.
- Labels:
-
FortiAnalyzer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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`
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
We are still looking for someone to help you.
We will come back to you ASAP.
Regards,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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`
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
Thanks a lot for sharing it with us!!
Regards,
![](/skins/images/03B6F9D09B0B73D4E0068FD5D5412A2D/responsive_peak/images/icon_anonymous_message.png)