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.
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
Any assistance is highly appreciated.
