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

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

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

 

 

 

Environment

Service Desk Manager 17.x

 

Resolution

 

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