Querying Atlassian group data

Summary

This article walks through a few examples of how to query your Atlassian group data via the Atlassian Data Lake and Atlassian Analytics. In the Atlassian Data Lake, the "Group" table holds information about your group(s) such as: the group name, the group ID, and the organization ID. This data can be used to show how many issues a group is assigned, to display the group(s) selected in a Jira custom field, Confluence pages restricted to a group, etc.

The article will highlight a few different ways you can query your group data. Our first example will be how to query any issues that have been assigned to a specific group The second example is how to query the names of the groups that are selected within a Group Picker custom field. Finally, the third example will be how to query the names of any groups that can access a restricted page in Confluence.

Solution

Querying issues assigned to a group

In this example chart, we’ll be able to query the number of issues a group may have been assigned or tagged on an issue through the “Group picker (single group)” or “Group picker (multiple groups)” custom fields.

1. Select Create > Chart > Custom chart from the global navigation to start with a blank visual mode query.

2. Add the following columns to your query:

   1. The Issue key column from the Issue table under “Jira family of products” and use the Count of unique aggregation.

   2. The Name column from the Group table.

3. Optional: Rename the “Count of unique Issue key” column to “Issues”

4. Run the query.

When creating a query that uses columns from the Issue table under “Jira family of products” and the Group table, check that the join path is through the Issue group mapping table.

Querying group data in a custom field

For this example, we’ll be querying the name of the group(s) assigned to a ticket via the ‘Approver groups’ custom field. If your group data is in a different custom field simple swap any references to the ‘Approver groups’ field with the name of your field. To query the groups assigned to an issue, you can use the following steps:

First query

1. Select Create > Chart > Custom chart from the global navigation to start with a blank visual mode query.

2. Add the following columns to your query:

   1. The Issue key column from the Issue table under “Jira family of products” and use the Group aggregation.

   2. The Name and Value columns from the Issue field table and use the group aggregation.

3. Add a query filter in the “Filters” section of the query to filter the results to display issues with an assigned group.

   1. Select + Add filter to add the first query filter.

      1. Add the Name column from the Issue field table.

      2. Select the appropriate filter operator. If you only want to filter for one custom field, use the = operator. If you want to filter for multiple custom fields, use the is one of operators.

      3. Type the name(s) of the custom fields that you want to filter for. This is case-sensitive. For our example, we’ll type ‘Approver groups’ here.

      4. Optional: Use the Issue key column from the Issue table under “Jira family of products” if you want to filter by a specific issue.

4. Run the query.

5. Reorder the columns to have the Value column in the leftmost position of the results.

Second query

1. Select Add query to create another query.

2. Add the Group ID and Name columns from the Group table.

3. Run the query.

Join the queries

1. Edit the join step and switch the join type to be ‘Left’ instead of 'Outer'. A left join allows only the data from the matching objects from the second query to be added to the results of the first query.

2. Optional: Hide the Value and Issue field Name columns.

3. Optional: Add a "Group & aggregate" step to count the unique issues per group to see how many issues are assigned to each group.

4. Optional: Rename the Name column to your desired name, such as ‘Approver groups'.

Querying a group's ability to access a Confluence page or space

In Confluence, individual pages or entire spaces can be restricted to allow specific users or groups to be able to access the content. You can create a query in Atlassian Analytics to see which Atlassian groups can access a restricted page or space within Confluence.

Let’s start with an example on how to query which groups can access restricted pages:

1. Select Create > Chart > Custom chart from the global navigation to start with a blank visual mode query.

2. Add the following columns to your query:

   1. Add the Title column from the Page table.

   2. Add the Restriction type column from the Page restriction table.

   3. Add the Name column from the Group table.

3. Run the query.

To query the groups who have access to a restricted Confluence page, add the following columns in Step 2 above:

1. The Name column from the Space table.

2. The Permission type column from the Space permission mapping table.

3. The Name column from the Group table.

These examples are a few of the ways that you can query your Atlassian group data within Atlassian Analytics. If you have additional group data that you’d like to query but are unsure how to, please contact our support team for further assistance.