FAZ 7.6.2 - VPN Report Broken After Upgrade (No Matching Log Data)

[HRCA]

Hey everyone

Thanks in advance for any help you can provide.

I recently upgraded my company's FortiAnalyzer (FAZ) to 7.6.2, and a custom VPN connection/disconnection report used by my manager has broken. While the report is custom, it was originally based on a Fortinet employee’s article. 

After logging a support case, I was informed that because this is a custom report, I need to resolve the issue myself.

What I’ve Found So Far

• I found a Reddit post where another user experienced similar issues after the FAZ backend switched from PostgreSQL to ClickHouse.
• I followed the Fortinet ClickHouse migration documentation and updated my SQL query accordingly.
• The data appears correctly when previewing the dataset, and it also shows up in chart previews.
• However, when I generate the report, it comes out blank with the message:
  :police_car_light:"No matching log data for this report"

My Current SQL Query

SELECT
`user` AS "User",
(
CASE
WHEN `action` = 'tunnel-up' THEN 'Connected'
ELSE 'Disconnected'
END
) AS "VPN Status",
$calendar_time(itime) AS `Time Stamp`
FROM
$log
WHERE
$filter
AND (
(`action` = 'tunnel-up')
OR (`action` = 'tunnel-down`)
)
GROUP BY
`Time Stamp`,
`action`,
`user`
ORDER BY
`user`,
`Time Stamp`

Questions for the Community

1. Has anyone else faced this issue after upgrading to FAZ 7.6.x?
2. Are there any known issues with ClickHouse affecting FAZ report generation?
3. Is there a workaround to get this report working again?

Any help or insight would be greatly appreciated!

Thanks
Heath

[Stephen_G]

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,

[Stephen_G]

Hi,

Sorry, we're still trying to get you an answer or reply. In the meantime, if anyone viewing this topic has a possible answer, your input is welcomed.

[Stephen_G]

Hi again,

Is this article any help?

[HRCA]

Thanks Stephen. As the report was working until the upgrade and reading through the article, I don't believe that is the issue. Maybe this will go unresolved until more people hit the issue on FAZ 7.6 🤷

[HRCA · Accepted solution]

Hey All

This has been solved with the below SQL query. All credit to farhanahmed 

Thanks 

Heath

SELECT
a.`user`,
a.login,
b.logout,
b.duration,
b.transferred_bytes
FROM
###(
SELECT
`user`,
min(from_dtime(dtime)) AS login,
tunnelid
FROM
$log-event
WHERE
action = 'tunnel-up'
and $filter
GROUP BY
`user`,
tunnelid
)### a,
###(
SELECT
max(from_dtime(dtime)) AS logout,
duration,
sum(sentbyte + rcvdbyte) AS transferred_bytes,
tunnelid
FROM
$log-event
WHERE
action = 'tunnel-down'
and $filter
GROUP BY
duration,
tunnelid
HAVING
sum(sentbyte + rcvdbyte) > 0
)### b
WHERE
a.tunnelid = b.tunnelid
ORDER BY
login,
`user`

[czamudio]

Hello

there is a bug with vpn logs report in 7.6.2, you will need to create a ticket so we can investigate the specifics and see if it match

[farhanahmed]

Thanks @HRCA for the mention. I am glad the solution I provided helped you resolve the issue.

SELECT
a.`user`,
a.login,
b.logout,
b.duration,
b.transferred_bytes
FROM
###(
SELECT
`user`,
min(from_dtime(dtime)) AS login,
tunnelid
FROM
$log-event
WHERE
action = 'tunnel-up'
and $filter
GROUP BY
`user`,
tunnelid
)### a,
###(
SELECT
max(from_dtime(dtime)) AS logout,
duration,
sum(sentbyte + rcvdbyte) AS transferred_bytes,
tunnelid
FROM
$log-event
WHERE
action = 'tunnel-down'
and $filter
GROUP BY
duration,
tunnelid
HAVING
sum(sentbyte + rcvdbyte) > 0
)### b
WHERE
a.tunnelid = b.tunnelid
ORDER BY
login,
`user`

its a bit more useful because it shows login, logout and duration of a user as well. You can then adjust the chart to change the format of the duration and bytes.