Mapping User Accounts to Projects

[Marc Hartman]

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?

[Bevan Williams]

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.

[Robert]

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.

• https://jira.atlassian.com/browse/ANALYTICS-65

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! 

[Marc Hartman]

Thank you.  Functionality of this type would be helpful to track who is working on what.