Description
This article describes how to convert the Unix EPOCH date-time column data that is being presented in a generated report in FortiAnalyzer's report module by using Excel. The same CSV file data source can also be extracted using a Linux platform.
The Unix EPOCH or Unix timestamp is the number of seconds that have elapsed since the year 1970, January 1st at midnight UTC/GMT. On the other hand, once the report is generated, the report can be downloaded in the format of HTML, PDF, XML, CSV, and JSON.
The Unix EPOCH date-time is shown in the report type XML, CSV, and JSON only. The conversion is essentially useful if a custom graph is needed to plot from a CSV data source.
Scope
FortiAnalyzer Report Module
Solution
Steps:
- Open the downloaded CSV report with Excel.
- Select any blank entry in the Excel.
- Use the below formula in the Excel function. Replace <Source EPOCH Date-Time data> with the EPOCH cell record. Also, replace <timezone> with the value of the intended local time zone.
=(((<Source EPOCH Date-Time data>/60)/60)/24)+DATE(1970,1,1) + (<timezone>/24)
- Format the cell with a customer number format of m/d/yyyy h:mm:ss
- See the following recording for the how-to steps by using Excel. In this example, the data source EPOCH cell record will be A19, and the GMT+8 will be used to show the local time zone. Finally, drag the formulated cell to populate the whole column.
