Hi all,
Well need to pull list of users within confluence-group from confluence database (PostgreSQL) and using the below query with some minor tweaks
<span>WITH</span> last_login_date <span>AS</span>
<span>(</span><span>SELECT</span> user_id
<span>,</span> to_timestamp<span>(</span>CAST<span>(</span>cua<span>.</span>attribute_value <span>AS</span> <span>double precision</span><span>)</span><span>/</span><span>1000</span><span>)</span> <span>AS</span> last_login
<span>FROM</span> cwd_user_attribute cua
<span>WHERE</span> cua<span>.</span>attribute_name <span>=</span> <span>'lastAuthenticated'</span>
<span>AND</span> to_timestamp<span>(</span>CAST<span>(</span>cua<span>.</span>attribute_value <span>AS</span> <span>double precision</span><span>)</span><span>/</span><span>1000</span><span>)</span> <span><</span> <span>(</span><span>CURRENT_DATE</span><span>)</span><span>)</span>
<span>SELECT</span> <span>c</span><span>.</span>user_name
<span>,</span> <span>c</span><span>.</span>lower_user_name
<span>,</span> <span>c</span><span>.</span>email_address
<span>,</span> <span>c</span><span>.</span>display_name
<span>,</span> <span>c</span><span>.</span>last_name
<span>,</span> g<span>.</span>group_name
<span>,</span> l<span>.</span>last_login
<span>FROM</span> cwd_user <span>c</span>
<span>INNER</span> <span>JOIN</span> last_login_date l <span>ON</span> <span>(</span><span>c</span><span>.</span>id <span>=</span> l<span>.</span>user_id<span>)</span>
<span>INNER</span> <span>JOIN</span> cwd_membership m <span>ON</span> <span>(</span><span>c</span><span>.</span>id <span>=</span> m<span>.</span>child_user_id<span>)</span>
<span>INNER</span> <span>JOIN</span> cwd_group g <span>ON</span> <span>(</span>m<span>.</span>parent_id <span>=</span> g<span>.</span>id<span>)</span>
<span>WHERE</span> g<span>.</span>group_name <span>=</span> <span>'confluence-users'</span>
<span>ORDER</span> <span>BY</span> last_login <span>DESC</span><span>;</span>but for some reason it only returns 4150 users with the last login time and date.
Then made a little change to line 15 where is says INNER JOIN last_login_date
full outer JOIN last_login_date l ON (c.id = l.user_id)
then it returns all users within the "confluence-group" ~12000 users.
but the other ~7850 users last login date is showing NULL but when I check a couple of them as an example they had last login date in UI.
So I dont know if the "last_login_date" fetch from different tables? or how does it work? and more importantly I need to get the query to return all ~12000 users with their last login date.
Appreciate it if someone could point me to the right direction please.
Thank you
Moses
