User login data query

[Caitsith]

Hi,

I have a question that i can get "Login Count", "Last Login", "Failed Count" at once for all users.

I know it can get from user statistics, it's hard to check all each users.

So, i found this query for Last login but is there any way to query at once for the three things?

4.JPG

https://confluence.atlassian.com/jirakb/retrieve-last-login-dates-for-users-from-the-database-363364638.html

I use Jira 7.2.6 with MysqlDB

[Niclas Sandstroem]

Yes you can use the following attribute_name to build our query.

select distinct attribute_name from cwd_user_attributes where attribute_name in ('login.count','login.totalFailedCount','login.lastLoginMillis');
     attribute_name
------------------------
 login.lastLoginMillis
 login.count
 login.totalFailedCount

[Caitsith]

Hi, 

I got error with this query, where i should fix?

SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    to_timestamp(CAST(a.attribute_value AS BIGINT)/1000) AS "Last Login" ,
    b.attribute_value as "Login Count",
    c.attribute_value as "Total Failed Login Attempts"
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name 
    FROM cwd_membership m 
    JOIN globalpermissionentry gp ON m.parent_name = gp.group_id 
    WHERE gp.permission IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
    ) AS m ON m.child_name = u.user_name
LEFT JOIN (
    SELECT * 
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.id
LEFT JOIN (
    SELECT * 
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.count'
    ) AS b ON b.user_id = u.id
LEFT JOIN (
    SELECT * 
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.totalFailedCount'
    ) AS c ON c.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BIGINT)/1000) AS "Last Login", b.attribute_value as "Login Count", c.attribute_v' at line 3

[kishore singh]

use this

DATEADD(second, cast(a.attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login1",