SDM: The Last Known Date that an End User Logged into Service Desk Web Interface

Products

• CA Service Desk Manager
• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager - Mobile Application
• CA Service Desk Manager - Xtraction

Issue/Introduction

This article discusses a SQL Query that can be used to determine the last time a given user had logged into the SDM Web interface

Environment

Release : 17.1

Component : SERVICE DESK MANAGER

Resolution

The following query returns the last time that a given user had logged into Service Desk

select MAX( dateadd(S, [session_log].[login_time], '1970-01-01')),  [ca_contact].[userid]
FROM [mdb].[dbo].[session_log]
INNER JOIN [ca_contact] ON [session_log].[contact]=[ca_contact].[contact_uuid] 
group by [ca_contact].[userid]

The given time in the above query is UTC Timezone.

Additional Information

The above query would be executed direct on the SQL Server MDB database via SQL Server Management Studio.

Please be cautious when running any queries direct to the MDB database.  Improper execution may cause a lockout of the database for the Service Desk application.

Please make sure you have session logging enabled.  This article discusses how session logging may be disabled.

https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=53259