Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only

  • 1.  SQL Query for Custom Login History Report

    Posted 01/25/23 01:58 PM
    Edited by System Admin 03/22/23 11:47 AM
    Hi All,

    Looking to get some assistance on advancing my SQL query below to pair up my results. Working primarily with the LOGINTRACKING table. This will be outputted onto a BIRT report in a similar fashion. Has anyone performed a similar request before?

    Name Login Date Logout Date Total Time (mins)
    Matt  2023-01-25 11:26 2023-01-25 11:28 2
    Matt  2023-01-25 11:00 2023-01-25 11:05 5

    I have additional columns to add but starting here with my single row output of Login/Logout times. Likely have the report itself perform the duration calculation above unless it's best to do that in SQL and then just display the value.

    1) Is below the best way to get Login/Logout information on a single line? 
    2) How can I uniquely identify each user's session to have it like the above? Are we limited within what's available in the LOGINTRACKING table out of the box? My current output of the query below is not what as intended and I'm struggling on the missing link.

    with TempA as (
    SELECT lt.name, lt.attemptdate as Login
    FROM logintracking lt
    WHERE lt.attemptresult = 'LOGIN'
    ), TempB as (
    SELECT lt.name, lt.attemptdate as Logout
    FROM logintracking lt
    WHERE lt.attemptresult in ('LOGOUT', 'SYSLOGOUT', 'TIMEOUT')
    )
    SELECT A.name, Login, Logout
    FROM TempA A INNER JOIN TempB b ON A.name = B.name

    Thanks in advance!

    Cheers,
    Matt

    ------------------------------
    Matt F
    ------------------------------
    #Maximo
    #AssetandFacilitiesManagement


  • 2.  RE: SQL Query for Custom Login History Report

    Posted 01/26/23 10:12 AM
    Have you tried doing a JOIN on the MaxSessionUID?  Be aware that you may get more the one "Out" record because a TIMEOUT and a SYSLOGOUT may occasionally be written for the same Session.  I usually ignore the SYSLOGOUT.

    ------------------------------
    Julio Hernandez
    Ergonare, Inc.
    Phoenix AZ
    480-759-9870
    ------------------------------



  • 3.  RE: SQL Query for Custom Login History Report

    Posted 01/26/23 10:34 AM
    Hi Julio,

    I have not. Unless I'm not following correctly, I don't think that will benefit me as I'm querying on the historical records in LOGINTRACKING, not the active sessions in MAXSESSION.

    Cheers,
    Matt


    ------------------------------
    Matt F
    ------------------------------

    Original Message


  • 4.  RE: SQL Query for Custom Login History Report

    Posted 01/26/23 12:41 PM

    Hi Matt,

    I think I understand what you're doing.  I'm not talking about JOINing to the MaxSession table.  The MaxSessionUID is a column in the LoginTracking table which allows you to relate the LOGIN to the LOGOUT (or TIMEOUT or SYSLOGOUT) records corresponding to the same Session.

    If I login to Maximo on a Browser at 10:00, the system will insert a LOGIN record.  If I open another Browser 10:15 later and login to Maximo again, the system will insert another LOGIN record.  If I log out of Maximo on the 2nd Browser at 10:30, the system will insert a LOGOUT record with the same MaxSessionUID as the 2nd LOGIN record.   If I log out of the 1st Browser at 10:45, the system will insert a LOGOUT record with the same MaxSessionUID as the 1st LOGIN record.

    JOINing on the MaxSessionUID will allow me to see that I was logged into the 2nd Browser for 15 minutes and to the 1st Browser for 45 minutes.

    Does that make sense?

    The SQL you have above that is JOINing only on Name field will JOIN every LOGIN record for each Name to every LOGOUT, etc. records for that Name and will definitely not yield the desired results.



    ------------------------------
    Julio Hernandez
    Ergonare, Inc.
    Phoenix AZ
    480-759-9870
    ------------------------------

    Original Message


  • 5.  RE: SQL Query for Custom Login History Report

    Posted 01/26/23 04:40 PM
    Thank you, Julio. Makes complete sense. Exactly what I was looking for. I can't believe I missed this obvious column - d'oh! Too much time away from the DB and SQL I guess.

    Appreciative of you, and this forum. 

    Cheers,
    Matt


    ------------------------------
    Matt F
    ------------------------------

    Original Message


  • 6.  RE: SQL Query for Custom Login History Report

    Posted 01/27/23 08:41 AM

    Matt,

    If the intent is to measure concurrent usage, the MaxSessions approach would count each login separately so you would not get an accurate concurrency view. (You can log into many sessions, concurrency should only count a user one time based on their first session login to their last session logout/timeout.)

    Here is SQL that would help you capture logins for concurrency analysis. It is written for DB2, but you could easily convert the date format to MS SQL or PL SQL:

    select  
        logintracking.USERID,  
        ATTEMPTDATE AS LOGIN_DATE,  
        ( 
          SELECT 
            MIN(LOGOUT.ATTEMPTDATE) FROM LOGINTRACKING LOGOUT 
          WHERE 
            LOGINTRACKING.USERID      = LOGOUT.USERID AND 
            LOGOUT.ATTEMPTDATE        > LOGINTRACKING.ATTEMPTDATE AND 
            ATTEMPTRESULT        NOT IN ('FAILED','SUCCESS') 
        ) as LOGOUT_DATE 
      from  logintracking  
      where attemptresult in ('LOGIN') 
/* Optionally, you may want to limit the window of time you're looking at
and attemptdate BETWEEN TIMESTAMP('2021-01-01 00:00:00') AND TIMESTAMP('2022-04-30 23:59:59')
*/
ORDER BY ATTEMPTDATE

    You could expand your analytics capabilities by creating a table or materialized view with the above SQL. This would make it easy to get user concurrency for any point in time by searching for all logins where the login date was prior to your end timestamp and the logout date was after your start timestamp. For example, if you created the above as a table or materialized view called "temp_logins" and wanted to know which users were logged in on 2022-03-01 between 9am and 10am, you'd run something like this against it:

    SELECT
  *
FROM
  TEMP_LOGINS
WHERE
  LOGIN_DATE  < TIMESTAMP('2022-03-31 10:00:00') AND
  LOGOUT_DATE > TIMESTAMP('2022-03-31 09:00:00')


    If you're doing this for the purposes of evaluating an upgrade from Maximo 7x to MAS, you can contact your account representative and they'll put you in touch with a resource to help you with a precise peak concurrency evaluation. 



    ------------------------------
    Christopher Westfall Senior Technical Specialist, IBM Technology
    ------------------------------

    Original Message


Related Content