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

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

The content on this page relates to platforms which are supported, however are out of scope of our Atlassian Support Offerings. Consequently, Atlassian can not guarantee providing any support for it. Please be aware that this material is provided for your information only and using it is done so 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.


ソリューション

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
(SELECT DISTINCT cwd_user.user_name, from_unixtime(cwd_user_attribute.attribute_value/1000) 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, '' AS last_auth
FROM cwd_user, cwd_user_attribute 
WHERE cwd_user.id NOT IN(SELECT DISTINCT cwd_user_attribute.user_id FROM cwd_user_attribute))
ORDER BY 2 DESC


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(to_timestamp(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 and/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. 





最終更新日 2018 年 11 月 2 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.