Troubleshooting Tip: Empty Reports

Description

Solution

1. Basic Checks Section:

Upgrade path:

One of the reasons that often leads to an empty report is the case when the FortiAnalyzer upgrade path is not properly followed. Thus, first check the upgrade path in the CLI and compare it to the Upgrade Path Tool recommendations.

diagnose cdb upgrade summary

Compatibility:

Validate that the FortiAnalyzer is not running a version lower than the FortiGates. Use the Compatibility Tool.

Report Time Frame and Filters:

Validate the time frame for which the report should be generated and the Filters used.

Go to All Reports -> Select a report -> Settings -> Time Period/Filters.

For the 'Time Period', make sure it is possible to see the analytics logs for that period:

FortiAnalyzer 7.2.x: Log View -> FortiGate. 

FortiAnalyzer 7.4.x: Log View -> FortiGate.

FortiAnalyzer 7.6.x: Log View -> Logs -> Fortinet Logs -> FortiGate.

Do not forget to choose the specific device the logs from which should be used in the report.

If there are no logs, it is possible to generate test logs with the command below on the FortiGate.

diagnose log test

For the 'Filters', make sure the correct values are used in the 'Log Field'.

These values should exist in the received logs.

To check the values in the logs, find a corresponding log and double click it to see its properties.

User Reports:

Because of the case sensitivity, if using the User reports, ensure to type the username the same way as it is seen in the 'Log View'.

Dataset:

Reports consist of Charts. Charts are based on the Datasets.

Check what the Dataset is used to create Charts in the affected (empty) Report. Test this Dataset.

How to identify the Dataset used in the Report:

Go to Reports -> Report Definitions -> All Reports -> Open a report -> Editor ('Layout' in earlier versions) -> Select any Chart (or use the cogwheel icon) -> Clone Chart -> Check the Dataset name.

Write down or copy the name of the Dataset.

The 'Clone Chart' option was used just to see the Dataset name. Now, press 'Cancel' so the Chart is not actually cloned.

How to test the Dataset:

Go to Reports -> Report Definitions -> Datasets. Search for the Dataset from the previous step.

Select the Dataset and press 'View'.

See the SQL query that generates specific output for the Chart. Investigate the query and make sure it is correct.

Use 'Validate' to check the query is properly configured. Use 'Format' to see the query in a more readable way.

Choose the 'Time Period'. It should be the same as it is supposed to be in the Report.

Choose the 'Devices' used for collecting the data. Press 'Go' ('Test' in earlier versions).

If no data is shown or the data is not complete, it may be the case there are no logs for that 'Time Period'/'Device' or the logs are not complete (check log properties as described above). 

2. Debug Section:

 

1. When starting to run a report, FortiAnalyzer creates a log of the report generation status and system performance. Use this diagnostic log to troubleshoot and report performance issues. For example, if the report is very slow to generate, it is possible to use this log to check system performance and see which charts take the longest time to generate. For information on how to interpret the report diagnostic log and troubleshoot report performance issues, see the FortiAnalyzer Report Performance Troubleshooting Guide

To retrieve report generation logs:

• In Reports -> Generated Report, 'right-click' the report and select Retrieve Diagnostic to download the log to the computer.
• Use a text editor to open the log.

 

2. Debug the reporting process using the commands below:
   
   

diagnose debug application sqlreportd 255
diagnose debug enable

While the debug is running, execute the report via GUI or CLI as described in step 3. After the debug output is generated, close the debugging in the CLI via commands:

diagnose debug disable
diagnose debug reset

 

3. Execute the report manually via the CLI. In this example, 'User Detailed Browsing Log' is used from the root ADOM.

exe sql-report run root ?
<schedule-name>    <----- Select one of the available SQL report schedule names.
ADOM: root
NAME    TITLE

10012  - User Detailed Browsing Log

10017  - User Top 500 Websites by Bandwidth

10018  - User Top 500 Websites by Session

10019  - Application Risk and Control

10025  - Cyber Threat Assessment

10037  - Secure SD-WAN Report

10039  - SOC Incident Report

10041  - Throughput Utilization Billing Report

22001  - FortiClient Default Report

30001  - FortiDDos Default Report

 

uranium-esx50 # exe sql-report run root 10012

 

 

4. Run the following command:

diagnose system fsck harddisk

This operation will check and repair the file system, then reboot the system.
Do you want to continue? (y/n)y

 

5. Manually rebuild hcache tables for the reports: See related articles.
   
   
6. Rebuild the database. See related articles.

 

7. If the issue remains, contact Fortinet TAC Support and provide additional details to the ticket, such as the output of the following commands:

diagnose test application sqlreportd 99
exe tac report

 

Additionally, the output of the commands ran in step 2 of the debug section.