Maximo Open Forum

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.

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

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.

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

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.

@Yasar Mansoor Or here is another attempt at the SQL

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

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.

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;

@Yasar Mansoor Or here is another attempt at the SQL

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

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.

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

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;

@Yasar Mansoor Or here is another attempt at the SQL

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

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.

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.

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

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;

@Yasar Mansoor Or here is another attempt at the SQL

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

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.