Essential SQL Queries for Crowd

お困りですか?

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

コミュニティに質問

This guide is for informational purposes and is not eligible for Atlassian Support as SQL queries construction are beyond Atlassian Support Offerings. Atlassian will not be held liable for any errors or other unexpected events resulting from the use of the following SQL queries. A necessary  data backup is required to ensure data integrity. Please reach out to our Atlassian Answers should you require assistance on SQL queries.

SQL Queries:

Execute the following SQL queries on your preferred database client:

USE crowd;
  1. List of inactive users:

    SELECT id, 
           user_name, 
           active, 
           first_name, 
           last_name, 
           created_date, 
           directory_id 
    FROM   cwd_user 
    WHERE  active = 'F'; 
  2. List of users who have not logged into any integrated Atlassian Applications (JIRA, Confluence, Stash and etc.) before:

    SELECT cwd_user.user_name, 
           cwd_user.id, 
           cwd_user.first_name, 
           cwd_user.last_name, 
           cwd_directory.id, 
           cwd_directory.directory_name 
    FROM   cwd_user 
           JOIN cwd_directory 
             ON cwd_user.directory_id = cwd_directory.id 
    WHERE  cwd_user.id NOT IN(SELECT user_id 
                              FROM   cwd_user_attribute); 
  3. List of users who have not logged into any integrated Atlassian Applications for a period of time:
    • MySQL

    SELECT cwd_user.user_name, 
           cwd_user.id, 
           cwd_user.first_name, 
           cwd_user.last_name, 
           cwd_directory.id, 
           cwd_directory.directory_name, 
           From_unixtime(cwd_user_attribute.attribute_value / 1000) 
    FROM   cwd_user 
           join cwd_directory 
             ON cwd_user.directory_id = cwd_directory.id 
           join cwd_user_attribute 
             ON cwd_user.id = cwd_user_attribute.user_id 
    WHERE  Datediff(( Now() ), 
           ( From_unixtime(cwd_user_attribute.attribute_value / 1000) )) > 90; 

    • PostgreSQL

    SELECT cwd_user.user_name, 
           cwd_user.active, 
           cwd_user.id, 
           cwd_user.first_name, 
           cwd_user.last_name, 
           cwd_directory.id, 
           cwd_directory.directory_name, 
           To_timestamp(Cast(cwd_user_attribute.attribute_value AS DOUBLE PRECISION) 
                        / 1000 
           ) 
    FROM   cwd_user 
           join cwd_directory 
             ON cwd_user.directory_id = cwd_directory.id 
           join cwd_user_attribute 
             ON cwd_user.id = cwd_user_attribute.user_id 
    WHERE  ( Extract(epoch FROM Now()) ) - ( Cast( 
                  cwd_user_attribute.attribute_value AS DOUBLE PRECISION) / 1000 ) 
           >= 
                  10627200 
           AND cwd_user_attribute.attribute_name = 'lastAuthenticated' 
           AND cwd_user.active = 'T' 
    ORDER  BY To_timestamp(Cast(cwd_user_attribute.attribute_value AS DOUBLE 
                                PRECISION) / 1000 
              ) DESC; 

    (info) The number "10627200" is in seconds, so 10627200 / 60 / 60 / 24 = 123 days. You may modify this value to cater your needs.

    • Oracle

    SELECT cwd_user.user_name, 
           cwd_user.active, 
           cwd_user.id, 
           cwd_user.first_name, 
           cwd_user.last_name, 
           cwd_directory.id, 
           cwd_directory.directory_name, 
           to_date('01-01-1970', 'DD-MM-YY') + ( 1 / 24 / 60 / 60 / 1000) * ca.attribute_value AS "Last Login"
    FROM   cwd_user 
           join cwd_directory 
             ON cwd_user.directory_id = cwd_directory.id 
           join cwd_user_attribute ca
             ON cwd_user.id = ca.user_id 
    WHERE  ca.attribute_name = 'lastAuthenticated' 
           AND cwd_user.active = 'T'
           AND to_date('01-01-1970', 'DD-MM-YY') + ( 1 / 24 / 60 / 60 / 1000) * ca.attribute_value
           <= to_date(SYSDATE - 90, 'DD-MM-YY')
    ORDER  BY "Last Login" DESC;  
  4. List of last login dates for users:
    • MySQL

    SELECT cwd_user.user_name, 
           cwd_user.id, 
           cwd_user.first_name, 
           cwd_user.last_name, 
           cwd_directory.id, 
           cwd_directory.directory_name, 
           From_unixtime(cwd_user_attribute.attribute_value / 1000) 
    FROM   cwd_user 
           join cwd_directory 
             ON cwd_user.directory_id = cwd_directory.id 
           join cwd_user_attribute 
             ON cwd_user.id = cwd_user_attribute.user_id 
    WHERE  cwd_user_attribute.attribute_name = 'lastAuthenticated'; 

    • PosgreSQL

    SELECT cwd_user.user_name, 
           cwd_user.id, 
           cwd_user.first_name, 
           cwd_user.last_name, 
           cwd_directory.id, 
           cwd_directory.directory_name, 
           To_timestamp(Cast(cwd_user_attribute.attribute_value AS DOUBLE PRECISION) 
                        / 1000 
           ) 
    FROM   cwd_user 
           join cwd_directory 
             ON cwd_user.directory_id = cwd_directory.id 
           join cwd_user_attribute 
             ON cwd_user.id = cwd_user_attribute.user_id 
    WHERE  cwd_user_attribute.attribute_name = 'lastAuthenticated'; 

    (info) Crowd stores last login time based on  epoc time . The  from_unixtimestamp  for MySQL and   to_timestamp  for PostgreSQL returns a Unix timestamp in seconds. Hence the returned value of cwd_user_attribute.attribute_value is divided by  1000  to discard the milliseconds.

You may need to modify the above SQL queries to match your external databases (i.e. Microsoft SQL Server and Oracle) required format.

最終更新日: 2020 年 2 月 11 日

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

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