Hello,
I’m trying to create a report that counts last login for all users in Jira in a time period for the last 30 days. I want the report to count the different licensetypes (Jira-software & Jira-servicedesk) that is assigned to the users which was logged in.
The following report is based on SQL import:
SELECT DISTINCT u.user_name,
TO_DATE(‘19700101’,‘yyyymmdd’) + ((attribute_value/1000)/24/60/60) as last_login_date, license_role_name
FROM jira_p.cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM jira_p.cwd_membership m
JOIN jira_p.licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
) m ON m.child_name = u.user_name
JOIN (
SELECT *
FROM jira_p.cwd_user_attributes ca
WHERE attribute_name = ‘login.lastLoginMillis’
) a ON a.user_id = u.id
JOIN jira_p.cwd_directory d ON u.directory_id = d.id
JOIN jira_p.cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN jira_p.licenserolesgroup lrg
ON Lower(m.parent_name) = Lower(lrg.group_id)
WHERE d.active = ‘1’
AND u.active = ‘1’
AND license_role_name in (‘jira-software’,‘jira-servicedesk’)
And the following mapping:
I have tried to create a report based on Time Dimension and two userdefined calculated measures: Jira-servicedesk and Jira-software. I have attached both the report and configuration of the calculated measures.
As you can see of the attachment, the report is showing the same amounts for both measurements. Since not every user has both licensetypes the measurements should have been displayed different numbers.
Is there something that I have done wrong here, and is the formula in the measurement calculations wrong, since i’m getting the same count for both licensetypes?
Best regards,
Peder