Purpose

There are certain scenarios when you wish to know which users have used their account in the past period. 

Solution

This can be a bit tricky with Fisheye/ Crucible depending on your setup. There are a couple of factors that you'll have to take into consideration. 

• Do you use Delegated LDAP authentication? 

• What version of Fisheye/ Crucible are you using?

Identify Users

Fisheye/ Crucible 4+ and without Delegated LDAP Authentication

If you are  not  using delegated LDAP Authentication, something like the below query should work for you.

(SELECT DISTINCT cwd_user.user_name, (TO_DATE('1970-01-01','yyyy-mm-dd')+((cwd_user_attribute.attribute_value/1000)/24/60/60)) AS last_auth
FROM cwd_user, cwd_user_attribute 
WHERE cwd_user_attribute.user_id = cwd_user.id 
AND cwd_user_attribute.attribute_name = 'lastAuthenticated')
UNION
(SELECT DISTINCT cwd_user.user_name, NULL AS last_auth
FROM cwd_user, cwd_user_attribute 
WHERE cwd_user.id IN (
                      SELECT DISTINCT user_id 
                      FROM cwd_user_attribute 
                      WHERE user_id NOT IN (
                                            SELECT DISTINCT user_id 
                                            FROM cwd_user_attribute 
                                            WHERE attribute_name = 'lastAuthenticated')
                                            )
                      )
ORDER BY 2 ASC

(
-- This first query retrieves users that have EVER logged in
SELECT DISTINCT
      dir.directory_name,
      usr.user_name, 
      TO_CHAR(TO_TIMESTAMP(CAST(ua.attribute_value AS DOUBLE PRECISION)/1000), 'YYYY-MM-DD HH24:MI:SS') AS "last_authentication"
FROM  
      cwd_user_attribute AS ua 
      INNER JOIN cwd_user AS usr ON ua.user_id = usr.id
      INNER JOIN cwd_directory AS dir ON usr.directory_id = dir.id
WHERE 
      ua.attribute_name = 'lastAuthenticated'

	  -- Uncomment this line below and change the start / end dates to retrieve all users that have ever logged between that interval. 
	  -- If the line is left commented out, all users that have ever logged in will be retrieved.
	
      --AND TO_TIMESTAMP(CAST(ua.attribute_value AS DOUBLE PRECISION)/1000) BETWEEN '2020-11-01' AND '2020-11-30'
) 
UNION 
(
-- This second part of the qyery retrieves users that have NEVER logged in
SELECT DISTINCT 
      dir.directory_name, 
      usr.user_name, 
      NULL AS last_authentication
FROM cwd_user_attribute AS ua 
     RIGHT OUTER JOIN cwd_user AS usr ON ua.user_id = usr.id
     LEFT OUTER JOIN cwd_directory AS dir ON usr.directory_id = dir.id
WHERE 
     usr.id NOT IN (SELECT DISTINCT cwd_user_attribute.user_id FROM cwd_user_attribute WHERE attribute_name = 'lastAuthenticated')
)
ORDER BY 1 ASC, 3 ASC;

Pre Fisheye/ Crucible 4.0 or using Delegated LDAP Authentication

If you are using delegated LDAP or any version before Fisheye/ Crucible 4.0, our methods may not provide the desired output.

Before 4.0

Crowd was not imbedded in Fisheye/ Crucible before 4.0, so there were no cwd tables that we could query against.

Delegated LDAP Authentication

If you are using delegated LDAP, this will not work for you because of (CWD-3826) Delegated Authentication directories do not track last authenticated time. The bug was fixed in imbedded Crowd version 2.11.0; However, Fisheye/Crucible is still using Embedded Crowd library version 2.9.5. There is an improvement request to update the Crowd library in Fisheye (FE-7028) Update Embedded Crowd library to version 2.11.0 that contains a fix for CWD-3826. This should help Fisheye/Crucible admins keep track of user activity from the database even if they're using Delegated LDAP Authentication.

The next best way to identify active users in Crucible is to check when the last time a user accessed Crucible review data. 

MySQL

SELECT cru_user_name,MAX(FROM_UNIXTIME(cru_last_viewed/1000)) AS "Last Visited Time"  FROM cru_recently_visited GROUP BY cru_user_name ORDER BY "Last Visited Time";

Oracle

SELECT cru_user_name,MAX(to_date('19700101','YYYYMMDD') + (cru_last_viewed/1000/86400)) AS "Last Visited Time"  FROM cru_recently_visited GROUP BY cru_user_name ORDER BY "Last Visited Time";

MS SQL 

SELECT cru_user_name,MAX(DATEADD(S, (cru_last_viewed/1000),
{d '1970-01-01'})) AS "Last Visited Time" FROM cru_recently_visited GROUP BY cru_user_name ORDER BY "Last Visited Time";

The only problem with this is that cru_recently_visited only stores information about the last time a user accessed a review or a project. That is not related to the last time the user authenticated, and it's not necessarily reliable because it applies to Crucible only. You might have a set of users that only use Fisheye and they won't show up in the results because they're not accessing Crucible reviews/projects every day. Unfortunately, we don't have an alternative other than upgrading or moving away from delegated LDAP if you fall into this category. We recommend you watch and vote on (FE-7028) Update Embedded Crowd library to version 2.11.0 that contains a fix for CWD-3826 if this is the case.

Removing Inactive Users

You can use the instructions in Deleting or deactivating a user; However, if you have hundreds of users that need to be deactivated, this can be problematic. The REST API endpoint rest-service-fecru/admin/users/{name} is useful for bulk removing inactive users.