Inactive users

SELECTED ANSWER

replied on December 9, 2022

Hi Mark-

This seems useful to know so I poked at it a little bit and think I figured out queries to use both at the repository level and at the LFDS level. Note that I haven't used these for anything real yet, so it's possible I overlooked something.

Repository

There is a built-in view called all_trustees that lists out everybody in the account cache (I'm still not entirely sure what all is in that giant list...all AD users? everybody in LFDS?); if you limit it to isuser = 1 then you'll see the logins like you would see in the admin console. You can then join that on the user_login table via sid to get the list of accounts for that repository and when they last logged in. No logins at all would be null.

SELECT  all_trustees.trustee_id
        ,all_trustees.trustee_name
        ,all_trustees.descrip
        ,user_login.last_login
        ,user_login.last_logout
FROM all_trustees
LEFT OUTER JOIN user_login ON all_trustees.sid = user_login.sid
WHERE all_trustees.isuser = 1

LFDS

The logic is similar, joining the directory_objects table with user_logins. The type field in directory_objects denotes user vs group vs organization, with type = 0 for users. Flags can be used to identify active vs inactive vs some other things but I'm not super familiar with it. Again, last_login will be null if they've never logged in.

SELECT [directory_objects].[name]
      ,[directory_objects].[type]
      ,[flags]
      ,[comments]
      ,[container_id]
      ,[path]
      ,[user_logins].last_login
  FROM [directory_objects]
  LEFT JOIN user_logins ON directory_objects.sid = user_logins.sid
  WHERE [directory_objects].[type] = 0

1 0

replied on December 11, 2022

Further to Pieters query, I have added a bit more based on another Stored Procedure.

USE [LFDS]
GO
/****** Object:  StoredProcedure [dbo].[LFLFDSUserLicenseLastLogin]    Script Date: 12/12/2022 2:03:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LFLFDSUserLicenseLastLogin] 

AS
BEGIN

	SET NOCOUNT ON;

	SELECT UserName = ([directory_objects].[name])
,[directory_objects].[sid]
	,eMail = (SELECT [str_val]
				FROM [additional_claims]
				WHERE [additional_claims].[claim_id] = 1
				AND [additional_claims].[sid] = [directory_objects].[sid])
  	,LicType = (CASE [user_licenses].[type]
				WHEN '9bba0d89-9a13-455f-ada9-83cf071d46b9' THEN 'Full'
				WHEN '6e858768-cd5c-4e50-8fbe-aced7f67e25f' THEN 'Retrieval'
				WHEN '510aa16b-1b84-4572-776a-0bea0bdee170' THEN 'Participant'
				END)
    ,DisplayName = (SELECT [str_val]
					FROM [additional_claims]
					WHERE [additional_claims].[claim_id] = 12
					AND [additional_claims].[sid] = [directory_objects].[sid])
	,LastLogin = (Select last_login FROM user_logins
  
  Where [user_logins].sid = [directory_objects].sid)
FROM [directory_objects]
INNER JOIN [user_licenses] ON [directory_objects].[sid]=[user_licenses].[sid]
Order by LastLogin asc
END

1 0

Question

Question

Answer

Replies

Sign in to reply to this post.