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

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

3 answers

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

Hi Niyas,

Oracle has a problem with :: in this line

to_timestamp(pslastlogin.propertyvalue::numeric /1000) AS lastlogin

I'm not sure about the user information in JIRA 4.2.3. Is it in the table userbase or as in my case in cwd_users?

However to get the last login date I use following query

select cwd_user.*, 
to_date('01.01.1970','dd.mm.yyyy') + to_number(cwd_user_attributes.attribute_value)/1000/60/60/24 as last_login 
from cwd_user, cwd_user_attributes 
where cwd_user.id = cwd_user_attributes.user_id
and cwd_user_attributes.attribute_name ='login.lastLoginMillis';

You would probably need to rewrite this for other attributes you want to obtain (see the original query) and if it is userbase and not cwd_users to replace the "from" and join clause.

Hope I could help,

Udo

Thanks Udo. This helped

0 vote
Anuj Jha I'm New Here Feb 26, 2018

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Monday in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

642 views 6 12
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you