Fortiview Server Error:DB:Excepetion:Column 'XXX' is not under aggregate function and not in GROUPBY

[pankaj_stl]

We are facing issue after upgrading the Fortimanager to version 7.6.1, where no data is visible in Fortiview of Fortimanager and showing error as below.

Server Error:DB::Exception: Column 'XXX' is not under aggregate function and not in GROUP BY.

if anyone know how to resolve this issue.

[Stephen_G]

Hello,

We are still looking for someone to help you.

We will come back to you ASAP.

[arielr9]

I have an open ticket with the same issue, more than 3 weeks waiting for an answer. 

[rkubota]

Based on your question, I understand that the FortiAnalyzer feature is enabled on FortiManager.

The error indicates that some database error occurs when displaying FortiView.

I confirmed the same error in the test environment with version 7.6.1, suggesting that this may be an issue with the product rather than an issue specific to an individual environment.

The SQL debugging for FortiView can be done with the following commands:

「FortiView」
--------------------------------
# diagnose sql debug sqlqry dbgon
# diagnose sql debug sqlqry show

＜Reproduce the issue in the FortiView GUI＞

# diagnose sql debug sqlqry dbgoff
# diagnose sql debug sqlqry delete

Upon verification, the following JSON error was observed:

sql_query.c:2839 P4835 sql-rewriter error: parse json error: Invalid json

From this, I believe it would be necessary to proceed with further investigation through a TAC ticket.