Skip to main content
HRCA
HRCA
New Member
February 27, 2025
Solved

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

  • February 27, 2025
  • 6 replies
  • 2212 views

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

Best answer by HRCA

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`

6 replies

Stephen_G
Moderator
Stephen_G
Moderator
March 2, 2025

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 - Fortinet Community Team
Stephen_G
Moderator
Stephen_G
Moderator
March 4, 2025

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 - Fortinet Community Team
Stephen_G
Moderator
Stephen_G
Moderator
March 7, 2025

Hi again,

 

Is this article any help?

Stephen_G - Fortinet Community Team
    HRCA
    HRCAAuthor
    New Member
    March 7, 2025

    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
    HRCAAuthorAnswer
    New Member
    March 12, 2025

    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
      Staff
      czamudio
      Staff
      March 27, 2025

      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
      Staff
      farhanahmed
      Staff
      April 2, 2025

      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.

      Terms & ConditionsAccessibility statement