Confirm

Expand all | Collapse all

Confirm User Last Login Time

  • 1.  Confirm User Last Login Time

    Posted 01-07-2020 06:16
    Hi
    Has anyone written of got any SQL that identifies when a Confirm User last logged in?
    I have had a go but my SQL just lists everytime a user has logged in and cannot figure out how to just restrict it to the last date/time they logged in.

    We have over 250 registered users and I'm looking into trying to remove users that no longer, use or require, Confirm so need a list that identifies when a user last logged in.

    This is my current SQL

    SELECT
    user_log.login_time,
    user_log.licence_no,
    user_log.login_name,
    user_log.first_name,
    user_log.surname,
    user_log.station_id,
    to_char(user_log.login_time, 'dd/mm/yyyy') as Log_In_Date,
    to_char(user_log.login_time,'HH24:MI:SS') as Log_In_Time,
    to_char(user_log.logged_off_time, 'dd/mm/yyyy') as Log_Off_Date,
    to_char(user_log.logged_off_time,'HH24:MI:SS') as Log_Off_Time
    FROM
    user_log
    ORDER BY user_log.login_time desc


    ------------------------------
    Martin Langler
    EAST RIDING OF YORKSHIRE COUNCIL
    BEVERLEY
    ------------------------------


  • 2.  RE: Confirm User Last Login Time

    Posted 01-07-2020 18:58
    Edited by James Corletto 01-07-2020 19:14
    Hi Martin,

    we have previously not reported on this but do similar reporting for Confirm Connect users so this post made me think that we can use something similar.

    The above query would have duplicate records for users for a few reasons as the user_log table appears to store every login which occurs. By adding the license number and station id this can add more duplicate records for example in my time at Salisbury i have recorded logins on 33 different devices and we have 3 licenses.

    To limit it to the most recent login I started a join from the user_login table to get the list of current users in the system then joined to the user_log table twice once to get the most recent login and again to return the additional information in your query above, there may be a better way to run this query that others might share.

    By joining to the user_login table you can also then limit for only active users as we do not generally delete users but make them inactive (we have 235 active and 291 inactive users).

    see below for my query which I have  filtered for only active users but you can remove the where clause if you would like to see all users in the system

    SELECT
    ,user_log.licence_no
    ,user_login.login_name
    ,user_login.first_name
    ,user_login.surname
    ,user_log.station_id
    ,user_log.login_time
    ,user_log.logged_off_time

    FROM
    user_login

    LEFT JOIN
    ( SELECT MAX(user_log.login_time) as login_time ,user_log.login_name FROM user_log GROUP BY user_log.login_name ) as q
    ON user_login.login_name = q.login_name

    LEFT JOIN user_log
    ON user_login.login_name = user_log.login_name AND user_log.login_time = q.login_time

    WHERE user_login.inactive_flag = 'N'

    ------------------------------
    James Corletto
    Team Leader Strategic Asset Management
    City of Salisbury
    Salisbury
    ------------------------------



  • 3.  RE: Confirm User Last Login Time

    Posted 01-08-2020 03:26
    Hi James,

    Thanks for the info will give it a try later.

    I managed to get the results I required by running the SQL below which identified the last login date for certain users, did filter some out using the where clause. I can now invalidate the users we belive no longer require a Confirm Account within our organisation.

    SELECT
    MAX( user_log.login_time) as Last_Login,
    user_login.login_name,
    user_login.first_name,
    user_login.surname,
    count ( user_login.login_name ) as Quantity_Of_Logins,
    work_group.work_group_name,
    user_group.user_group_code,
    user_group.user_group_name,
    user_login.inactive_flag

    FROM
    user_login,
    user_log,
    work_group,
    user_group

    WHERE
    user_login.user_group_code = user_group.user_group_code AND
    user_login.login_name = user_log.login_name AND
    work_group.work_group_code = user_login.work_group_code AND
    user_log.source_flag IS NULL AND
    user_login.surname IS NOT NULL AND
    user_login.inactive_flag ='N' AND
    user_group.user_group_code NOT IN ('CCAO', 'SUPE' ) AND
    user_login.surname <> 'WEB'

    GROUP BY
    user_login.login_name,
    user_login.first_name,
    user_login.surname,
    work_group.work_group_name,
    user_login.inactive_flag,
    user_group.user_group_code,
    user_group.user_group_name

    ORDER BY
    Last_Login ASC

    ------------------------------
    Martin Langler
    EAST RIDING OF YORKSHIRE COUNCIL
    BEVERLEY
    ------------------------------