How to query for inactive/ idle users in Fisheye/ Crucible and remove them

Still need help?

The Atlassian Community is here for you.

Ask the community

The content on this page relates to platforms which are supported; however, the content is out of scope of our Atlassian Support Offerings. Consequently, Atlassian cannot guarantee support. Please be aware that this material is provided for your information only and you may use it at your own risk.

Purpose

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


You may have to adapt the below queries depending on what database you are using.


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.

Oracle Example...

This query retrieves all users that have logged in along with all users that have never logged in:

(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
PostgreSQL Example...

This query compatible with PostgreSQL retrieves users that have logged in (and between two dates) in addition to users that have never 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'
      AND TO_TIMESTAMP(CAST(ua.attribute_value AS DOUBLE PRECISION)/1000) BETWEEN '2020-11-01' AND '2020-11-30' -- Comment this line to retrieve all users that have ever logged in regardless of date/time.
) 
UNION 
(
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)
)
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. 





Last modified on Jul 29, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.