What SQL query in Orchestrator's database displays users' last successful login or errors in the UI'?

News Knowledge Base
1

Which SQL query I can use in the Orchestrator database that will provide the last time when the users logged in to Orchestrator UI?

Issue Description

Execution of a SQL query within the Orchestrator database is required to generate a report indicating the most recent login times of users accessing the Orchestrator UI.

Resolution

To build a such query report, use a query as below:

Note:

  • Replace REPLACE_WITH_YOUR_TENANT_NAME with your Tenant name.
  • Replace REPLACE_WITH_YOUR_ORCHESTRATOR_DATABASE_NAME with your Orchestrator Database Name.
  • Replace "0" in Result = 0 and A.Result = 0 with "1" or "3" if you are looking for errors 'Invalid credentials' or 'The user was not found in database.' or refer to the following Knowledge Base for other errors(results).

SELECT 
 A.[PartitionName] AS TenantName,
 A.[ClientIpAddress], 
 A.[BrowserInfo],
 A.[UserNameOrEmailAddress], 
 A.[UserId],
 CASE 
   WHEN A.Result = 0 THEN 'Success'
   WHEN A.Result = 1 THEN 'Invalid credentials'
   WHEN A.Result = 3 THEN 'The user was not found in database.'
   ELSE 'Other' 
 END AS LoginStatus,
 B.LastLoginTime
FROM 
 [REPLACE_WITH_YOUR_ORCHESTRATOR_DATABASE_NAME].[identity].[UserLoginAttempts] A
INNER JOIN
   (SELECT UserId, MAX(CreationTime) as LastLoginTime
    FROM [REPLACE_WITH_YOUR_ORCHESTRATOR_DATABASE_NAME].[identity].[UserLoginAttempts]
    WHERE Result = 0 
    GROUP BY UserId
   ) B
ON A.UserId = B.UserId AND A.CreationTime = B.LastLoginTime
WHERE A.Result = 0 AND LOWER(A.[PartitionName])=LOWER('REPLACE_WITH_YOUR_TENANT_NAME')


Example:

4.png

This query will show the details of the last successful login for each user in the 'Default' Partition. Specifically, it will display the TenantName, ClientIpAddress, BrowserInfo, UserNameOrEmailAddress, UserId, LoginStatus ('Success' for all results because of the where clause), and LastLoginTime.

This query filters by 'Success' login status and 'Default' PartitionName (TenantName) making use of the LOWER SQL function to ignore the case while comparing.

Always verify proper read permissions for accessing the database table. The utilization of the 'Group By' and 'Max' functions ensures retrieval of the most recent successful login details for each user.