Design for Analytics and Reporting Performance

Analytics and reporting place demands on FortiSIEM in addition to log processing. When planning the deployment consider the following:

• The number of analysts

• How heavily the system is used

• Scheduled reporting requirements

The following guidelines are recommended for best query performance.

• Sufficient CPU, memory, and Disk I/O in the Supervisor node will help to scale GUI performance for very large deployments where there are a large number of concurrent analysts. The minimum recommended values are 32vCPU , 64GB RAM and 200MBps Disk I/O.

• Design a solution with more shards for increased query performance. FortiSIEM with ClickHouse distributes queries across multiple shards.

• Use Primary indices in Filter conditions as much as possible. Primary indices provide the best query performance followed by Data Skipping indices. See ClickHouse Index Design for list of ClickHouse Primary and Data Skipping indices.

• Use Data Skipping indices in Query Filter conditions, specially when Primary indices are not possible.

• Operator usage:
  

  • Operator "=" provides best performance.

  • Operators "IN" and "NOT IN" with a small set provides good performance. For these queries, a direct SQL Query is performed by enumerating the set, until the size of the resulting SQL Query is less than 200KB. When the size of the SQL Query is more than 200KB, the query is converted into a LookupTable query, which are somewhat slower. For an end user, it is not possible to measure the size of the SQL Query, but the general idea is to use as narrow a group as the problem dictates.

  • CONTAIN and REGEXP queries for string valued attributes may be relatively slow, unless there are additional conditions using Primary/Data Skipping indices in the same Filter condition.
    

  • In conclusion, the general idea is to choose the narrowest possible Filter Condition by always including ClickHouse Primary and Data skipping indices.
    

Examples

Queries with Filter conditions like “Raw Event Log CONTAIN “User1”” will perform poorly, since Raw Event Log is neither a Primary index nor a Data Skipping index. To improve query performance, consider adding Primary index or Data Skipping index to the Filter condition, e.g.

1. Event Type = “FortiGate Traffic Denied” AND Raw Event Log CONTAIN “User1”

2. Event Type IN “Denied Traffic” AND Raw Event Log CONTAIN “User1”

3. Event Type IN “Regular Traffic” AND Raw Event Log CONTAIN “User1”

Option 1 will provide better performance than Option 2. Option 2 will likely perform better than Option 3 since Regular Traffic event group has 1500+ members while Denied Traffic event group has about 700 members, therefore the SQL Query in Option 3 has a higher probability of crossing the 200KB size limit.

As another example, a query with Filter conditions like “Win Logon Id = 2” will also perform poorly, since Win Logon Id is neither a Primary index nor a Data Skipping index. To improve performance, change the filter condition to

• Event Type = “Win-Security-4624” AND Win Logon Id = 2