We just recently upgraded to the Enterprise license tier and are trying to use Atlassian Analytics to map users from the Admin user module to their respective project in Jira. We use an SSO tool to grant licenses and assign everyone the jira-software-users group to everyone aside from a few Administrators. How can I use Atlassian Analytics to show which project a user has been assigned to as well as those who are not assigned to a project?
Hi @Marc Hartman !
Currently, the Atlassian Data Lake does not include a table that maps users to their Jira project/space roles or permissions. That said, we do have a feature request open to track interest for this down the road.
There is a workaround mentioned there that uses the Jira API to bridge this gap as well - though that is probably better suited for one-off ad-hoc analysis rather than ongoing reporting needs. Another proxy for this might be to query Jira tables for activity in projects by user, but that is not quite exact either.
Hope this helps!
Thank you. Functionality of this type would be helpful to track who is working on what.
Hi @Marc Hartman,
Have you come right with this?
If I'm understanding correctly, you should be able to return a table chart using the following SQL:
SELECT DISTINCT `Project`.`project_key` AS `Project key`, `Project`.`name` AS `Project name`FROM `jira_issue` AS `Work item`INNER JOIN `jira_project` AS `Project` ON `Work item`.`project_id` = `Project`.`project_id`INNER JOIN `workspace` AS `Workspace` ON `Project`.`workspace_id` = `Workspace`.`workspace_id`WHERE `Work item`.`assignee_account_id` = {SELECTED_USER} OR EXISTS ( SELECT 1 FROM `jira_issue_history` AS `History` WHERE `History`.`issue_id` = `Work item`.`issue_id` AND `History`.`workspace_id` = `Work item`.`workspace_id` AND `History`.`field_key` = 'assignee' AND ( `History`.`value` = {SELECTED_USER} OR `History`.`prev_value` = {SELECTED_USER} ) )ORDER BY `Project key
This is assuming a Custom Dropdown Control added called "SELECTED_USER" (can use visual query here) that selects account id and name (for readability).
If however, your required scenario is as @Robert interpreted earlier, we'll need to wait for that functionality. A workaround that works would to use Teams.
Create a Admin Team, then, add a Dropdown Control on Team ID. Update the control query to filter where the team_id in the Atlassian Team Member table "is one of" the control value.
Hi Marc,
One addition to Bevan’s assignment-based approach: your requirement to include users with no matching projects means the report needs to start from the user list, rather than from work items.
For an all-user version:
Apply any site/project restrictions inside the assignment query before merging, so they do not remove unmatched users afterward.
Label those null results “No matching issue assignments in the included data.” They do not establish that someone has no project access or project role; Robert’s distinction still applies.
Hi Marc, one thing to check before you build the report: if your permission schemes grant Browse Projects to jira-software-users, every licensed user can already reach every project, so "assigned" in your setup most likely means project role membership. Role actors are not in the Data Lake yet (ANALYTICS-65), but the REST API lists them per project (/rest/api/3/project/{projectKey}/role), and joining that output to the user list Sergey described gives you the people with no role anywhere. If you'd rather see this inside Jira than maintain that join, AccessRadar opens a person and shows their effective access across spaces with the path behind each grant (group, role or permission scheme), team-managed spaces included, and snapshot summaries export to CSV. Full disclosure: I founded Radrly, the company that builds AccessRadar. https://marketplace.atlassian.com/3691498947
One implementation detail to add to Marcin’s API suggestion: GET /rest/api/3/project/{projectKey}/role returns role names and URLs, rather than the user membership needed for the join.
GET /rest/api/3/project/{projectKey}/role
Follow each returned role URL to retrieve its actors. Include both directly assigned users (actorUser.accountId) and users belonging to group actors (actorGroup.groupId). Atlassian documents those response structures in the project roles reference.
actors
actorUser.accountId
actorGroup.groupId
For group actors, Get users from group provides the members; collect every page before joining their account IDs to the user export. Otherwise, users whose roles come through groups could incorrectly appear to have no role.
The reporting account also needs permission to read the relevant project roles and group memberships. Treat inaccessible projects or failed requests as incomplete data, not empty membership. The result describes role membership across the projects checked; it still does not establish that someone has no project access.
Thanks Sergey, good catch. That endpoint only returns the role names with their URLs, so the actors come from one more call per role (/rest/api/3/project/{projectKey}/role/{id}). Expanding the group actors matters most here: many sites fill project roles with groups, and skipping that step would put exactly those people on the "no role anywhere" list.
It looks like you're new here. Sign in or register to get started.