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:
Sent: Thu January 26, 2023 04:39 PM
From: Matt F
Subject: SQL Query for Custom Login History Report
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:
Sent: Thu January 26, 2023 12:40 PM
From: Julio Hernandez
Subject: SQL Query for Custom Login History Report
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:
Sent: Thu January 26, 2023 10:33 AM
From: Matt F
Subject: SQL Query for Custom Login History Report
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:
Sent: Thu January 26, 2023 10:11 AM
From: Julio Hernandez
Subject: SQL Query for Custom Login History Report
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
Original Message:
Sent: Wed January 25, 2023 01:58 PM
From: Matt F
Subject: SQL Query for Custom Login History Report
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
------------------------------
#AssetandFacilitiesManagement
#Maximo