Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?


Converting the Junos Space Audit Log File Timestamp from UTC to Local Time Using Microsoft Excel

You can unzip the compressed comma-separated values (CSV) audit log file (extension .csv.gz) and open the extracted CSV file as a spreadsheet in Microsoft Excel. In Microsoft Excel, you can convert the entries in the Timestamp column from UTC (GMT) to local time.

To convert UTC time to local time:

  1. Retrieve the audit log file from where you archived it. If you archived the file locally, the file is located in /var/lib/mysql/archive on the active node.
  2. Unzip the audit log file (extension .csv.gz).
  3. Open the unzipped audit log file (extension .csv) in Microsoft Excel.
  4. To the left of the UTC Time column, insert a new column.
  5. Label the column header Local Time.
  6. Click the first cell of the new column and insert the following formula =XX/ 86400000 + 25569 - Y/24 in the cell, where XX represents the cell letter and row number where you want to insert the local time-conversion function and Y represents the difference in hours between your local time and the UTC time.
  7. Press Enter.

    The calculated local time appears in the cell.

  8. Format the local time by right-clicking the cell and selecting Format Cells.

    The Format Cells dialog box appears.

  9. From the Category list, select Date.
  10. From the Type list, select a date format that you want.
  11. Click OK.

    The local time and date are displayed in the specified format.

  12. Copy or apply the cell function and formatting to the rest of the rows in the Local Time column. The rest of the local times appear as shown Figure 1.
    Figure 1: Formatting the Local Times Column in Microsoft ExcelFormatting the Local Times Column in Microsoft Excel
  13. Save the Microsoft Excel file.