Alfresco Audit alfresco-access Login Summary

alfresco-access-audit-loginSummaryQuery.sql

select
  unpsv.string_value as user,
  max(audit_time) as lastLoginTime,
  count(*) as loginCounts
from
  alf_prop_link pl
  left join alf_audit_entry ae on ae.audit_values_id = pl.root_prop_id
  left join alf_prop_value unpv on unpv.id = ae.audit_user_id
  left join alf_prop_string_value unpsv on unpsv.id = unpv.long_value
where
  unpv.persisted_type = 3
  and key_prop_id in (
    select
      pv.id
    from
      alf_prop_value pv
      left join alf_prop_string_value psv on psv.id = pv.long_value
    where
      pv.persisted_type = 3
      and psv.string_value = '/alfresco-access/login/user'
  )
group by unpsv.string_value;