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
I use Jira 7.2.6 with MysqlDB
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
use this
DATEADD(second, cast(a.attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login1",
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.