Global protect External gateway in remote user time format issue

[Joshan_Lakhani]

 In global protect I want to see the Total number of user are Login in and Log out at which time. In Global-protect Gateway when i click on remote user i see current and previous user  connected to through VPN. When i fetch PDF/ CSV file. In file the time it will see correct but when i downlaod this file the  format is change like 15182019 for login and 151824324 for logout. Please suggest how can i correct this.

Source Region	Tunnel Type	Login At	Logout At
IN	IPSec	1581517627	1581517638
AE	IPSec	1585037444	1585063472
OM	IPSec	1582520743	1582525272
IN	IPSec	1581517603	1581517611
AE	IPSec	1585057546	1585058982
AE	IPSec	1584964236	1584968502
AE	IPSec	1584380301	1584389687
AE	IPSec	1584427168	1584427383
AE	IPSec	1585037448	1585062715
IN	IPSec	1583241965	1583242314

[JoergSchuetter · Accepted solution]

The time is displayed as "seconds since 1970-01-01 00:00:00 UTC". If you run on a Linux system

date --date=@1581517627

you will have the date and time based on your local timezone

Wed 12 Feb 2020 03:27:07 PM CET

I bet there are better ways to translate the timestamp back into something readable. The formula in Excel is something like "=(A1-Date (1970,1,1)) *86400"

[koonseng.neo]

Hi JoergSchuetter,

I have the similar issue after exporting out my Global Protect Remote user list.

I try your solution to input your formula "=(A1-Date(1970,1,1))*86400" inside a new cell in excel, where :-

 i) A1 is the value of the user login date become numbers (eg. 1586828881)

The result is exponential figures. (eg.  1.37E+14)

I change the multiply (*) to division (/) "=(A1-Date(1970,1,1))/86400, it looks more like a date after formatting.

The problem is, I got the "day" 2 days earlier and the "year" 70 years earlier.

For example, login date = 14/4/2020. After the formula, I got login date = 12/4/1950

I am using Palo-Alto FW P3020. Can you advise further ? Thanks. 

[JoergSchuetter]

looks like the issue is addressed with PAN-126014 (which is solved in 9.0.7, but I couldn't spot this fix for 8.1.x)

Fixed an issue for GlobalProtect gateways where the Login At and Logout At time fields in the Previous User PDF/CSV report for User Information used the Epoch standard for displaying time.

[koonseng.neo]

Thanks for your quick reply.

[GerryB]

Is there a solution without upgrading PAN OS?

Thanks,

Gerry

[STASHFIN]

We are also facing same issue, as per palo alto support team, need to upgrade OS to 9.0.13, current using 9.0.3,

If you want to you can get vpn user login and logout details via cmd, run "show global-protect-gateway previous-user"  then copy all data on notepad.