Technical Tip: How to make the Custom datasets work after firmware upgrade

Description

This article describes the SQL database schema may change during FortiAnalyzer firmware upgrade. Predefined datasets included in the firmware should reflect the schema changes but custom datasets must be manually updated by the administrator.

These change can result in syntax errors when attempting to run custom datasets/charts after upgrading firmware on the FortiAnalyzer.

For example, FortiAnalyzer release notes for patch releases v5.0.7 onward mentioned the following:

Due to database schema changes in v5.0.7, the following rules must be followed by any existing or new datasets:
• If the dataset references any IP-related data, such as srcip or dstip, use the ipstr(‘…‘) function to convert an IP address for proper display. For example, ipstr(‘srcip‘) returns the source IP in a string.
• The column, status, has been changed to action. Replace status with action in the dataset query for proper status.

Scope

FortiAnalyzer v5.0.8 or later.
FortiAnalyzer v5.2.0 or later.

Solution

For all versions of FortiAnalyzer, the administrator can always manually run a test query against each custom dataset in order to check whether there are schema problems (Edit dataset and then select the 'Test' button).

Starting in FortiAnalyzer v5.0.8, a Verification Tool was added for checking the syntax of custom datasets.

'Right-click' on any dataset in the dataset listing (Reports -> Advanced -> Dataset) to see the following two options:

Validate: validates this dataset only.
Validate All Custom: validates all custom datasets.


Running the verification tool is recommended after a firmware upgrade to help identify any custom datasets that need to be modified.