Does a report exist that shows distinct logins to repositories / forms or does this need to be created manually?
Any auditing information for the repository first requires adding Audit Trail to the solution. It's reporting UI doesn't have any queries for "distinct", but you could query the backend database to easily get that information.
Forms added the two columns: last_login_time and last_logout_time in the cf_users table since Forms 10.4.1, these two columns will be updated when the user login and logout. You can create a session history table to get the logs for login/logout similar as following:
--create sessionhistory table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SessionsHistory](
[LogID] [int] IDENTITY(1,1),
[UserID] [int] NULL,
[SessionID] [uniqueidentifier] NULL,
[LoginTime] [datetime2] NULL,
[LogoutTime] [datetime2] NULL
) ON [PRIMARY]
GO
--create trigger on cf_users table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ('[dbo].[SessionHistoryTrigger]', 'TR') IS NOT NULL
DROP TRIGGER [dbo].[SessionHistoryTrigger]
GO
CREATE TRIGGER [dbo].[SessionHistoryTrigger]
ON [dbo].[cf_users]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
--audit log for login
IF UPDATE(last_login_time)
BEGIN
INSERT into [dbo].[SessionsHistory](
UserID,
SessionID,
LoginTime
)
select u.user_id, u.auth_code,u.last_login_time from
[dbo].[cf_users] u
join
inserted i on u.user_id=i.user_id
join
deleted d on u.user_id=d.user_id
End
--audit log for logout
IF UPDATE(last_logout_time)
BEGIN
INSERT into [dbo].[SessionsHistory](
UserID,
SessionID,
LogoutTime
)
select u.user_id, u.auth_code,u.last_logout_time from
[dbo].[cf_users] u
join
inserted i on u.user_id=i.user_id
join
deleted d on u.user_id=d.user_id
End
END
We tried to run the code against the sandbox database, but the trigger won’t compile because the columns last_login_time, auth_code, and last_logout_time don’t exist within the cf_users table. We are on Forms 10.3.0.975. Is there another way to accomplish this? I did notice a Last Login / Logout field in the Admin Console, is this a possibility?