Here is a quick SQL query you can use to find inactive users that haven’t logged into Maximo in the last 2 months.
DB2
select * from maxuser
where status='ACTIVE'
and sysuser=0
and not exists (select userid from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and attemptdate>(current date-60 DAYS))
order by userid;
Oracle
select * from maxuser
where status='ACTIVE'
and sysuser=0
and not exists (select userid from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and attemptdate>sysdate-60)
order by userid;
SQL Server
select * from maxuser
where status='ACTIVE'
and sysuser=0
and not exists (select userid from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and attemptdate>getdate()-60)
order by userid;
Obviously, the above query works only if you have enabled the login tracking feature in Security Groups – Security Controls.
This is very useful if you want a quick idea about how many users are really accessing the system.
Another interesting use of this query is to optimize Maximo licenses by simply deactivating users that no longer use the system. You can even define an escalation that automatically do the job for you.
Find inactive users in Maximo
And a version for SQL Server:
select * from maxuser
where status='ACTIVE'
and not exists (select userid from logintracking where logintracking.userid=maxuser.userid and attemptresult='LOGIN' and attemptdate > getdate()-360)
order by userid;
😉
Thank you!
What happens to the internal users of Maximo if they are inactivated? I'm wondering about users such as DEFLT, MAXIMO, MXINTADM, SYSADM, etc?
You are totally right.
I have added a where clause (and sysuser=0) to filter out those users from the query.
Nicer report:
SELECT max(maxuser.userid) AS userid,
max(logintracking.name) AS name,
max(maxuser.status) AS status,
max(maxuser.defsite) AS site,
max(convert(date,logintracking.attemptdate,101)) AS attemptdate
FROM maxuser
INNER JOIN logintracking ON maxuser.userid=logintracking.userid
WHERE maxuser.status='ACTIVE'
AND sysuser=0
AND NOT EXISTS (SELECT userid FROM logintracking
WHERE logintracking.userid=maxuser.userid
AND attemptresult='LOGIN'
AND attemptdate>getdate()-90)
GROUP BY maxuser.userid
ORDER BY maxuser.userid DESC