Find users last login onto Bitbucket

SELECT cu.lower_user_name
      ,cu.display_name
      ,cu.lower_display_name
      ,cu.lower_email_address
      ,cu.is_active
      ,dateadd(second,cast(cast(cua.attribute_value AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000') AS LAST_LOGIN
         , cm.lower_parent_name
  FROM [BitBucket].[dbo].[cwd_user] As cu
      LEFT JOIN [BitBucket].[dbo].cwd_membership AS cm
        ON cu.directory_id=cm.directory_id
        AND cu.lower_user_name=cm.lower_child_name
        --AND cm.membership_type='GROUP_USER'

      LEFT JOIN [BitBucket].[dbo].cwd_user_attribute As cua
      ON cu.id = cua.user_id and cua.attribute_name='lastAuthenticationTimestamp' 
      WHERE cm.lower_parent_name IN('bitbucketusers','bitbucketadmins')

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: