User Login Stats
This is sort of a how to make the sausage type question.
I do weekly, monthly and yearly reports of total logins to Pyramid and unique user logins. I do this by exporting the Entries log for the time period I want to report on then remove failed logins and logouts.
As I was starting to work on the numbers for all of 2023 it occurred to me that rather than doing all of this manually that it would be great to have a data model that I could report against since I was pretty sure that all of the Pyramid log data was stored in the Pyramid repository database.
I believe the entry log data is stored in the dbo.server_logs_audit table.
Most of the column names make sense, typed_user_name, authentication_method_invoked, time, type, client_ip, user_id, tenant_id, source, entry_source, custom_data
What I do have questions about are time, type, source, and entry_source.
Type, source, and entry_source are integers but I think I can figure out what the integers correspond to in the Entries Log.
The one I have the biggest question about is time. The column type is BigInteger and values appear to all be 13 digits (for example: 1698951758850) and I cannot figure out how that gets translated to a date.
Can someone from Pyramid confirm that I have the right table and provide some info on how they are translating the 13 digit number into a date and time?
I suspect it is EPOCH value. The number of milliseconds since the 1/1/1970.
The type, source and entry_source columns indeed correspond to enumerations. The type one is this:
SUCCESSFUL_LOGIN = 1
FAILED_LOGIN = 2
LOGOUT = 3
entry_source corresponds to:
BROWSER = 0
MOBILE = 1
EMBED = 2
API = 3
UNKNOWN = 4
ODATA = 5
MOBILE_TABLET = 6
and source is internal, and will not be exposed here. Just know that it probably doesn't concern you, and is not shown in the UI/export mechanism either.
As for the time one - Alex was correct saying it's an EPOCH value. More specifically, what's called EPOCH millis, which represent the number of milliseconds since 1.1.1970 (in UTC time). You can see this site for reference - https://www.epochconverter.com/.
If you would like to use this in Model, you could use a Calculated Column node, and set the formula to
This would convert the bigint to a date.
Have a nice one! Always here for any further questions
Thanks and I think you both covered my questions! Very much appreciated.