Wrong Time in SQL Query and Reports

[thommy88]

Hi,

we have a Fortigate 100D Cluster 5.2.1 and a Forti Anaylzer VM 5.2.

We notice that the time in SQL-qeury´s and in reports not shown correctly.

When we user "Traffic-bandwidth-timeline" with the following settings see in the attached file.

 

We´re getting this output:

hodex    traffic_out    traffic_in 2014-10-20 23:00    592,894,731    8,160,500,738 2014-10-21 00:00    2,246,362,730    17,946,993,295 2014-10-21 01:00    3,448,872,307    18,865,926,487 2014-10-21 02:00    4,911,297,420    18,667,395,397 2014-10-21 03:00    4,275,801,558    16,429,365,130 2014-10-21 04:00    4,362,237,099    23,935,103,139 2014-10-21 05:00    4,005,303,775    18,061,657,653 2014-10-21 06:00    6,197,975,369    17,085,574,033 2014-10-21 07:00    3,989,806,362    18,981,800,100 2014-10-21 08:00    4,740,491,277    17,487,473,644 2014-10-21 09:00    3,361,311,393    13,043,074,849 2014-10-21 10:00    1,549,734,469    11,921,472,657 2014-10-21 11:00    896,772,418    3,454,822,517 2014-10-21 12:00    586,808,192    602,991,347 2014-10-21 13:00    17,962,776    271,544,810 2014-10-21 14:00    136    117 The timesone on the Fortigate 100D and the analyzer is set correctly. Have anyone this problem too?

regards,

thomas

[thommy88]

We are using Vmware Esxi 5.1.

regards,

thomas

[netmin]

With your information I just set up a trial FAZ VM on one of our ESXi 5.1 clusters to capture some packets and the issue is visible as well. The cluster is NTP synced (ESXi always run UTC as they should). The VM is NTP synced as well.

Interestingly, the hardware FAZ doesn't show the problem when accessed from the same client. So it appears to be a real FAZ VM bug.

[thommy88]

Thank for your information. nice to hear that you can represent the issue. so i hope the next firmware update will fix this.

regards,

thomas

[Andy]

This problem is most often caused by the SYSTEM timezone being different from the SQL timezone.

Execute the following commands to show the current SYSTEM and SQL times:

-------------------------------------------------------

execute time

execute sql-query-generic "select now()"

-------------------------------------------------------

If the results are different then you have a problem.

The simplest method of fixing this is to upgrade to release 5.2.2 (or later) which resets the SQL timezone whenever the system timezone is changed.

Best of luck.