Question

LFDS database user_logins table not updated for some Windows users

Directory Server

Updated April 15

asked on April 14

I am trying to create a SQL script to extract the list of the users along with their last login dates. The final goal here is to disable users that have not used Laserfiche in more than 3 months.

For this, I am using a select query with a joint between the "directory_objects" and "user_logins" tables of the LFDS database (we are using Laserfiche 11 self-hosted). This seem to work fine for saml users and for a few Windows users but several other Windows users do not appear with a null last_login date.

I checked the repository in Laserfiche Admin Console, and confirmed these users have both "Last Login" and "Last logout" today. However, when searching for their SID in the "user_logins" table, it doesn't seem to exist.

Does anyone have an idea why this might happen ?

Thank you !

0 0

Answer

SELECTED ANSWER

replied on April 15 • Show version history

You should be able to query the Repository DB like:

SELECT    [AT].[trustee_id]
         ,[AT].[sid]
         ,[AT].[trustee_name]
         ,[AT].[isuser]
         ,[AT].[descrip]
         ,[AT].[trustee_flags]
         ,[UL].[last_login]
         ,[UL].[last_logout]
FROM      [dbo].[all_trustees] AS [AT]
LEFT JOIN [dbo].[user_login] AS [UL]
ON        [AT].[sid] = [UL].[sid]
WHERE     [AT].[isuser] = 1
AND       [AT].[trustee_name] NOT LIKE '%$'

2 0

Replies

replied on April 15

Thanks Brianna, I just ran some more tests and you are right:

- If the Windows AD user connects using the Windows client, then the "Last Login" field won't be updated in the LFDS database / directory_objects table

- As soon as the user connects from the Web client, the update is done immediately.

Thank you !

1 0

replied on April 14

If they are logging in directly using Windows authentication, e.g., through the Desktop Client or into the Desktop Admin Console, then that information won't be captured in LFDS, since they are not logging in through LFDS.

This can also occur if you still have non-SSO authentication enabled for the Web Client.

0 0

You are not allowed to follow up in this post.

Question

Question

LFDS database user_logins table not updated for some Windows users

Answer

Replies

Sign in to reply to this post.