How to query for inactive/ idle users in Fisheye/ Crucible and remove them
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
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.
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
Without Delegated LDAP Authentication
If you are not using delegated LDAP Authentication, review the two example queries below.
Oracle example
This query retrieves all users that have logged in along with all users that have never logged in:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
(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 ASCPostgreSQL 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
(
-- 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;With 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-7108) Upgrade embedded Crowd to 3.3.0 or later. 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.
PostgreSQL
1
2
3
4
5
SELECT cru_user_name AS user_name,
MAX(TO_CHAR(TO_TIMESTAMP(CAST(cru_last_viewed AS DOUBLE PRECISION)/1000), 'YYYY-MM-DD HH24:MI:SS')) AS last_visited
FROM cru_recently_visited
GROUP BY user_name
ORDER BY last_visited;MySQL
1
2
3
4
5
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
1
2
3
4
5
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
1
2
3
4
5
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-7108) Upgrade embedded Crowd to 3.3.0 or later 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.
Was this helpful?