Find inactive users in Maximo

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

5 thoughts on “Find inactive users in Maximo

  1. 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;

    😉

  2. What happens to the internal users of Maximo if they are inactivated? I'm wondering about users such as DEFLT, MAXIMO, MXINTADM, SYSADM, etc?

  3. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top