![Advanced-UI](https://img.shields.io/static/v1?label=UI&message=Advanced&color=lightgrey)
![NOVA-UI](https://img.shields.io/static/v1?label=UI&message=NOVA&color=blue)
![DB-View](https://img.shields.io/static/v1?label=DB&message=View&color=red)

The "Login Management" plugin provides an overview of all active users, their last login and the number of logins.
Furthermore, an expiration date can be set for users, who are not provisioned by an identity provider (e.g. AD, SCIM) and who are not protected.
The table can also be exported as excel, pdf or csv.

The task will deactivate the above specified users in two cases:

* The expiration date has been reached.
* The user has not logged in or has never used the account after a configurable number of days. 

In addition, the user will be informed by email a configurable number of days before the deactivation date.

If configured, the task also sets an expiration date for the password after a configurable number of days for unprovisioned and unprotected users.

[MINITOC]

## Database View

Before installation of the extension, it is necessary to create the view *login_management* manually by executing the following command on your CELUM database.

> > > > If you update to a 6.13 version or above, you have to delete old view and create it new, because of changes in the columns.

#### MS SQL

```sql
CREATE VIEW login_management AS
    SELECT u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created,
    DATEDIFF(d, u.IMS_CREATED, GETDATE()) daysCreated,
    MAX(s.ims_time) last_login,
    COUNT(case when s.ims_event_type = 1 then 1 else null end) logins,
    DATEDIFF(d, MAX(s.ims_time), GETDATE()) daysLastLogin,
    u.ims_authenticator_name,
    u.ims_expiration_date,
    u.ims_password_expiration_date,
    upv.value As 'uiLocale'
    FROM ims_authorizable u
    LEFT JOIN ims_stats s
    ON s.ims_user_id = u.ims_id AND s.ims_event_type = 1
    LEFT JOIN ims_userprofile up
    ON u.ims_id = up.ims_userid
    LEFT JOIN ims_userprofilevalue upv
    ON up.ims_id = upv.ims_id AND upv.name = 'uiLocale'
    WHERE u.ims_discriminator = 'usr' AND u.ims_deactivated = 0 AND u.ims_authenticator_name != 'api'
    GROUP BY  u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created, u.ims_authenticator_name, u.ims_expiration_date, u.ims_password_expiration_date, upv.value
```

#### MySQL

```sql
CREATE VIEW login_management AS
    SELECT u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created,
    (TO_DAYS(CURDATE()) - TO_DAYS(u.ims_created)) daysCreated,
    MAX(s.ims_time) last_login,
    COUNT(case when s.ims_event_type = 1 then 1 else null end) logins,
    (TO_DAYS(CURDATE()) - TO_DAYS(MAX(s.ims_time))) daysLastLogin,
    u.ims_authenticator_name,
    u.ims_expiration_date,
    u.ims_password_expiration_date,
    upv.value As 'uiLocale'
    FROM ims_authorizable u
    LEFT JOIN ims_stats s
    ON s.ims_user_id = u.ims_id AND s.ims_event_type = 1
    LEFT JOIN ims_userprofile up
    ON u.ims_id = up.ims_userid
    LEFT JOIN ims_userprofilevalue upv
    ON up.ims_id = upv.ims_id AND upv.name = 'uiLocale'
    WHERE u.ims_discriminator = 'usr' AND u.ims_deactivated = 0 AND u.ims_authenticator_name != 'api'
    GROUP BY u.ims_id
```

#### Oracle

```sql
CREATE VIEW login_management AS
    SELECT u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created,
    (trunc(sysdate) - trunc(u.ims_created)) daysCreated,
    MAX(s.ims_time) last_login,
    COUNT(case when s.ims_event_type = 1 then 1 else null end) logins,
    (trunc(sysdate) - trunc(max(s.ims_time))) daysLastLogin,
    u.ims_authenticator_name,
    u.ims_expiration_date,
    u.ims_password_expiration_date,
    TO_CHAR(upv.value) AS uiLocale
    FROM ims_authorizable u
    LEFT OUTER JOIN ims_stats s
    ON s.ims_user_id = u.ims_id AND s.ims_event_type = 1
    LEFT OUTER  JOIN ims_userprofile up
    ON u.ims_id = up.ims_userid
    LEFT OUTER  JOIN ims_userprofilevalue upv
    ON up.ims_id = upv.ims_id AND upv.name = 'uiLocale'
    WHERE u.ims_discriminator = 'usr' AND u.ims_deactivated = 0 AND u.ims_authenticator_name != 'api'
    GROUP BY  u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created, u.ims_authenticator_name, u.ims_expiration_date, u.ims_password_expiration_date, TO_CHAR(upv.value)
```

## Properties

To be configured in *{home}/appserver/conf/custom.properties*.

##### loginManagement.license

> type: String, **required: yes**, default: -

License key (delivered by brix IT Solutions)

##### loginManagement.visibleForGroup

> type: List of long (comma-separated), required: no, default: -

Restrict the use of the plugin to this user group IDs (superadmins always see it in any case).

##### loginManagement.expireDays

> type: long, required: no, default: 0

Defines how many days since the last login/creation date the user account will be deactivated. If not defined, nothing happens.

> > > Super Administrators (including the API-User) will never be deactivated

##### loginManagement.reminderDays

> type: long, required: no, default: 0

Defines how many days before the deactivation the user should receive a reminder mail. If not defined, nothing happens.

##### loginManagement.cronExpression

> type: time (seconds minutes hours days months years, separated with "space"), required: no, default: `0 0 1 * * ?` (each night at 01:00)

When and how often the task is running.

##### loginManagement.protectedUsers

> type: list of UserIds (comma-separated), required: no, default: -, since: 1.1

Protected users are never deactivated, regardless of the settings above. Useful for technical users. Note that superadmins are never deactivated, so those don't need to be listed here.

##### loginManagement.pwExpires

> type: boolean, required: no, default: false

Defines if a password expiration date should be set for unprovisioned users.

##### loginManagement.protectedPwUsers=

> type: list of UserIds (comma-separated), required: no, default: -, since: 1.3 

Password of protected user never expires. Useful for technical users. Note that superadmins' password never expires, so those don't need to be listed here.

##### password.expirationDays

> type: long, required: no, default: 30

Defines after how many days the password should expire.

##### loginManagement.deleteDays

> type: long, required: no, default: 0, since: 1.3.7

Defines after how many days since the deactivation date the user account should be deleted. If not defined, nothing happens.

##### loginManagement.protectedDeleteUsers

> type: list of UserIds (comma-separated), required: no, default: -, since: 1.3.7

Protected users are never deleted, regardless of the settings above.


## Screenshots

![menu](menu.png)

![table](table.png)

## Compatibility Matrix

| Login Management | CELUM (min. version)     |
|------------------|--------------------------|
| 1.0.0            | 6.4  (tested up to 6.8)  |
| 1.1              | 6.4  (tested up to 6.8)  |
| 1.2              | 6.4  (tested up to 6.8)  |
| 1.3              | 6.4  (tested up to 6.11) |
| 1.3.0-6.4.0      | 6.4  (tested up to 6.12) |
| 1.3.1            | 6.13 (tested with 6.13)  |
| 1.3.7-6.4.0      | 6.4 (tested with 6.12)   |
| 1.3.7-6.13.0     | 6.13 (tested with 6.14)  |


| Nova Plugin  | CELUM (min. version) | Backend Plugin (min. version)                 |
|--------------|----------------------| --------------------- |
| 1.0.4-6.14.0 | 	6.14.0              | 1.3.7

## Release Notes

#### 1.0.0

> Release: 2021-01-22

Initial Version

#### 1.1

> Release: 2021-06-29

Added [protectedUsers](#loginmanagementprotectedusers)

#### 1.2

> Release: 2021-07-26

* Added restriction for provisioned users
* added expiration date
* added passwort expiration date
* added export

#### 1.3

> Release: 2022-03-16

* Added [protectedPwUsers](#loginmanagementprotectedpwusers)
* added infomail for expiration date

#### 1.3.1

> Released 2022-08-16

Added support for CELUM 6.13

#### 1.3.7

> Released 2023-03-14

* Nova Support
* delete deactivated user