Where to find data (date, time, user, ..) related to Service Desk login/logout?

Document ID : KB000047021
Last Modified Date : 14/02/2018
Show Technical Document Details

Question: 

How to find data (date, time, user, ..) related to Service Desk login/logout?

 

Environment:  

Service Desk 12.x

Service Desk Manager 14.1

 

Answer: 

Details about Service Desk login/logout can be found in session_log table

This table contains the following fields:

 

id                  Primary key of this table  

login_time      Indicates the time of when the session began.

logout_time    Indicates the time of when the session ended.

session_id      Displays the ID if the status is okay.

contact           Foreign key to the contact_uuid field of the ca_contact table, this is the User.

session_type   Specifies the unique numeric ID.

policy             Specifies the session policy.

status            Identifies the Login status: 0—Okay

 

Note: date/time fields are in the UNIX format; the number stored is the number of seconds from GMT 1/1/1970 12:00 am.

 

Considering the relationship with the ca_contact table, a possible query to extract also the last_name and first_name of the contact is:

 

SELECT A.last_name, A.first_name, login_time=dateadd(ss, B.login_time, '1/1/1970'), logout_time=dateadd(ss, B.logout_time, '1/1/1970'), B.session_id, B.session_type

FROM ca_contact A, session_log B

WHERE A.contact_uuid = B.contact

order by login_time

  

Additional Information:

Information about the conversion of date/time in Standard Date/Time can be found in:

 

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec468391.aspx

 

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec411768.aspx