Sorry for the delay in replying. As stated by Yasar, it does use a SQL tool. I use MS SQL Server. If you don't have access to a SQL tool, you can try this,
(defsite = 'FWN' and status='ACTIVE' and sysuser=0 and not exists (select userid from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and attemptdate>getdate()-90))
This query used from Maximo and returns all users who haven't logged in in at least 90 days.
Original Message:
Sent: 10-20-2023 17:57
From: Yasar Mansoor
Subject: Report to show last login by USER ID?
Hi Danny, The query I gave you needs to run on a query tool. to incorporate into a BIRT report, you need to tweak it a little.
I use Oracle database so my query can run using SQL developer tool.
Troy's query would run using SQL tool as well.
------------------------------
Yasar Mansoor
eSolutions Saudia
Original Message:
Sent: 10-19-2023 18:42
From: Danny Richardson
Subject: Report to show last login by USER ID?
Thank you @Troy Brannon. Do I run that where the Where Clause is at or do I need to use an SQL tool for that? Thank you
------------------------------
Danny Richardson
Original Message:
Sent: 10-18-2023 09:01
From: Troy Brannon
Subject: Report to show last login by USER ID?
Here is a query I use to find the last log in for one of our sites. You should just need change the siteid.
SELECT DISTINCT m.personid AS PersonID
,p.displayname AS Name
,MAX(CONVERT(varchar(18),l.attemptdate,120)) AS 'Last Login'
FROM maxuser AS m
INNER JOIN person AS p
ON m.personid = p.personid
INNER JOIN logintracking AS l
ON m.userid = l.userid
INNER JOIN maxuserstatus AS s
ON m.userid = s.userid
WHERE m.status= 'ACTIVE' and m.defsite = 'FWN' and m.sysuser = 0
and s.changedate < dateadd(day,datediff(day,0,getdate())-90,+0)
GROUP BY m.personid, p.displayname
ORDER BY PersonID, 'Last Login' desc;
------------------------------
Troy Brannon
Alcon
Original Message:
Sent: 10-17-2023 17:38
From: Danny Richardson
Subject: Report to show last login by USER ID?
@Yasar Mansoor Or here is another attempt at the SQL
------------------------------
Danny Richardson
Original Message:
Sent: 10-17-2023 02:36
From: Yasar Mansoor
Subject: Report to show last login by USER ID?
Hi Danny,
You may query LOGINTRACKING table to get the last login for a user.
I would use below query to achieve this requirement. It might help:
SELECT MAX(ATTEMPTDATE) LASTLOGIN FROM LOGINTRACKING WHERE USERID = 'MAXADMIN' AND ATTEMPTRESULT = 'LOGIN' ORDER BY LOGINTRACKINGID DESC;
Best Regards,
#Administration
#EndUser
#EverythingMaximo
#Security
------------------------------
Yasar Mansoor
eSolutions Saudia
Original Message:
Sent: 10-16-2023 18:14
From: Danny Richardson
Subject: Report to show last login by USER ID?
Is is possible to create a report to show the last login by each USER ID. The only thing I found similar was under Manage Sessions, but you can't filter that by site, and it has multiple entries for each user.
Thank you in advance.
#Administration
#EndUser
#EverythingMaximo
#Security
------------------------------
Danny Richardson
Technimark LLC
------------------------------