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

Description

This article describes time-related fields in FortiAnalyzer.

Scope

FortiAnalyzer..

Solution

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

itime is generated by FortiAnalyzer when it receives a log and is in Analyzer (not Collector) mode; it represents the timestamp when FortiAnalyzer received the log and typically differs slightly from when the event occurred.

dtime is calculated by FortiAnalyzer in UTC using the 'date' and 'time' fields received from FortiGate; it represents the FortiGate's timestamp when the log event occurred and will typically be slightly older than itime. When raw logs are exported from FortiAnalyzer, the field may show as eventtime instead. 

Only dtime and itime are inserted into the FortiAnalyzer's SQL database. The 'Date/Time' column when viewing logs is based on itime (NOT dtime). Raw logs contain all four fields. A log example:

itime=2014-12-29 15:35:09 vd=root rcvdbyte=4831 srccountry=Reserved app=HTTP transip=198.51.100.181 logver=52 date=2014-12-29 dstip=203.113.0.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=Reserved time=15:35:07 action=close dstintf=port1

Note: In the database, the itime and dtime fields are stored as integer. There are functions to convert them to human-readable format to use in datasets/charts/reports:

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