get last login date of users in confluence (postgres)

me_tx4neb

hi

i want to get last login date of confluence users. try this but it return for some users "last update" or " last failed login date" instead of last login date.

 

any idea?

thanks

me_tx4neb

<span>WITH</span> last_login_date <span>AS</span>
(<span>SELECT</span> user_id
      , to_timestamp(<span>CAST</span>(cua.attribute_value <span>AS</span> <span>double precision</span>)<span>/</span><span>1000</span>) <span>AS</span> last_login
   <span>FROM</span> cwd_user_attribute cua
  <span>WHERE</span> cua.attribute_name <span>=</span> <span>'lastAuthenticated'</span>
    <span>AND</span> to_timestamp(<span>CAST</span>(cua.attribute_value <span>AS</span> <span>double precision</span>)<span>/</span><span>1000</span>) <span><</span> (<span>CURRENT_DATE</span>))
<span>SELECT</span> c.user_name
     , li.successdate
     , g.group_name
  <span>FROM</span> cwd_user c
 <span>INNER</span> <span>JOIN</span> last_login_date l <span>ON</span> (c.id <span>=</span> l.user_id)
 <span>INNER</span> <span>JOIN</span> cwd_membership m  <span>ON</span> (c.id <span>=</span> m.child_user_id)
 <span>INNER</span> <span>JOIN</span> cwd_group g       <span>ON</span> (m.parent_id <span>=</span> g.id)
 <span>INNER</span> <span>JOIN</span> user_mapping um <span>ON</span> (c.user_name <span>=</span> um.username)
 <span>INNER</span> <span>JOIN</span> logininfo li <span>ON</span> (um.user_key <span>=</span> li.username)
 <span>WHERE</span> g.group_name <span>LIKE</span> <span>'CEO-%'</span> ;