I need to get login history of all users from database.

Question

Hi,

I want to get login details of all users from database. I got below query from atlassian Example SQL query,

SELECT
 u.username AS username ,
 psname.propertyvalue AS FullName ,
 psemail.propertyvalue AS email,
 pslogincount.propertyvalue AS logincount,
 to_timestamp(pslastlogin.propertyvalue::numeric /1000) AS lastlogin
FROM
 userbase u
JOIN
 (
 SELECT
 id,
 entity_id
 FROM
 propertyentry
 WHERE
 property_key = 'fullName'
 )
 entityName
ON
 u.id = entityName.entity_id
JOIN
 (
 SELECT
 id,
 entity_id
 FROM
 propertyentry
 WHERE
 property_key = 'email'
 )
 entityEmail
ON
 u.id = entityEmail.entity_id
left JOIN
 (
 SELECT
 id,
 entity_id
 FROM
 propertyentry
 WHERE
 property_key = 'login.count'
 )
 loginCount
ON
 u.id = loginCount.entity_id
left JOIN
 (
 SELECT
 id,
 entity_id
 FROM
 propertyentry
 WHERE
 property_key = 'login.previousLoginMillis'
 )
 lastLogin
ON
 u.id = lastLogin.entity_id
JOIN propertystring psname
ON
 entityName.id=psname.id
JOIN propertystring psemail
ON
 entityEmail.id = psemail.id
left JOIN propertystring pslogincount
ON
 loginCount.id=pslogincount.id
left JOIN propertystring pslastlogin
ON
 lastLogin.id = pslastlogin.id
ORDER BY username;

After executing, I m getting ORA-00907: "missing right parenthesis error".

Could you please assist me on this?

If there is any other way to get details of login history of users, then please let me know.

Jira version is 4.2.3

Thanks,

Niyas

anujjha_kpmg · Answer

If you are using MS SQL server as your database and Jira 7.0.* as your Jira version, you may want to use the below SQL query to get the complete login details includeing his last login, previous login and total login counts.

SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
DATEADD(second, cast(a.attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login",
DATEADD(second, cast(b.attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Previous login",
c.attribute_value AS "Login_Counts"
FROM [JIRASchema].cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM [JIRASchema].cwd_membership m
JOIN [JIRASchema].licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM [JIRASchema].cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.ID
JOIN (
SELECT *
FROM [JIRASchema].cwd_user_attributes cb
WHERE attribute_name = 'login.previousLoginMillis'
) AS b ON b.user_id = u.ID
JOIN (
SELECT *
FROM [JIRASchema].cwd_user_attributes cc
WHERE attribute_name = 'login.count'
) AS c ON c.user_id = u.ID
JOIN [JIRASchema].cwd_directory d ON u.directory_id = d.ID

Order by cast(c.attribute_value as INT) desc

Thanks,

Anuj

AK · Answer

Thanks Udo. This helped

Felipevsw · Answer

On this KB it has examples for MySQL, PostgreSQL and MSSQL. If you're using Oracle, maybe, you can create a similar query based on these ones.

https://confluence.atlassian.com/display/JIRAKB/Retrieve+last+login+dates+for+users+from+the+database