Example queries for Assets data using Atlassian Analytics

Summary

If you use asset and configuration management, you can associate your objects with issues in Jira Service Management and Jira.

Solution

A list of Jira issues that reference objects

In Atlassian Analytics, you can combine data from tables for asset and configuration management and tables for Jira products to analyze object usage in your Jira projects. Use queries to get the data you need, then apply extra transformations using other Visual SQL steps. Learn more about Visual SQL steps.

Take a look at these Visual SQL examples to give you an idea of the information you can get from the Atlassian Data Lake.

To get a list of Jira issues that are associated with particular objects:

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

2. Add the following columns to your query:

   1. The Object ID column from the Asset object issue mapping table and use the Group aggregation.

   2. The Label column from the Asset object table and use the Group aggregation.

   3. The Issue key column from the Jira issue table and use the Group aggregation.

3. Optionally, add query filters in the “Filters” section of the query.

   1. Add a query filter to filter for objects of a specific object type:

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

      2. Add the Name column from the Asset type table.

      3. Select the appropriate filter operator. If you only want to filter for one object type, use =. If you want to filter for multiple object types, use is one of.

      4. Type the names of the object types that you want to filter for. This is case-sensitive.

   2. Add a query filter to filter for Jira issues that use a specific issue field:

      1. Select + and condition to add another query filter.

      2. Add the Name column from the Jira issue field table.

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

      4. Type the names of the issue fields that you want to filter for. This is case-sensitive.

4. Select Run query.

5. Use a “Hide column” step to hide the Asset object issue mapping object ID column.

6. Optionally, rename columns in the result table. For example, you could change Name to Issue field name for clarity.

7. Optionally, use a “Reorder columns” step to change the ordering of the columns in your final result table.

Use this information to answer questions such as:

• Which objects are associated with Jira issues?

• How many Jira issues are associated with particular objects?

• Which objects are referenced in a particular Jira issue field?

The Visual SQL example above gets a list of Jira issues with objects of type Laptop.

A list of Jira issues that reference objects with attributes that are also objects

An object has various attributes, and attributes can be a reference to another object. This example is an extension of the previous example, where we got a list of Jira issues associated with a particular object type. In this example, we want to get a list of Jira issues that are associated with an object that has an attribute that is also an object.

Getting this information requires three queries.

The first query

1. Add the following columns to the query:

   1. The Attribute value object ID column from the Asset object attribute table and use the Group aggregation.

   2. The Name column from the Asset type table and use the Group aggregation.

   3. The Name column from the Asset type attribute metadata table and use the Group aggregation.

   4. The Issue key column from the Jira issue table and use the Group aggregation.

2. Add query filters in the “Filters” section of the query.

   1. Add a query filter to filter for objects of a specific object type:

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

      2. Add the Name column from the Asset type table.

      3. Select the appropriate filter operator. If you only want to filter for one object type, use =. If you want to filter for multiple object types, use is one of.

      4. Type the names of the object types that you want to filter for. This is case-sensitive.

   2. Add another query filter to filter for objects that have a specific attribute:

      1. Select + and condition to add another query filter.

      2. Add the Name column from the Asset type attribute metadata table.

      3. Select the appropriate filter operator. If you only want to filter for one attribute, use =. If you want to filter for multiple attributes, use is one of.

      4. Type the names of the attributes that you want to filter for. This is case-sensitive.

3. Select Run query.

The second query

This second query gets a list of all attribute IDs for attributes that are also objects.

1. Select Add query to create another query.

2. Add the Attribute value object ID column from the Asset object attribute table and use the Group aggregation.

3. Add a query filter to exclude objects that don’t have attributes that are also objects:

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

   2. Add the Attribute value object ID column from the Asset object attribute table.

   3. Select is not null for the filter operator.

4. Select Run query.

The third query

This third query gets a list of all object IDs and their labels.

1. Select Add query.

2. Add the following columns to the query:

   1. The Object ID column from the Asset object table and use the Group aggregation.

   2. The Label column from the Asset object table and use the Group aggregation.

3. Select Run query.

Join the queries

Now you need to use joins to bring the data of each query into a single result set.

1. Merge the second and third queries together using a Left join.

2. Merge those results with the first query also using a Left join.

3. Use a “Hide column” step to hide the Attribute value object ID column.

4. Optionally, use a “Reorder columns” step to change the ordering of the columns in your final result table.

The Visual SQL example above gets a list of Jira issues associated with objects of type Laptop that also have an attribute named Size. The Size attribute is also an object.

The number of objects with a particular attribute

This example helps you get a distribution of objects of a particular object type with a particular attribute. Use this information to display how many objects exist for that object type.

For example, if your organization has objects of type Laptop, where each laptop has a Brand attribute, you can see how many laptops of each brand are in your organization by running this query:

1. Add the following columns to your query:

   1. The Attribute value string column from the Asset object attribute table and use the Group aggregation.

   2. The Object ID column from the Asset object attribute table and use the Count of distinct aggregation.

2. Add the following query filters:

   1. Add a query filter to only include data for objects of a particular object type. For this example, the object type is Laptop.

      1. Add the Name column from the Asset type table.

      2. Select = for the filter operator.

      3. Type Laptop for the filter value. You can change the value to whatever object type you want to filter for.

   2. Add a query filter to only include data for objects with a particular attribute. For this example, the attribute is Brand.

      1. Add the Name column from the Asset type attribute metadata table.

      2. Select = for the filter operator.

      3. Type Brand for the filter value. You can change the value to whatever attribute you want to filter for.

3. Select Run query.

4. Optionally, select a compatible chart type to display the distribution data. A bar chart works nicely for this example.