January 18, 2017

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.

6 comments:

  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;

    ;-)

    ReplyDelete
  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?

    ReplyDelete
    Replies
    1. You are totally right.
      I have added a where clause (and sysuser=0) to filter out those users from the query.

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. 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

    ReplyDelete