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.
Nominating a forum post submits a request to create a new Knowledge Article based on the forum post topic. Please ensure your nomination includes a solution within the reply.
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`
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,
Hello,
We are still looking for someone to help you.
We will come back to you ASAP.
Regards,
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`
Hello,
Thanks a lot for sharing it with us!!
Regards,
Select Forum Responses to become Knowledge Articles!
Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.
User | Count |
---|---|
1696 | |
1091 | |
752 | |
446 | |
228 |
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.
Copyright 2024 Fortinet, Inc. All Rights Reserved.