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; 

    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'; 

    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.