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

[Dave_Hall]

daylight saving enabled/disabled on either?

[thommy88]

It´s enabled on both. For Information:

we use GMT+1:00 as timezone.

regards,

thomas

[Dave_Hall]

What about the time/timezone on the management computer?  I am suspecting the GUI is merely displaying the time/date according to the "local time" on your computer.

See https://forum.fortinet.com/FindPost/115696

[thommy88]

The timzone on the local pc is the same.

i mean that the sql query output is not correct.

for example the time from 00:00-07:00 is the traffic in the night like the sql query. but this should be the data traffic on the day. in the night we don´t have so much traffic.

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

regards,

thomas

[hzhao_FTNT]

Hi Thomas, I am not able to reproduce this issue on my FAZ, but I have checked with dev team, $flex_timescale is based on "itime" in this dataset, i.e. time in fortianalyzer. It is weird to see this issue if your FAZ and management PC are in same time zone. How about report running? Could you run Bandwidth and Applications Report with your time period?

Regards,

hz

[Dave_Hall]

At this point, I'm going to assume the timezone is incorrect or DST issue on the source fgt devices that the FortiAnalyzer is receiving logs from.  That or a timezone sync issue on the VM Host.

[netmin]

I think Dave is right. It looks like the VM host (the virtual HW clock for guests) is running in TZ US/Pacific.

[thommy88]

I´m check the vm host.

the timzone is correct and the time is the same as on the forti analyzer and fortigate.

i disabled daylight saving but this doesn´t has any change for the report.

regards,

thomas

[netmin]

Are you using ESXi hosts, Hyper-V, ...?