How to count last login in Jira based on lisenstype (Jira-software & Jira-servicedesk)


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
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
FROM jira_p.cwd_user_attributes ca
WHERE attribute_name = ‘login.lastLoginMillis’
) a ON a.user_id =
JOIN jira_p.cwd_directory d ON u.directory_id =
JOIN jira_p.cwd_membership m
ON = 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 = ‘1’
AND = ‘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,

I figured out what the problem was and changed the formula in the measurement calculations to:

[User Name].[License Role Name].Members,
[License Role Name].CurrentMember.Name MATCHES ‘jira-servicedesk’

And same formula for “jira-software”.
Now i can filter the lastlogin based on two licensetypes:)

Hi @Pederba79!

I’m happy to see you have moved forward!

Still, I would like to leave my feedback here. I suggest you map the LICENSE_ROLE_NAME column as a separate dimension, e.g., License role, instead of a User property. In this way, no calculated members would be necessary - you could put the time on rows and Last login count on columns and then split it by License role.

Lauma /