WSA reporting CSV file different with PDF version

[firmansworo]

Hi guys,

i need to know why the output CSV different with PDF.

sample from CSV :

Begin Timestamp	End Timestamp	Begin Date	End Date	Time (GMT +07:00)	URL
1459357200	1459443599	2016-03-30 17:00 GMT	2016-03-31 16:59 GMT	1459416286	tunnel://www.google.com:443/
1459357200	1459443599	2016-03-30 17:00 GMT	2016-03-31 16:59 GMT	1459416058	tunnel://www.google.com:443/

sample from PDF :

The question is why Time (GMT +7:00) at CSV cannot be read like PDF version?

need your help for this, thanks guys

[Tao Yang]

WSA will only export raw data to the csv file as mentioned in the user guide.

"

Exporting Report Data

Most reports include an Export link that allows you to export raw data to a comma-separated values (CSV) file. After exporting the data to a CSV file, you can access and manipulate the data in it using applications such as Microsoft Excel.

The exported CSV data displays all message tracking and reporting data in Greenwich Mean Time (GMT) regardless of what is set on the Web Security appliance. The purpose of the GMT time conversion is to allow data to be used independently from the appliance or when referencing data from appliances in multiple time zones.

"

[firmansworo]

Hi Tao,

thanks for the answer, how to manipulate the Time like the pdf version? coz i tried to change it using excel not work.

[Tao Yang]

I don't know the exact reason behind this. However I am assuming as .csv  file is editable and additionally you could also run some macros/functions to convert it.

Hope it helps.

[jeliopoulos]

Hi,

I have been playing around with this today, having the same issue as you.  The time it shows in the Time (GMT +7:00) is actually a unix format for date, and shows the number of seconds from Jan 1 1970.  After some searching I found an Excel formula that will convert this to current time.

First, open the csv file in Excel.  Add 2 columns after the Time (GMT +7:00) column.  Set the format of one column to Date, and the other to Time.  Then copy this formula into both:

=(((A1/60)/60)/24)+DATE(1970,1,1)+(-7/24)

Replace A1 with the cell containing the unix time.  Also, the -7 at the end is to convert to GMT -7.

Refer to http://spreadsheetpage.com/index.php/tip/converting_unix_timestamps/ for more details about the formula.

Note the reason for creating 2 columns in the spreadsheet is to separate the date and the time.  The cell format converts it into either a date or a time.  I have not found a way to show both date and time in one cell.

Hope this helps.