필수 SQL 쿼리

SQL 쿼리 :

원하는 데이터베이스 클라이언트에서 다음 SQL 조회를 실행하십시오.

1. 비활성 사용자 목록 :

    

   SELECT id, user_name, active, first_name, last_name, created_date, directory_id FROM cwd_user WHERE active = 'F';

2. 통합 된 Atlassian 응용 프로그램 (JIRA, Confluence, Stash 등)에 로그인하지 않은 사용자 목록 :

    

   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. 일정 기간 동안 통합 된 Atlassian 응용 프로그램에 로그인하지 않은 사용자 목록 :
   • 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;

    숫자 "10627200"은 초 단위이므로 10627200 / 60 / 60 / 24 = 123 일입니다. 필요에 따라 이 값을 수정할 수 있습니다.

    

4. 사용자에 대한 최종 로그인 날짜 목록 :
   • 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';

    

     Crowd는 epoc 시간을 기반으로 마지막 로그인 시간을 저장합니다. MySQL에서 from_unixtime값, PostgreSQL에서 to_timestamp값은 Unix 타임 스탬프를 초 단위로 반환합니다. 따라서 cwd_user_attribute.attribute_value의 반환 값을 1000으로 나눠 밀리 초를 버립니다.