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;
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'
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 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I think you need to add 1357554646231 milli seconds to the date 01/01/1970.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.