Technical Tip: Understanding FortiAnalyzer time-related fields in logs and SQL tables

[tsimeonov_FTNT]

Description

This article describes time-related fields in FortiAnalyzer.

Scope

FortiAnalyzer.

Solution

The FortiAnalyzer has four time-related log fields: date, time, dtime and itime.

itime is generated by FortiAnalyzer when it receives a log (with SQL enabled) i.e. FortiAnalyzer local time.
dtime is calculated by FortiAnalyzer in UTC  using the 'data' and 'time' fields received from the FortiGate (in case of downloading rawlogs, it will be represented like eventime).

SQL: Only dtime and itime are inserted into SQL tables.
GUI: GUI 'Date/time' column is calculated based on itime.
Raw Logs: FortiAnalyzer Raw logs include all four fields.

FortiAnalyzer logs:

itime=2014-12-29 15:35:09 vd=root rcvdbyte=4831 srccountry=Reserved app=HTTP transip=172.17.97.181 logver=52 date=2014-12-29 dstip=91.209.8.22 duration=23 sentbyte=578 transport=50925 group=SSO_Guest_Users service=HTTP proto=6 user=guest devid=FGVM010000016443 poluuid=d2f8f562-8fa2-51e4-e6a8-32600e0bd677 dstport=80 type=traffic devname=FGTVM52 dtime=2014-12-29 15:35:07 trandisp=snat sessionid=91254 itime_t=1419896109 policyid=5 srcintf=port2 srcip=192.168.1.205 offset_idx=139690533087533 sentpkt=6 level=notice appcat=Not.Scanned srcport=50925 logid=13 subtype=forward rcvdpkt=7 dstcountry=Bulgaria time=15:35:07 action=close dstintf=port1

In SQL reporting, these fields have a built-in function to convert the long integer into human-readable time format:

• From_itime(itime).
  
• From_dtime(dtime).