Maximo SQL query to retrieve Active Users of a group or from a specific server

Information about the number of users logging in a specific JVM is required for sizing the infrastructure capability of a JVM. 

One such example is to get the users logging into Maximo from Anywhere solution. Client can decide on the number of the user licenses needed for Mobile Add-on. 

Administrator can come up with JVM size with the number of concurrent users connecting to this server where Maximo Anywhere Users are connecting to. 

This data can be retrieved using a SQL query from a specific JVM. This query will list the successful login of users into a dedicated JVM with their last login date.

SELECT DISTINCT

    b.userid,     b.defsite,     c.displayname,     b.status,

    MAX (trunc(d.attemptdate)) AS lastlogin

FROM

    maximo.groupuser       a,     maximo.maxuser         b,

    maximo.person          c,     maximo.logintracking   d

WHERE

    a.groupname LIKE 'ANYWHERE_TECHNICIAN%'

    AND a.userid = b.userid

    AND b.defsite IN (  'XX01', 'YY01' )

    AND c.personid = b.personid

    AND b.status = 'ACTIVE'

    AND a.userid = d.userid

    AND d.servername = 'MXServer_AW01'

    AND d.attemptresult = 'LOGIN'

GROUP BY

    b.userid,     b.defsite,     c.displayname,     b.status

ORDER BY

    b.defsite,     b.userid