List of active users (who login/update/view JIRA frequently) from JIRA 4.2.2 db table.

[Deleted user]

Hi there,

We are intrested in sending out JIRA notifications to only those users who are actually active in JIRA. For that I want to pull out list of active users (who login/update/view JIRA frequently) from JIRA 4.2.2 db table. I tried to run the below SQL in SQL developer (got from: https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA) but it throws "Missing IN or OUT parameter at index:: 1" error.

Can you please assist me in what I am missing?

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;

Any assistance is highly appreciated.

Thanks! Ash

[Udo Brand]

here is the SQL transformation of the last login date

select 

to_date('01011970','ddmmyyyy') + round(to_number(ua.attribute_value)/24/60/60/1000,0) last_login_date

from cwd_user_attributes ua 

where ua.user_id = 10000 

and ua.attribute_name ='login.previousLoginMillis'

[Deleted user]

Yes, you are right! On the DB side, it is the ATTRIBUTE_VALUE from CWD_USER_ATTRIBUTES table which stores last login information (ATTRIBUTE_NAME = login.lastLoginMillis) but this value seems to be in a different format.

Not sure how to read this value?

<th>ATTRIBUTE_NAME</th>

ATTRIBUTE_VALUE
login.lastLoginMillis	1357554646231

[Udo Brand]

I think you need to add 1357554646231 milli seconds to the date 01/01/1970.

[Udo Brand]

I think the error lies within your to_timestamp function. See here.

I'm not sure if this works (most information you are looking for I found in CWD_USER and CWD_USER_ATTRIBUTES) but I'm on 5.1.2