Update a date database field on successful login

[Marc MAURICE]

Hello,

I'm using jdbc/mysql to authenticate my users.

I have a simple need : I want to add a last_login_date field, updated at
each successful login

I want to be able to know if some accounts have not been used from a
very long time.

I red carefully the doc and reviewed the list of properties, but I see
nothing about that.

Do I need to write some custom code ? Do you know what code or class I
should extend ?

note: just updated my overlay to 6.2 successfully.

Thanks in advance,

Marc

[mba...@scad.edu]

Marc,

I'd suggest taking a look at JDBC Audits - https://apereo.github.io/cas/6.2.x/installation/Audits.html#database-audits

I won't do exactly what you want, but you'll be able to get a last login date from the table it creates.

Good luck!

-Mike

[Marc MAURICE]

Thanks mike.

This may not be the cleanest solution, but it works and will be easy to maintain accross CAS upgrades.

I just configured jdbc audit, and a trigger to update my users table.

delimiter //
CREATE TRIGGER COM_AUDIT_TRAIL_on_insert_update_users_datelastlogin
AFTER INSERT ON COM_AUDIT_TRAIL FOR EACH ROW
BEGIN
  IF NEW.AUD_ACTION = 'AUTHENTICATION_SUCCESS' THEN
    UPDATE users
      SET date_lastloginsuccess = NEW.AUD_DATE
      WHERE email = NEW.AUD_USER;
  END IF;
END;//

In cas.properties :

cas.audit.enabled=true
cas.audit.number-of-days-in-history=30

#cas.audit.jdbc.max-age-days=30
cas.audit.jdbc.url=${cas.authn.jdbc.query[0].url}
cas.audit.jdbc.dialect=${cas.authn.jdbc.query[0].dialect}
cas.audit.jdbc.user=${cas.authn.jdbc.query[0].user}
cas.audit.jdbc.password=${cas.authn.jdbc.query[0].password}
cas.audit.jdbc.driver-class=${cas.authn.jdbc.query[0].driver-class}

I wonder what is the purpose of max-age-days. Is it the same as nomber-of-days-in-history.

There is also a column-length=100 default property.

Anyone knowing whate those properties do ? If not I will review the source code.

documentation link here

Thanks in advance,

Marc