Skip to main content
kimeeeed
kimeeeed
Visitor III
October 26, 2024
Solved

FortiAnalyzer dataset query questions

  • October 26, 2024
  • 5 replies
  • 1980 views

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'))))

abf1b07a-11dc-4dc6-9e6a-487dc839f207.png

 

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.

Best answer by kimeeeed

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`

5 replies

Anthony_E
Staff
Anthony_E
Staff
October 29, 2024

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
Staff
Anthony_E
Staff
November 4, 2024

Hello,

 

We are still looking for someone to help you.

We will come back to you ASAP.


Regards,

Anthony_E
Staff
Anthony_E
Staff
November 8, 2024

Hi @vraev,

 

Could you please have a look?

kimeeeed
kimeeeedAuthorAnswer
Visitor III
November 8, 2024

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
    Staff
    Anthony_E
    Staff
    November 8, 2024

    Hello,

     

    Thanks a lot for sharing it with us!!

     

    Regards,

    Terms & ConditionsCookie settingsAccessibility statement