Where can I find the database table information?
If you need the database table, refer to the following link:
[BioStar 2] BioStar 2 Database Table
The event log table is the T_LGyyyymm table.
When is the t_lgYYYYMM Table Created?
The log table (t_lgYYYYMM) is created based on the first device log on the first day of the month (or later if there is no log on the first day of the month).
However note that the table is created based on the device log time which is UTC + 0 based.
For example, if I had logs that happened on the device in the following times:
3/1 07:44 AM
3/1 08:50 AM
3/1 09:11 AM
3/1 10:22 AM
The logs will be in the following table (my timezone is UTC + 9):
3/1 07:44 AM > t_lg_201802
3/1 08:50 AM > t_lg_201802
3/1 09:11 AM > t_lg_201803
3/1 10:22 AM > t_lg_201803
This is because based on the UTC + 0 device time, the first 3/1 log of the device would be at 09:11 AM my time and that is when the new March table is created.
The device would store the log as 3/1 00:11, but I would apply the UTC + 09 on the log to properly translate the time to my timezone.
Hence my t_lgYYYYMM table will always be created after 9:00AM on the first day of the month, based on the first log that is uploaded to the server after that time.
How do I translate the log in the log table to a readable format?
Note that the SRVDT, is the time the log is stored in the server. This time is based on the time configuration of the server.
The DEVDT which is the time of the actual event is based on UTC 0.
Hence in order to calculate the actual time you have to apply your timezone's time.
Below is how a log can be understood.
I will look into my t_lg201709 table which is the table for logs of September, 2017.
We will look into record 1899 (EVTLGUID)
Based on t_evttyp table the EVT 15104 name is explained as TCP Connected (Connected to BioStar Server)
The event log time DEVDT is 1505880554. An easy way to convert this UNIX time for testing would be to use an online tool such as https://www.epochconverter.com/
The date stamp is 4:09:14 AM but if I apply my timezone (UTC +9:00) it becomes 1:09PM.
BioStar 2 web client applies your preference time, and my preference is set as UTC +9:00 in Setting > Preference.
As shown in the BioStar 2 event log, the log is 13:09 as calculated.
Note that you can refer to your device's timezone by going to the T_DEV device table.
This is the UTC based time that is expressed in seconds.
For instance, Seoul Korea is UTC +9:00. In this case the value would be 9 * 60 * 60 = 32400.
Lets check 1505880554 + 32400 = 1505912954 and convert it to human readable format. It matches the time seen in BioStar 2 client.
Additional Hints
Below are some hints on how to change the log into readable format with a query:
Maria DB
select FROM_UNIXTIME(DEVDT) as 'Log Time', SRVDT as 'Server Uploaded Time', EVT as 'Event Type', USRID as 'User ID' from t_lg201803;
* Enter your table name in the 't_lg201803' portion. Note again that a new table is created each month.
